I have created two tables for online shopping system.
Items table and Options table
An Item can have 1 or more options, See tables design below:
**Items Table:**
item_id (PK)
name
description
(Eg: 5, 'Item 1,'Item Description)
**Options Table**
option_id (PK)
item_id (FK)
name
price
(Eg: 12, 5,'Option 1', 1.22)
(Eg: 13, 5,'Option 2', 1.60)
You will need create **orders** and **order_items** tables. I do NOT want duplicating item names, option names and price in the **order_items** table. It should point to the refID of item_id and option_id
If I change the price or item name from items / option tables, it should not affect the past invoice (order).
When the price or item name changes, it should insert the new price into the price_history table (audit).. If you know better solution let me know.
Note: You can include PHP code to show working demo.
Thanks