I need to write a small DB to store product bill of materials (BOM). I made3 tables:

component: id, component_name;
// just a list of all components
assembly: id, assembly_name, isproduct;
// list of assemblies. product can consist of several assemblies - isproduct is a boolean to decide whether assembly is a final product
spec: id, assembly_id, component_id, qty;
// bill of materials itself (or specification) - stores qty of components in assemblies

One problem is that assembly itself can also be a component. And in given structure it results in the fact that component_id in spec table can hold foreign ids of two tables (component and assembly) How should i change theDB structure to overcome that problem?

Looking forward to community comments.
Thanks in advance for your answers.

Thanks, Phil! After some time I came with almost the same idea. But instead of "type" variable I introduced BOOL IsAssembly - to distinguish assemblies in components table.
Now I've got another problem - I am unable to build up a query (in MS Access) for collecting data from two tables, one of which contains two columns for foreign keys from another table.

<img src="https://lh4.googleusercontent.com/-x_yzVt4YF6k/TmpuchjbsrI/AAAAAAAAA-g/u2MkeK2sZbA/s800/%2525D0%252591%2525D0%2525B5%2525D0%2525B7%2525D1% 25258B%2525D0%2525BC%2525D1%25258F%2525D0%2525BD%2 525D0%2525BD%2525D1%25258B%2525D0%2525B9.PNG" />
Here is my current db structure.

Sergei Minayev <sergei.minayev (AT) gmail (DOT) com> wrote in
news:fc5ea70c-7dab-4f2d-a7dd-2b99271898cf (AT) glegroupsg2000goo (DOT) googlegro
ups.com:

It is possible to do this.

From the menu bar, select Tools, Options, Tables/Queries.
Uncheck the "Enable Autojoin" checkbox.
Open the Query design tool.
First, show the table with the 2 foreign keys,
TblJnComponentComponent
..
Then put your other table, TblComponents.
Put your TblComponents a second time.
Access will change the name of the second copy of the table.
You can change the aliases to more meaningful ones. I use ParentItems
and ChildItems.
Draw the relationships from the first table to the second and the
first table to the third.
Select the fields you want to see in the query and save then test it.
..



--
Bob Q.
PA is y I've altered my address.

On 09/09/2011 20:55:23, Sergei Minayev wrote:
%2525D0%252591%2525D0%2525B5%2525D0%2525B7%2525D1% 25258B%2525D0%2525BC%2525D1%25258F%2525D0%2525BD%2 525D0%2525BD%2525D1%25258B
%2525D0%2525B9.PNG"
Hi Sergei

Bob is spot on on the relationships you need.

Your earlier post about using a Boolean Type is that in the example I gave,
there were 3 types of components - finished components, assemplies and
alternatibe sources of those assemblies which I called Products.

In your relationship view, you do not need Id in tblSpecification.
Id_Assembly & ID_Component need to be joint keys. I stronlgly advise you hot
to use "ID" as the key to a table - you have it 3 times in 3 tables so noone
knows which ID you are referring to. Use ComponentID in the tblComponents and
MeasureID in the tblMeasurements and again as the foreign key in
tblComponents. I also query is_assembly and is_product. Not sure what
is_product is used for, but I can see a situation where both is_product and
is_assembly are both set to either true or false, and I guess if one is true,
the other is false. Sorry to be so picky, but "name" in your tblComponent is
a sort or reserved word, need surrounding with [] braces and is a pain. Use
ComponentName then you know what you are talking about.

Phil

It isn't really necessary to change the options to do this. Create the
query, add the tables, delete the 'autojoin' lines shown, and then draw
the join lines you wish.

Joan
Bob Quintal wrote:



--

"Joan Wild" <jwild (AT) nospamtyenet (DOT) com> wrote in
news:xn0hixu6q0000001 (AT) nntp (DOT) aioe.org:

While experienced users like you and I can deal with Access's being
'helpful', I've found that it often confuses neophytes.

I've also found that in a family tree structure like this one, that
it sometimes crashes when trying to add the relations.

Bob



--
Bob Q.
PA is y I've altered my address.

Ok! Thanks for the advice about ID field naming. I'll try to follow it.

I've got the idea. But there is no use in making separate table just for name. Cause both component and assembly can have more than 1 field in common.

On 10/09/2011 18:15:41, Bob Quintal wrote:
This has nothing to do with queries. The joins originally need to be set in
the relationships with referential integrity enforced. I grant you then when
you subsequently design a query, you need to sort out the joins from the 2
copies of the TblComponents to TblJnComponentComponent which should be the
same as shown in the relationships. The default name of the 2 copies of
TblComponents will be TblComponents and TblComponents_1. Make sure you join
ComponentID from TblComponents to Component1ID in TblJnComponentComponent and
ComponentID from TblComponents_1 to Component2ID in TblJnComponentComponent

Phil