mySQL Relational Tables

Posted on

Question :

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                |

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *