We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Table Design - Best way to organise my tables

salukibob
salukibob asked
on
Medium Priority
354 Views
Last Modified: 2008-03-06
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
Comment
Watch Question

Top Expert 2006
Commented:
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)
);


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Top Expert 2006

Commented:
Thanks for the points!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.