How should I design these tables?

[Sun_Force]

I'm trying to help a friend to design a database to keep track of all incoming orders to a company.
I don't work on the database myself (because of the lack of knowledge) and rather am a translator.

I think we need some advice on how he should design the tables.
To keep the situation simple, I cut down the number of fields in tables.
This is what we have at present:

tblCustomers:
CustomerPK | CustomerName

tblProducts:
ProductPK | ProductName | Revision

tblOrders:
OrderPK | CustomerFK | ProductFK | Revision | Quantity


An imaginery view of tblProducts:

ProductPK	ProductName
1	Part1
2	Part2
3	Part3
4	Part4
5	Part5

Up to here, everything's fine and I can imagine how the system works.
But I can not wrap my head around the following situation:

Sometimes the orders the company receives (I don't know what to call it) is single orders.
Part1 -> 100 Pcs
Part3 -> 600 Pcs

But sometimes the order is a set/collection of 1 or more product.
For example Part50 is a collection of these parts:
Part1 -> 20 Pcs
Part22 -> 1000 Pcs
part30 -> 500 Pcs
Part4 -> 8 Pcs

If the company receives an order for Part50, all the above parts should be manufactured.
It means that Part50 is behaved as one part and it should exists in tblProducts. But it's a combination of several members of the same table.

I think we need another table (tblSets or whatever I should call it) but I'm not sure about the structure and its relationship to tblProducts.

Notes:
1- The number and combination of members of a set/collection may or may not change in future. In industry, evolution of machines and their parts are inevitable. Revision takes care of these changes.
2- The number of members of a set/collection maybe 1 to several hundred.

How do you design this database.
Any insight is much appreciated.

 

[Ranman256]

typically there are 2 Order tables, 1 for the main order header, then 1 for the details to hold all items & quantities. Like a web shopping cart.
tOrders & tOrderDetail

tOrders tbl:
--------------
OrderID
OrderDate
CustID
PaymentID
etc

tOrderDetail tbl:
-------------
OrderDtlID
OrderID
ProductID
Qty
ProdCost

 

[Sun_Force]

typically there are 2 Order tables, 1 for the main order header, then 1 for the details to hold all items & quantities. Like a web shopping cart.
tOrders & tOrderDetail

tOrders tbl:
--------------
OrderID
OrderDate
CustID
PaymentID
etc

tOrderDetail tbl:
-------------
OrderDtlID
OrderID
ProductID
Qty
ProdCost

We'll see how other manufacturing concern tables (processes,....) will work with your suggestion.

thanks for helping.

 

[jdraw]

Not sure I understand your set up, but you may need to distinguish Product from Parts.
That is a Product is composed of 1 or many Parts.

You may get some insight from this thread (parts/components)

 

[Sun_Force]

I think @Uncle Gizmo understood what I meant. So let me use his example.

For now let's say eSwift2021 is made of 80 parts.
Sometimes we receive an order for eSwift2021.
Sometimes we receive an order for a chain.

we have no problem when the order is some parts of the bicycle (chain, break, etc). they are saved in tblProducts. We add necessary record in tblOrders (and tblOrdersDetails). We can track the manufacturing process, where and when they've been delivered, etc etc.

But how about when we receive an order for 10 eSwift2021?
We don't assemble the parts. So we don't sell eSwift2021. We only manufacture 80 parts and send them to the customer.

So even if the order sheet is one order, but from our point of view, it's a collection of 80 parts.
We need to track all 80 parts, how they've been manufactured and tested, when and where they've been sent?


I hope it clarifies our situation.
In above case how the[/b]

 

[Sun_Force]

typically there are 2 Order tables, 1 for the main order header, then 1 for the details to hold all items & quantities. Like a web shopping cart.
tOrders & tOrderDetail

tOrders tbl:
--------------
OrderID
OrderDate
CustID
PaymentID
etc

tOrderDetail tbl:
-------------
OrderDtlID
OrderID
ProductID
Qty
ProdCost

Still we need a table to keep the list of parts. In my example above, we need a table to save the list of all necessary parts for eSwift2021.
Can you advice on the structure of this table and its relation to tblProducts?

thank you

 

[Sun_Force]

Not sure I understand your set up, but you may need to distinguish Product from Parts.
That is a Product is composed of 1 or many Parts.

You may get some insight from this thread (parts/components)

I haven't setup anything yet. We are at the design process yet.
I'll check your link to see if it helps.
thank you

 

[Sun_Force]

I appreciate all the guidance.
We'll work on the database and will come back if we hit the wall.

Thank you.