| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > boms
Expert: Scottgem
Date: 10/9/2008
Subject: boms
Question
I am making a database to track the compliance of our parts. Our parts have BOM's with upper components using subcomponents. Sometimes something may be made from our upper components as well. My PK is 2fields partnumber and rev. I am not sure how to create BOM's. I am trying to figure out what I am going to do about component parts. Thinking to create two tables one tblBOM (autoPK) and another tblComponent(2fieldPK) with tblBOM connecting tblProducts and tblComponents. Thanks
Answer
First I do NOT recommend using compound keys. Especially when they will be used for foreign keys. You should use an autonumber primary key for all tables and use that as your foreign key.
In your situation, you have components that make up products, but a product can be included as a component of another product. So you should have a single table of components. You can add a flag in that record to indicate that a component is, itself a product of other components.
You then need a BOM table. The basic structure would be like:
tblBOM
BOMID (PK autonumber)
ProductID (FK)
ComponentID (FK)
ProductID, in this case, would link back to the components table.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer
Ask a Question
|
|
