0

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

0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.