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 |
|
Datascar
Starting Member
1 Post |
 Posted - 2007-03-14 : 13:38:13
|
| I am designing a quotation application. Currently there is a Customer, Quotation and Quotation details table. PK – FK relations between Customers and Quotations, Quotations and Quotation Details exists. If the design conform to the 3rd normal formNo column should be duplicated. Now if the quotation is approved its data should not change again so its said to be historic data. Say for instance the customer that was used on the approved quotation name change in the Customer table then there will be no data consistency. The customer name on the approved quote will now be different – which is not acceptable. Will it be acceptable to denormalize the order table so that it has duplicate columns for the customer data? What suggestions do you have regarding this scenario? |
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-14 : 13:59:57
|
| I would create a histroy table. For each update to Customer have a trigger copy the old record into the history table. Your queries could then check for either the orginal value or accept the current value. If you add a timestamp to the history table you can even track changes over time. We do this when you have prices changes and want to be able to 'refund' money to a customer after the prices have increased/decreased. Can query the producthistory table for a productid and date to retrive the price at the time of purchase.Raymond LaubertMCDBA, MCITP:Administration, MCT |
 |
|
|
|
|
|
Your opinion about table design of a quotation app