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 |
|
jhilb007
Starting Member
17 Posts |
 Posted - 2005-02-14 : 01:24:58
|
| Scenario 1:tbCustomers -> tbCustomersCases (CustId, CaseId) -> tbOpenCases (CaseId)tbProjects -> tbProjectsCases (ProjId, CaseId) -> tbOpenCases (CaseId)tbOrders -> tbOrdersCases (OrdId, CaseId) -> tbOpenCases (CaseId)Scenario 2:tbCustomers -> tbOpenCases (CaseId, CaseType = "Customers")tbProjects -> tbOpenCases (CaseId, CaseType = "Projects")tbOrders -> tbOpenCases (CaseId, CaseType = "Orders")Which is the better way of linking Customers to Cases, Projects to Cases, Order to Cases? Use an identifier in the tbCases to denote the CaseType or use a single intermediary table to link Customer, Projects, Orders to Cases? There will be lots of cases eventually.Thanks,Jeff |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-14 : 01:40:25
|
| If the LINK tables (tbCustomersCases etc.) in 1 get messed up you cannot deduce the Customer or Project or Order from the Case table.With (2) you'll struggle with the FK constraints. And you run the risk of making a typo in a Join and getting some wrong data.I generally go with (2) for this type of thing - but that's probably heresy in these parts! For example, a "User Notes" table that can be referenced by many different tables - I go with columns for NoteIDParentTableParentTableIDTextOfNoteIt's self contained when you are looking at the UserNotes tableKristen |
 |
|
|
jhilb007
Starting Member
17 Posts |
Posted - 2005-02-14 : 01:58:40
|
| Ah, I didn't think about what would happen if the intermediary table (scenario 1) would get lost. Your are correct, I wouldn't be able to accurately deduce what the data in tbCases refers to.Going with #2. |
|
|
|
|
|
|
|
|
Table design question