Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jessegavin
Starting Member
2 Posts |
 Posted - 2005-12-28 : 17:26:11
|
| I am developing a simple Content Management System for web sites. And I am in the middle of designing the database. I noticed that with a lot of the different types of content that my application will manage, they share a lot of the same types of fields (e.g. Title, Synopsis, Body). So I was thinking that maybe it would be good to create one main table that handles all the different types of info, but to just add a ItemTypeID to distinguish them.I have written a full description of this problem (with table diagrams) here: http://nontalk.com/dbdesign/Any thoughts would be appreciated.--Jesse Gavin |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-28 : 23:00:51
|
| I work with a website that has a variety of differnt content types, all of which use different tables. I have forever regretted not having a single "object ID" that could be used for any content type.There are definitely downsides, but I think if you had an itemType and then use different SP's to retrieve different item types (so they can join against different tables to get content-specific info), you'll be happiest.Well, I can't promise that, I suppose, but I can honestly say I wish I had gone in that direction years ago.Cheers-b |
 |
|
|
jessegavin
Starting Member
2 Posts |
Posted - 2005-12-28 : 23:56:41
|
| Thanks for the reply. That is definitely the direction I am leaning towards, but I wanted to hear what others have experienced before going all the way down that path.--Jesse Gavin |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-29 : 09:23:23
|
| You would be better off having different tables for each type of data. You have substantial differences in attributes between the different types of content, so I don't see any advantage to trying to combine them into one table.There are substantial disadvantages to combining them into one table. For example, if a query has to scan the table, you will have to scan all of your content, not just the type you are after. It will make it more work to write queries, because each will have select a particular type. It will make it harder to enforce domain integrity, because most columns will be nullable, and check constraints will be a lot harder to code.It's a relational database. Use it the way it was meant to be used by defining relations (tables) consisting of items that have the same metadata definitions.CODO ERGO SUM |
|
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2005-12-29 : 09:56:41
|
| I would go down the separate tables route (Option 3) as this is the simplest to understand, breaks the database down into logical entities, and will also give you the best performance as there will be fewer joins when retrieving e.g. news items. Option 1 is always a bad idea, too many redundant columns.You could argue that News and Article are the same entity, and that you have an additional attribute that defines which is which.Jason |
|
|
|
|
|
|
|
|
DB Design: Single or Multiple Tables?