?
Solved

Table Design - Best way to organise my tables

Posted on 2006-03-28
3
Medium Priority
?
317 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
0
Comment
Question by:salukibob
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 500 total points
ID: 16311667
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
 

Author Comment

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

Expert Comment

by:todd_farmer
ID: 16318699
Thanks for the points!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 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