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 |
|
spud
Starting Member
7 Posts |
 Posted - 2004-12-15 : 18:26:37
|
| I'm stuck and hoping there are some gurus who can help.Company tableCompanyID (pk)Division tableDivisionID (pk)CompanyID (pk)Department tableDeptID (pk)CompanyID (pk)DivisionID (?)There will always be departments for a particular company. In some cases, there *may be* Divisions that sit in between a particular company and its departments. For example:ABC company Division A Sales Services Division B Sales Services (no division) Sales ServicesHow is this _optional_ relationship between company, division, and department best handled? I thought about using a dummy record in the Division table to assign to all departments that have no real division structure so I could end up with a unique key in department table, but that didn't feel like a good practice. Suggestions? Thanks.update: more accurate title and example content. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-21 : 13:45:01
|
| Your virtual Division does have some merits. It simplifies the coding since the relationships are orthogonal.Some other options would be:1) Implement the Department logic in triggers: A bit messy and imposes a performance hit.2) Handle all data access through stored procedures: Still messy and back door data changes could still occur.3) Denormalize the Department table by including the Company key along with the Division key (possibly Null): Still a little messy.Virtual Divisions are starting to look good...HTH=================================================================The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet(1850-1894) |
 |
|
|
spud
Starting Member
7 Posts |
Posted - 2004-12-21 : 14:26:14
|
| Thanks for feedback HTH. I ended up buying a book called Data Modeling Essentials to help improve my understanding. Great book, but even it did not address this particular issue. I am not very experienced with modeling and database design, but would have thought this "pattern" (optional relationships in hierarchies) would be a fairly common one with well known solutions. I have also read a lot of posts on this board dealing with hierarchies, but haven't been able to "see" my solution in them. Searching... |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-21 : 14:44:36
|
| You're more than welcome, Spud. Remember though that these are just my opinions... I can be a full it it as anyone else ;-)HTH=================================================================The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet(1850-1894) |
|
|
|
|
|
|
|
|
handling optional relationship in a hierarchy