Hi
I have been asked to come up with a good compact way for doing product revisions.
If you look at the attached image my friend sells products that are made up
of several items from his stock. Now he can update how many of each item
he puts in the set but now he wants to be able to keep a history of changes.
(SQL Express 2008)
example
(Product) Bumper pencil case
|-----(Item) Pencil (Item_Qty) 10
|-----(Item) Rubber (Item_Qty) 4
|-----(Item) Pencil Sharpener (Item_Qty) 1
When he updates this it will become v2
(Product) Bumper pencil case
|-----(Item) Pencil (Item_Qty) 15
|-----(Item) Rubber (Item_Qty) 3
|-----(Item) Pencil Sharpener (Item_Qty) 2
What would the best data model be for this without making the ProductItems map table massive? Or is it best to sore arrays of
Product_Id (1) | Items(1,2,3) | Item_Qty(15,3,2) | Product_Ver(2) | Active(true)
Thanks in advance