What is the correct way to do this in SQL?

[Doctor Orange]

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)

[bossakungen]

I would not recommend putting an order_id in the items_table, this will make it so that for every order created you must create a new item row that is related to that order, causing heavy redundancy. Instead I would suggest putting the item_ids in the order table, like say:

 

ORDER Table

=========

order_id

item_id

items_complete

items_progress

items_remaining

 

This would cause one order row to contain the following information:

- what kind of item to be shipped

- how many items that are complete related to that order

- how many items that are remaining related to that order

 

The order could then be updated when unit counts are changed. Now, you might want to add customer_ids etc to the order row too but that is not what this thread is about

 

ITEM Table

========

item_id

item_name (or something else)

 

The above example does not need a UNIT table, however I might have misunderstood the question so please tell me if this is not what you meant. Also, there are a lot of ways to do things when it comes to databases, this is just one One key factor, imo, is to reduce redundancy, I would recommend reading about the normal forms.