I am about to start work on a PHP MySQL project, and I want to get it right from the start to save trouble and threads later ;0)
It's a system to track production of, lets say, A CANDY FACTORY!
One ORDER has many ITEMS.
One ITEM has many UNITS.
eg.
ORDER #145:
10kg gummi bears
5 complete
5 remaining
12L chocolate-milk
1 complete
2 complete
2 progress
7 remaining
The ORDER having two ITEMS; the two ITEMS, (10kg gummi, 12L milk); and each of those ITEMS has been (at some point) broken down into UNITS to be completed.
My question is, how do I relate the ORDERS, ITEMS & UNTIS tables?
I could have:
ORDERS
order_id
ITEMS
item_id
order_id*
UNITS
unit_id
item_id*
(where every child entry references it's parent entry)
Or I could use:
ORDERS
order_id
[b]items[/b](array)
ITEMS
item_id
[b]units[/b](array)
UNITS
unit_id
(where every parent entry references all it's children in an array)
___________________________________________________________
I am new to MySQL (in fact, all databases) so I'm still learning the correct, efficient and easy way to do these things.
What do you suggest?
Thanks so much :0)