Table Design - Best way to organise my tables

Hi,

I am building up a web database application that is essentially a content management system for users that are writing articles and reviews on diverse subjects. All articles and reviews share common data fields such as author, publishing date, etc, which lends itself well to an overall 'Articles' table. However, articles from different sections will have different content (i.e. a music album review will store links to the artists homepage, whereas a film review may have links to actor biographies, and links to various screenshot images). Therefore, each section needs a seperate table or group of tables layout.

Therefore, my question is how might I best link to these seperate types of tables from a common top-level 'Articles' table?

If, for example, I setup my Articles table to be:

Article ID
Author ID
Section ID - type of article (i.e. music review, film review, etc)
LinkID - I would like to use this field to link to the specific table for the Section type. I link to different types of tables, not a static link.

Can this be done? Or is there a better, or more common method for doing this?

Thanks for any help
salukibob
salukibobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
You can do what you described above, but there's a couple of things to keep in mind about this table design.  It effectively enforces a relationship between articles and authors and sections that a single article can only have one article and one section.  That may be valid for your purposes.  However, if there is a need to have multiple authors or multiple sections associated with a single article, you will find it difficult to do so with the table design above.  An alternative would be to move those relationships out into a seperate joining table:

CREATE TABLE article_section (
article_id INT NOT NULL,
section_id NOT NULL,
UNIQUE KEY (article_id, section_id)
);

CREATE TABLE article_author (
article_id INT NOT NULL,
author_id NOT NULL,
UNIQUE KEY (article_id, author_id)
);


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
salukibobAuthor Commented:
Your updated method does make a lot more sense. Although currently there is no intention to have multiple authors, i can see a need for an article to be included in multiple sections. So thanks for that advice, I think thats definately a better route to go down.

cheers
salukibob
0
todd_farmerCommented:
Thanks for the points!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.