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 |
|
tag.costa
Starting Member
1 Post |
 Posted - 2010-01-09 : 06:17:43
|
| Hi,Consider the following simplified example:. Customer table with: - CustomerID (pk) - CustomerName. Customer_History table with: - Customer_HistoryID (pk) - CustomerID (fk to Customer table) - CustomerName. Order table with: - OrderID (pk) - CustomerID/Customer_HistoryID (*). Order_History table with: - Order_HistoryID (pk) - OrderID (fk to Order table) - CustomerID/Customer_HistoryID (*). Both tables would have more fields and relationships with other tables which are not relevant for the question.(*)From a business perspective, users are allowed to change customer names and change the customer associated with each order.However, when orders are presented to the user, the customer name shown is not the 'current' customer name but the name that was there at the time the order was created.Which means the application will query the Customer_History table to show the customer name at the time of the order creation.Would you assign the foreign key in the Order table to the Customer table or the Customer_History table (and why)?Would you assign the foreign key in the Order_History table to the Customer table or the Customer_History table (and why)?Tiago Costa |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-09 : 07:34:15
|
Would you assign the foreign key in the Order table to the Customer table or the Customer_History table (and why)?--> to the Customer table because the id isn't changing in Customer table AND it is always possible that a Customer has no entry in Customer_History.Would you assign the foreign key in the Order_History table to the Customer table or the Customer_History table (and why)?--> same answer.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
Relationships with History Tables