Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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                |
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

Orders Product Table

ID  | OrderID  | Product  | Qty   | Description | Price
45  | 14       | 452SB    | 1     | Black       | 45.65
46  | 14       | 752SA    | 2     | Green       | 17.12
47  | 15       | 452SB    | 1     | Black       | 45.65

And yes, using Keys is always preferable

UPDATE

About foreign keys. Display them like this

Products Table

ID | StockNum | ProductDetailsID |
4  | 452SB    | 8                |
5  | 752SA    | 9                |

Products Details Table

ProductDetailsID | Description | Price
8                | Black       | 45.65
9                | Green       | 17.12
share|improve this answer
    
JSantos, doesn't that seem very redundant to you by adding these to the Order Products table? –  Robert Jun 25 '12 at 14:31
    
Redundant? Yes. Necessary? Probably. Redundancy is bad if it is unnecessary. There are other ways, all depends on the problem complexity and restrictions. In this case, this seams like a small classroom exercise. No need to add any complexity. –  JSantos Jun 25 '12 at 14:35
    
@Robert - it is redundant, on purpose. This is a textbook example of denormalization. –  dezso Jun 25 '12 at 14:38
    
@dezso I understand that but what about my update? If the product details change you just create a new row in Products Details and update the Product Table. The Orders Products table won't be updated obviously. –  Robert Jun 25 '12 at 14:42
    
@Robert that is a good solution –  JSantos Jun 25 '12 at 14:44
show 1 more comment

Using ID is a better solution.

You need to employ a concept called foreign key. This is why relation databases are called relational -- because you use foreign keys to relate records in different tables. It does not just give you a simpler and more robust design, but also a speed improvement, once your tables got populated with thousands of records. Nice things to google: "sql foreign key", "sql relations".

Some helpful URLs:

Article from MySQL Documentation about MySQL Foreign Key Syntax

Article from w3school about SQL Foreign Keys

share|improve this answer
    
w3fools.com –  dezso Jun 25 '12 at 14:40
    
thanks for pointing it out. well i looked through the exact article I referred to, and it seems to be alright. it's better though to refer to the MySQL documentation since the syntax is slightly different anyway, and it's more reliable. –  SkyDan Jun 25 '12 at 14:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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