Say I have 3 tables Products, Orders and Orders Product
Products Table
ID | StockNum | Description | Price
4 | 452SB | Black | 45.65
5 | 752SA | Green | 17.12
Orders Table
ID | OrderNum | Total |
14 | 12312 | 79.89 |
15 | 12313 | 45.65 |
Orders Product Table
ID | OrderID | Product | Qty |
45 | 14 | 452SB | 1 |
46 | 14 | 752SA | 2 |
47 | 15 | 452SB | 1 |
If product information changes (Price or Description) this would throw off all previous order information, whats the best solution for this?. Also does it make more sense to be storing the product ID instead of the StockNum?
** Update **
Does This Make More Sense? Also everything marked with ID at the end is FK
Products Table
ID | StockNum | ProductDetailsID |
4 | 452SB | 8 |
5 | 752SA | 9 |
Products Details Table
ID | Description | Price
8 | Black | 45.65
9 | Green | 17.12
Orders Table
ID | OrderNum | Total |
14 | 12312 | 79.89 |
15 | 12313 | 45.65 |
Orders Product Table
ID | OrderID | Product | Qty | ProductDetailsID |
45 | 14 | 452SB | 1 | 8 |
46 | 14 | 752SA | 2 | 9 |
47 | 15 | 452SB | 1 | 8 |
Best Answer
You can add the fields Price and Description to the Orders Product table and copy the values when you create the row. This allows you to read the values as they were by the time of the order execution
And yes, using Keys is always preferable
UPDATE
About foreign keys. Display them like this