Hi,

I'm looking for advice on how to store a components and parts type schema in
a database. Take the following as an example:

1 x Engine requires:
- 5 bolts (base part)
- 6 nuts (base part)
- 3 washers (base part)
- 4 fans (component)

1 x Fan requires:
- 3 bolts (base part)
- 2 washers (base part)
- 2 ball bearing sets (component)

1 x Ball Bearing Set requires:
- 10 ball bearings (base part)
- 3 bolts (base part)
- 2 washers (base part)

There are two distinct types here: components (engine, fan, BB set) and base
parts (bolts, nuts, washers, ball bearings).

I want to be able to store this information in the most efficient way
possible in a database. The front-end using the database will need to be
able to run queries that say things like 'get me the complete diarchal part
list for an engine' or 'get me the complete diarchal part list for a fan'.
At the moment I store this information in an array and recurse through it as
many times as is needed to get the part lists. However, I now need to allow
the users to maintain the components & parts data.

I recently came across a technique called Modified Preorder Tree Traversal
that I used to store a tree like structure and I was wondering if there is
another technique that I could use here. MPTT won't work in this case
because it isn't really a tree structure i.e. Engine, Fan and Ball Bearing
Set are all components as such and reside at the same level in the tree,
which each having children that point to each other.

Engine +---- Fan +---- BB Set
Ball bearings
Nuts | Washers |
Bolts
Washers | BB Sets ----+
Washers
Fans ------+

Please excuse the cheap diagram! :-)

One person I have spoken to suggests building another table that stores the
list of components needed by a component e.g. Engine -> Fan, Ball Bearing
Set. Then this could be queried first and the relevant records retrieved
from the components table by the front-end, which could work with the data
in memory, rather than recurse the database. However, I was wondering if
there was a more elegant way to approach this.

On a last note, in case it matters, I am using PHP4 & MySQL4.

I hope I've given a decent enough explanation of what I am trying to do.

Thanks for any help you can provide.

Steve

Sorry, the diagram came out fairly poorly. Here's another go at it:

Engine +-Fan +-BB Set
Nuts | Washers | Bolts
Washers | BB Sets-+ Washers
Fans----+


"Stephen McNabb" <smcnabb (AT) vision_hatespam (DOT) com> wrote

"Stephen McNabb" <smcnabb (AT) vision_hatespam (DOT) com> wrote

I think you can still use a tree structure Steve. There are clearly two
classes of part, composite parts and simple parts. All parts should have a
part id, and composite parts should have an entry in a "part of" table.

The "part of" table might be:

Part_Of(child_part_ID, parent_part_ID).

You would need a number of queries in the order of the "tree depth" to
obtain and structure all the data (using MySQL), and I wouldn't fancy coding
it in PHP (due to the weak typing). Its definitely doable though.

John

PS. Forget about the "modified preorder tree traversal" and think about the
tabular data you need and how to process it to obtain your required data
structure.

Stephen McNabb wrote:

What you want is usually done using two tables:
1. A components/basic parts table.
Each row has a flag which is set for a base part.
2. A Lookup Table.
Each row consists of:
base part part number
component part number
base part flag
For each unique component/base part pair there is one row.
This table has at least two indexes:
A where used index consisting of base part
number/component part number.
A uses index consisting of component part number/base
part number.

The general solution is to use the lookup table to step through
the parts and use recursion whenever a component is encountered.

HTH
Jerry

"Stephen McNabb" <smcnabb (AT) vision_hatespam (DOT) com> wrote

Stephen -

I think I know what you need. From a modeling perspective you want the
classic Bill of Materials model.

(Pretty sure my syntax is wrong in places, but conceptually it is
right)

-- distinct part types, "Component", "Base"
CREATE TABLE PART_TYPE
(
PART_TYPE_ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR2(50)
);

-- master list of parts
CREATE TABLE PART
(
PART_ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR2(50),
PART_TYPE_ID INTEGER NOT NULL REFERENCES PART_TYPE(PART_TYPE_ID)
);

-- BOM (Bill of Materials)
CREATE TABLE PART_BOM
(
PART_ID INTEGER NOT NULL REFERENCES PART(PART_ID),
CHILD_PART_ID INTEGER NOT NULL REFERENCES PART(PART_ID),
QUANTITY INTEGER,
PRIMARY KEY ( PART_ID, CHILD_PART_ID )
);

This is a good generic model for the information, however querying it
can get tricky. In Oracle, there is a method for doing hierarchical
queries which would return results like your diagram above, but I'm
not sure about MySQL.

Dave

One possible way of modeling the data is shown at
www.xdb1.com/Example/Ex105.asp using an experimental db. The model
allows for multiple types of engines, fans, nuts, bolts, washers, etc.

Thanks guys. From your advice I now have a good idea of how I'm going to
tackle this.



"Stephen McNabb" <smcnabb (AT) vision_hatespam (DOT) com> wrote

Look up the Nested Sets model in SQL FOR SMARTIES. I give a part
explosion example that you can modify.