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 |
|
davejuk
Starting Member
1 Post |
 Posted - 2012-01-06 : 07:25:41
|
| Let's say I have a Products table and an Orders table. I then join them with a ProductsOrdered table.If I change the price of a product, then it will be changed on all existing orders which is obviously unacceptable. So when I create a record in ProductsOrdered, I also copy the price to its Price column.But then if I change the product name (as part of a promotion, for example) then it is changed on all previous orders. So I duplicate the name as well.This leaves me wondering if it would be easier to do away with the join entirely, and just copy the entire row from Products into ProductsOrders.How do you guys approach this problem? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-06 : 08:19:13
|
| Design of the AdventureWorks database (Microsoft's sample database) may give you some guidance. What information you save with the Orders depends on your business requirements - In AdventureWorks, the SalesOrderDetail has UnitPrice, UnitPriceDiscount etc., the Product table has ListPrice and so on. But they don't save the product name in the Order data, they just save the ProductId.You can download the Adventureworks database from codeplex. http://msftdbprodsamples.codeplex.com/ |
 |
|
|
|
|
|
Joining Table: Data Duplication Vs. Keeping values