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

 

[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...

 

[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!