Table design question 2

[TCARPENTER]

I'm stumped. I have been working on a database to track a product assembly. The problem is this: Product can consist of an Assembly (or Assemblies), which can contain a sub-assembly (or more), which can contain components. However, Product can contain just components, just sub-assemblies, or just assemblies. I thought I was alright with just 4 tables:

Product_Table
Product_Recnum
Product_Desc
Assembly_Recnum
SubAssembly_Recnum
Component_Recnum

Assembly_Table
Assembly_Recnum
Assembly_Desc

SubAssembly_Table
SubAssembly_Recnum
SubAssembly_Desc

Component_Table
Component_Recnum
Component_Desc

All seemed well - created the relationships, forms etc., until a user mentioned sometimes entering the data was becoming tedious as some assemblies contain 25 components with different sub-assemblies (not containing components)...
you get the idea.

My question is this; should my tables include Foreign Keys for each possible configuration (in other words should SubAssembly contain a Foreign Key for components, then should Assembly contain a Foreign Key for Components and SubAssembly?) After looking at the tables I realized I haven't really associated say, the components to sub-assemblies, or the components to an assembly etc. I have read more than a few articles on Heirarchy tables - is this a good case for one? Although, after reading the articles, I'm not convinced I understand how to apply the concepts to an Access database - or do I need to just do something fancier with my forms?

Any advice would be greatly appreciated.

 

[SkipVought]

Hi,

I am an IT guy with 20+ years of experience in the aircraft manufacturing industry. So I've dealt with indented bills of meterial, (parent child relationships).

ONE TABLE!

You have
Parent
Component

and of course all the other BOM related data like QtyPerParent, ComponentType

Any Component that is also a subassy appears in the table as a Parent

Any Component that is raw material or a purchased part, appears ONLY as a Component.

A part is a part.

Hope this helps

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[LittleSmudge]

This sounds like you aught to swing into OO thinking.


The only really have one 'thing' in relational terms and that is a component.
True, a component my consists of multiple other components, which themselves contain components - but they are components none the less.
Some components may be complete finished products and other are 'raw material' or sub-assembled, but they are all components.

In maufacturing terms you now have a SBOM ( Structured Bill Of Materials )

So you just need a tblComponent

Then you get to consider .. ..

Can a specific component ( that you call an Assembly) be used in more than one Product ?

Can specific SubAssembly components be used in more than one Assembly component

Can specific 'Components' be used in more than one Product, Assemply or SubAssemply ?

If the answer to any one of the three questions is YES then you need a Many-to-Many relationship between the Component table and other records in the Component table

tblComprises
ComprisesId
ParentRef FK to tblComponent.ComponentId
ChildRef FK to tblComponent.ComponentId


You then populate tblComprises, the link to the Product goes in ParentRef and the link to the Assembly or component goes in ChildRef


On a Form you can do this by having a main form bound to tblComponent and tblComprises bound to a subForm
With the 'Product' compoent in view on the main form you'll see the constituent parts listed on the subform. Use a combo box bound to tblComponent to allow the user to select appropriate additional components to add to this level of the structure.


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TCARPENTER]

I see once again I've forgotten the shortest distance between two points is line - not a curve the opposite direction - Thanks!