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 |
|
ratface
Starting Member
2 Posts |
 Posted - 2005-07-04 : 04:23:12
|
| I have a question regarding which is the better form of database design. My application has a requirement for a category system where categories can be related to several "parent" tables. For instance, I have the tables tblCompany, tblPerson, tblCategory. tblCategory contains general category information including information about grouping and ordering of categories. A category can be related to either a company or a person. In the past I have used a system where I have a relCategories table containing the following columns... rcId catId externalId externalTable externalTable contains an int which identifies which table the externalId is related to. This has worked ok in the past, but can lead to some hefty sql if one wants to return an overview using information from company and person tables with several categories displayed. I have also considered using a relation table like the following... rcId catId compId persId ... possible other table Ids This looks like it would simplify joins, but if I end up joining categories to more tables there would be a requirement for many columns containing nulls. The third possibility I have examined would be to create separate relation tables for each parent table. This would appear to be the most normalised design, but seems slightly overkill in my opinion. Does anyone have any reflections regarding which is the more accepted design practice? Pros and cons of the various methods? |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-07-05 : 13:40:59
|
| Hi,IMHO: If the parent(s) can be associated with zero-or-one category, place Category_Id as a foreign key in each parent.If a parent can be associated with zero-one-or-many categories you can create a relationship table for each parent/category pair (which you alluded to), and, while seemingly bloating the system with a lot of tables, is usually the easiest and most straightforward to work with, or you can assign each parent table an id, and have one relationship table with parent_id, parent_key, category_id. Stay away from imbedding columns for each parent horizontally. |
 |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-07-05 : 13:48:17
|
| Also: I think I see a surrogate key (rcId) in your design.Since parent_id and category_id combinations will be unique, this is not neccessary. There has been some debate about assigning surrogate keys to relationship tables, and, (in my opinion), this is not a good practice unless there are very strong arguments for it based on alternative processing.Keep it simple. I lean towards parent_table_id, parent_id, category_id if there are many parent tables. If only two or three, KISS dictates an xref table for each parent/category relationship.Cheers |
|
|
|
ratface
Starting Member
2 Posts |
Posted - 2005-07-16 : 07:37:57
|
| Thanks - Klang, that's very helpful advice. Sorry for the late answer - I've been on holiday! |
|
|
|
|
|
|
|
|
Categories with multiple "parent" tables