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:
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.
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.
Last edited: