Quick Q about Product Tables (parts)

[RickDB]

OK, here's my delima (my head is spinning)

I have a standard Product table:

tblProducts
ProductID (PK)
ProductName
VendorName
Description
UnitPrice
InStock

Every Product has many Parts, but one Part may go to many Products

I will build this table:

tblProductParts
PartID (PK)
PartName
VendorName
Description
UnitPrice
InStock

How would I relate them together? I figure I need to use a Many to Many, with this table inbetween:

tblProductPartsList
ProductPartsListID (PK)
ProductID (FK to tblProducts)
PartID (FK to tblProductParts)

I feel like I'm missing something, can you let me know what you think???
THANKS!!!

 

[RuralGuy]

You need to search on BOM or Bill Of Materials. Here's a start:
Bill Of Materials
BOM, with Joe Celko Nested Sets

 

[Pat Hartman]

The relation table doesn't need an arbitrary autonumber pk since it has a natural two-column key and this particular table is unlikely to have any child tables.

tblProductPartsList
ProductID (PK fld1, FK to tblProducts)
PartID (PK fld2, FK to tblProductParts)
Quantity
other data relative to the junction of these two fields.

A better design, one that will allow for an infinite number of levels in the bill, uses a single Parts table rather than a product table and a parts table. The product/parts design that you have only supports a two-level bill. Using a single table, the junction table becomes:

tblProductPartsList
ProductID (PK fld1, FK to tblProducts)
PartID (PK fld2, FK to tblProducts)
Quantity

With the single table approach, you may need to identify SKUs or saleable items. Just add a flag to the parts table to indicate that this is a part that should be listed in your catalog. Queries will pull out only the parts you sell.

 

[RickDB]

Pat, I like the suggestion to eliminate the tblProductParts and create a layout where I can have multiple layers of parts based off of the single parts table instead of the current 2 level design...


How do I set up the relationships though? What I have tried does not work:

tblProducts
VendorID
ProductName
PartID
Description
UnitPrice
InStock

Junction table:

tbleProductPartsList
ProductID (Composite PK)
PartID (Composite PK w/ ^ )
Details

I get the error message "No unique index found for the referenced field of the primary table."

I am missing something here, but cannot see how I need to relate the junction table back to the tblProducts... Do I need 2 instances of the tblProducts in the Relationships window?

I am lost...

 

[Pat Hartman]

The junction table needs to join the primary key of tbl1 with the primary key of tbl2 (or instance 2 of tbl1). The message indicates that PartID is not the primary key of tblProducts. It needs to be in order to be used as a foreign key.

 

[RickDB]

So the attached method will wwork? It seems to... I just want to make sure I don't find a huge problem with it a month down the road and I'm screwed.

Thanks for the replies Pat, it's greatly appreciated!

Also, I spent a bit of time trying to wrap my head around the Celk-BOM database RuralGuy posted, but it makes me feel like Nigel from Spinal Tap. Is it a good method to use here? Thanks for that tip also RuralGuy!