S Wilson,
It is a simple database. It is called a bill of materials. There are two
basic schemas. The first uses a single table with an added column to track
what belongs to what. So:
ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | . . . (and whatever else
you need) ITEM_KIT_ID. Kit ID would be the row id of the item that
represents the kit.
For example:
ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | ITEM_KIT_ID
----------+------------+----------------+------------------------+-----------------
1 | 10 | Duck | Donald Duck
|
2 | 20 | Duck Nose | Donald Duck's Nose
| 1
I can know a couple things from this list. First Item 1 is a finished good
because it has no kit number (it doesn't belong to something else) Second,
Item 2 is part of Item 1 because if its kit id.
The limitation in this design is that because it is a one-to-many
relationship it doesn't easily handle complex builds where some parts are
components of some other parts. It would also be a pain to track
consumables like shrinkwrap, glue or assembly hardware like screws. So, to
fix this a variation on this design is needed. You need to add a table that
tracks which components are part of which other components--your Bill of
Materials table or BOM. So the Item Master table above changes slightly to:
ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | . . . (and whatever else
you need)
Kit ID is moved to the BOM table:
ITEM_ID | KIT_ID | FINISHED_GOOD. . . (and whatever else you need related to
this item and kit)
Finished good is a true/false flag included for reporting performance so you
don't need a sub-query to look for null kit id (meaning finished good) It
also allows your management to flag things as finished goods as it suits the
business even though the finished good may be a component of something else.
Bills of materials are a classic example of working with hierarchical data.
There are lots of others, including geneology and organizational charts.
There is a third solution to this problem I didn't list because it is
somewhat unorthodox and also because as a teacher I think students should be
left with something to do.
"Swilson513" <sw********@aol.com> wrote in message
news:4f**************************@posting.google.c om...
I'm trying to make what should be a simple DB, but have done got
myself confused. I'm trying to keep track of the contents of several
kits that use common components. Each component has a unique number
and each kit has a unique number. I would like to be able to pull up
the kit number them choose the individual components that are included
in that kit and keep that record so if any components are changed it
would update the kit.
