Sql-server – Design question: join data

database-designsql server

I have this simple tables (from Northwind database)

Orders–>[Order Details]<—Products

with a query can see the order and the products using join very simple

  SELECT
  Orders.OrderID,
  Products.ProductID,
  Products.ProductName
FROM Orders
INNER JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
  ON [Order Details].ProductID = Products.ProductID

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          Alice Mutton
10279       17          Alice Mutton
10294       17          Alice Mutton

but the problem when a user change the name of the product this will cause display the new name for that product

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          New product name 
10279       17          New product name
10294       17          New product name

old Orders shouldnt change so my question is how keep the related data even if they change the name of the new product

thanks sorry bad english

Best Answer

The typical solution in OLTP is to copy the relevant attributes onto the Order or Order Details table. Eg handle it like the product price, which is always copied onto the Order. So the name of a Product at the time of the Order is modeled as an attribute of the Order, not through the relationship with the Product table.

In Data Warehousing, the typical solution is to maintain a Slowly-Changing Dimension for the Product, so each SqlesFact is linked with a historically-accurate version of the Product Dimension.

Related Question