Table Design

[Marco Martin]

Hi Group,

I need to come up with a sound Database design, that will allow me to
"Hiarchialy" save and extract data. The end result will someday be a Bill Of
Materials.

The basic idea is this:

Lets say we create a Widget:
The Widget is comprised of 3 items;
1) a Display screen
2) a keyboard
3) a power supply

the Display Screen, keyboard and power supllies are made up of different
components also.

So the data should look something like this:

WIDGET
- DisplayScreen
-DisplayScreen Casing
-plastic
-screws
-Paint
-Various electronic components
-Wires
- Keyboard
-keys
-wires
-switches
- PowerSupply
-Transformer
-Diodes
-capacitors

So My question is; What would be the best way to store this inside a
Database. Should it all come from the same table? If so, how will I keep
track of these parts(ie:plastic belongs to DisplayScreenCasing which belongs
to displayScreen which belongs to Widget.)? I've come up with a few
solutions, but none allow me to track the hierarchy efficiently without
writing hundreds of lines of code.

What i would like to do is have one table hold all the parts, and have ONE
lookup table which would hold the references to each part.

Any insight, ideas, or directions would be greatly appreciated.

Best regards, and happy holiday season,

Marco

[Dan Brussee]

The simplest way I can see is to have a parts table and a where-used
table.

PartID Description
1 DisplayScreen
2 DisplayScreenCasing
3 Plastic
4 Screws
5 Paint
6 Keyboard
7 Plastic

Where-Used
PartID AssemplyID
2 1
3 2
4 2
5 2
7 6

On Mon, 22 Dec 2003 17:01:00 -0500, "Marco Martin"
<ma**********@sympatico.ca> wrote:

Hi Group,

I need to come up with a sound Database design, that will allow me to
"Hiarchialy" save and extract data. The end result will someday be a Bill Of
Materials.

The basic idea is this:

Lets say we create a Widget:
The Widget is comprised of 3 items;
1) a Display screen
2) a keyboard
3) a power supply

the Display Screen, keyboard and power supllies are made up of different
components also.

So the data should look something like this:

WIDGET
- DisplayScreen
-DisplayScreen Casing
-plastic
-screws
-Paint
-Various electronic components
-Wires
- Keyboard
-keys
-wires
-switches
- PowerSupply
-Transformer
-Diodes
-capacitors

So My question is; What would be the best way to store this inside a
Database. Should it all come from the same table? If so, how will I keep
track of these parts(ie:plastic belongs to DisplayScreenCasing which belongs
to displayScreen which belongs to Widget.)? I've come up with a few
solutions, but none allow me to track the hierarchy efficiently without
writing hundreds of lines of code.

What i would like to do is have one table hold all the parts, and have ONE
lookup table which would hold the references to each part.

Any insight, ideas, or directions would be greatly appreciated.

Best regards, and happy holiday season,

Marco