Help needed with database design

[B Garner]

Hi

Heres hoping someone can help me with a problem.

I need to design a small database to hold stock parts.
This part is well within my abilities and is quite simple.
eg
Part_no
Location
description
Price

etc.

The problem I have is that I need to be able to create some master parts
which will make a kit composed of individual parts.

The users need to be able to choose a master part which will return a list
of the component parts and details such as price etc. On the same order the
individual parts may also be ordered in addition to the master part.
Also some of the individual parts will appear in more than one master part
group.

Any ideas on how to design tha tables to make this easy.

Many thanks in advance.

Brian

 

[David Cressey]

Hi

Heres hoping someone can help me with a problem.

I need to design a small database to hold stock parts.
This part is well within my abilities and is quite simple.
eg
Part_no
Location
description
Price

etc.

The problem I have is that I need to be able to create some master parts
which will make a kit composed of individual parts.

The users need to be able to choose a master part which will return a list
of the component parts and details such as price etc. On the same order the
individual parts may also be ordered in addition to the master part.
Also some of the individual parts will appear in more than one master part
group.

Any ideas on how to design tha tables to make this easy.

Many thanks in advance.

Brian

You are describing a case of the Bill of Materials problem.

One way to do what you describe is to build a second table, Components,
with fields

Master_Part_No
Component_Part_No
Quantity

To indicate that the part "Wheel" occurs 4 times in the master part "Auto",
you would put the part_no for auto in the Master_Part_No field, the part_no
for Wheel in the Component_Part_no field, and the number 4 in the quantity
field.

(Assuming that all four wheels are the same).

To create the lists you describe you would join the master parts table twice
to this table. In one join, you would use Master_parts_no as a foreign key
to Part_no. In the other join, you would use Component_part_no as a foreign
key.

 

[Lance]

I would do this in 2 tables ( although there are valid reasons to consider
doing it in 3 tables ).

The first being the "product table" which would contain both your individual
parts and your master parts.

The second table is a "part to master part" table. This table would contain
fields Part_no & Master_Part_no, the values for both fields would come from
the Part_no field from the "product table." You would also need to add
fields for any other information that is specific to the way the part and
master part interact.. such as quantity. Then for each part you need to add a
record ( consisting of its part_no, the part_no of the master part + whatever
other fields you've included ) for each master part it belongs to. Thus, if
an individual part is included in 4 master parts there would be 4 records for
it in this table.

I would also add a record for each part with itself as both the master and
individual part. This would allow you to ( probably ) use the same process
regardless of a product being an individual part or master part.

 

[B Garner]

Hi David

Thanks for the help.

I will have to try this and see if it produces the results I need.

What I want is a stock application where I can order any part individually.

Eg "spark plug" and the list shows the item and quantity I have ordered.
I also need to be able to order "service" which would list 4 spark plugs 4
leads and a filter etc.
I would hope this to be detailed on the order list by component.
This would then create a picking list to collect the individual parts to
make the order.

Is this possible with your example.

Thanks

Brian