BOM table design

[nina1]

I have a table in MS Access that looks like this:

Parent PName Child CName Clevel SupID Supplier ChildQty
11111 base 111-05 pppp A1 00555 uuuuuu 1
11111 base 111-04 llll B1 00553 mmmmmm 1
11111 base 111-07 hhhh c1 00333 dddddd 2
and so on.
I have abot 150 parents and 5-6 children for each parent plus some children might be subassembly so they will have subchildren. For example 111-05 is made of 111-051 and 111-052.
I want to design a good relational database with normalized structure of the tables.
Any help would be apprecialted!
Thanks a lot!

 

[Remou]

A good start would be to read:

http://www.r937.com/relational.html

 

[SkipVought]

Hi,

Supplier is NOT an attribute of a BOM. It is at best, a PART level attribute and at worse, at the level of part EFFECTIVITY (from, thru either date or model configuration)

The Component may, indeed, reference the part effectivity, with it is not unique to the usage in the BOM. In reality, when the configuration changes, the Part ID should also change.

Name or Nomenclature is also not a BOM attribute. It is an attribute of the Part.

I don't know what a Level A1, B1, C1 is. I know what an Indenture (level) 1, 2 or 3 is.

So we're down to...
[tt]
Parent
Child
Clevel
ChildQty
[/tt]
Sometimes a UsedOnOPER is included for complex assemblies, where scheduling of the parts kit is not necessarily uniform for the parent.

Skip,

_[red][/red]