How should I design these tables? (1 Viewer)

Sun_Force

Active member
Local time
Today, 14:52
Joined
Aug 29, 2020
Messages
356
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:

ProductPKProductName
1Part1
2Part2
3Part3
4Part4
5Part5

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:

Ranman256

Well-known member
Local time
Today, 00:52
Joined
Apr 9, 2015
Messages
3,963
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

Active member
Local time
Today, 14:52
Joined
Aug 29, 2020
Messages
356
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

Super Moderator
Staff member
Local time
Today, 00:52
Joined
Jan 23, 2006
Messages
13,924
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)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:52
Joined
Jul 9, 2003
Messages
13,568
Raman's explanation is correct for a normal invoicing system.

The table "tOrderDetail" is often referred to as a line item table, due to is appearance as rows or lines in the relevant section of the invoice.

However I don't think that's the essence of your question, I think your question was how do you account for a part which is made from a combination of other parts.

A line item arrangement may well suit you, and suffice. However if your business process refers to the composite part by its own name, then you may need to find a different solution.

For example you may have a bike called "eSwift2021" which is sold as a bike, but it is composed of components:- wheels
chain
sprockets
frame
handlebars
seat
brakes
battery
electric motor ...

Before I go any further, there have been previous posts on this particular subject, that is one part created out of other parts, or one component created out of other components. I'm explaining it like that so you have some terms that might help you in a search of the forum to find previous discussions.
 

Sun_Force

Active member
Local time
Today, 14:52
Joined
Aug 29, 2020
Messages
356
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]
 
Last edited:

Sun_Force

Active member
Local time
Today, 14:52
Joined
Aug 29, 2020
Messages
356
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:52
Joined
Jul 9, 2003
Messages
13,568
I'll check your link to see if it helps.

My vague recollection of that thread makes me think it should be of help to you. Have a look, and if necessary, get back to us with some more questions.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:52
Joined
Jul 9, 2003
Messages
13,568
I reckon you need a composite product table. The table "tblCompositeProd", would consist of two columns, the composite product name "fCompositeName" and composite product components "fComponent"... Both Fields would be filled with an ID number "ProductPK" from the product table "tblProducts" so a normal product "a Chain" with an ID "ProductPK" of 101 would appear thus:-

"fCompositeName" >>> "fComponent"
101 >>>>>>>>>>>>>>> 101

And a listing for the bike "eSwift2021" a composite product (which could which consists of 80 parts) with an ID "ProductPK" of 997501 would appear thus:-



"fCompositeName" >>> "fComponent"
997501 >>>>>>>>>>>>>>> 101 (Chain)
997501 >>>>>>>>>>>>>>> 209 (Rear Wheel)
997501 >>>>>>>>>>>>>>> 335 (Front Wheel)
997501 >>>>>>>>>>>>>>> 011 (Sprocket)
997501 >>>>>>>>>>>>>>> 1033 (Battery)
997501 >>>>>>>>>>>>>>> 3978 (Motor)

When a customer places an order for a chain you would look up "Chain" product ID "101" in the table "tblCompositeProd", field "fCompositeName"... Your query would return just one result, the same product number as your look up "101" then you would insert this result in to the line item table along with the invoice number.

If a customer placed an order for a bike "eSwift2021" you would look up product ID "997501" in the table "tblCompositeProd", field "fCompositeName".. But this time you would not get one result you would get 80 results defining the parts required to build the eBike.

You would use an insert query to insert these results into your line item table, not forgetting the invoice number.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 19, 2002
Messages
33,777
Ranman got you to two tables for the Order -- tblOrderHeader and tblOrderDetail are common names.
Uncle got you to two tables for the Product -- tblProduct and tblProductDetails are common names.

The tblProductDetails is a junction table and it connects tblProduct to itself essentially letting one product be the parent of other products. Some manufacturing systems call this concept "Kits". Inventory processing gets complicated when they keep both the kit and its component parts in inventory but you don't seem to do that. You don't build the kit or even store all the components in one box. You just need to know what the components are. Most junction tables connect two different tables for example Students to Subjects but this is a self referencing relationship similar to Employee to Supervisor because a Supervisor is also an Employee and a Product is also a Part. The difference here though is the relationship is many-many which is why you need the junction table. In most organizations the Supervisor-Employee relationship is 1-m so no junction table is needed unless you work for one of those progressive organizations where they matrix their organizational structure giving each employee potentially many supervisors. (I know, TMI)

So, the order details will have one row for each product the customer orders ie
productA, 20, $3,00
productB, 1, $588

But, when the order goes to the production department this gets translated to the break down from tblProductDetails so they know what they need to manufacture.
ProductA 20
Produc1. 3
Product2, 43
Product3, 5
Product88, 2
 

Sun_Force

Active member
Local time
Today, 14:52
Joined
Aug 29, 2020
Messages
356
I appreciate all the guidance.
We'll work on the database and will come back if we hit the wall.

Thank you.
 

Users who are viewing this thread

Top Bottom