Table Design - Best way to organise my tables

Posted on 2006-03-28
Last Modified: 2008-03-06

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
Question by:salukibob
    LVL 30

    Accepted Solution

    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)


    Author Comment

    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.

    LVL 30

    Expert Comment

    Thanks for the points!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now