Table design puzzle

[L'apprentis]

Hi
I have been struggling for quite a wee while thinking the design of a part of my Database, it is maybe not that complex but seems quite difficult to me...
I have different ASSEMBLIES made of differents PARTS (Bodies, Straps, Hinge, Screws...) and most of them are built on the same models but there is quite a few odd ones.
Most assemblies are made of 1 Body, 2 Straps, 2 Screws and 2 Hinge pins
but some can be made of 1 Bodies, 2Screws and 2Hinge pins only
or 1 Bodies and 4 Screws
there is quite a few configuration and some can be made of really unusual parts that are only gonna be used once or twice.

There is different models for each part So I have created TblStraps, TbleScrews, TblHinge..., Tbl Miscellaneous (regrouping all the odd ones)

This is where I am getting confused because I want to make it easy for the user to fill in a new assembly detail.

Each assembly is defined by is body,

and I could use a query to show the number of parts for each AssemblyModel.
Should I have a table for each assembly model as shown below:

TblModel1:............................TblModel2:.............................................
BodyId (Primary).....................BodyId (Primary)....................................
StrapId (linked to TblSrap)........ScrewId (Linked to TblScrew)..................
ScrewId (Linked to TblScrew)....HingeId (Linked to TblHinge)....................
HingeId (Linked to TblHinge)

and a table for the miscelaneous models who is going to have quite a few fields because some of those models can have 1 or 2 types of srews and Hinge pins and up to 8 more parts, lots of the Records will have blank fields as well.
Ohhhh, I don't know where to start, I am confused and needs some advice, I feel bad to beg but I am really desperate

 

[L'apprentis]

Thank you so much

Uncle Gizmo and Pat your help is beyond helpfull. I am going to apply this layout to my system. Pat as you advise I am going to merge TableAssembly and Tablepart as it is the proper way to design a BOM.
Therefore, if I understood well, my relationship should be similar to:

tblItem
ItemID (autonumber primary key)
MfgItemNum
Nomenclature
CategoryID (foreign key to tblCategory)
etc.

tblAssemblyParts
AssemblyID (primary key fld1, foreign key to tblItem.ItemID)
PartID (primary key fld2, foreign key to tblItem.ItemID)
Quantity
etc.

I am going to keep a table for category:

tblCategory
CategoryID (autonumber primary key)
CatName

Hope it's right, thanks again.

 

[L'apprentis]

Getting there (BOM)

I have been searching the forum for BOM thread but could'nt find any similarities with the situation I am in just now. I took a snapshot of the table concerned for ease of understanding.
I need to link Some Data to the 'Parent' Assembly only, therefore I am going to get blank field in the table : I am not sure that I should have does blank field in the table, this is why i am a bit doubtfull...

Also, each assembly are supposed to be fitted around an oil Tubing which diameter is included in the nomenclature of some the Items. (A=Assembly, B=Body or C=Straps + Pipe Size + Drawing numbers))

ie: A5500-A60 = Final Assembly------Diameter 5500
A3500-A59 = Final Assembly -----Diameter 3500
B5500-G26 = Body---------------Diameter 5500
B5500-A60 = Body---------------Diameter 5500
B3500-A59 = Body---------------Diameter 3500
C5500-830S = Straps------------Diameter 5500

I have already a Table featuring the different Pipe sizes already in use within the database, and I need to link this table to the Item Classed as Assembly, Body and Straps only, this will also create blanks, does it really matter?

 

[L'apprentis]

Concerning the Previous Post...
Is it acceptable to include a Query inside the relationship window?
I was thinking of linking the table PipeSize to a query of the TblItem.

 

[L'apprentis]

Ok...I think I may still include the query inside the relationship window to ease the understanding of the connection between some of the fields unless it is a major programmer mistake.
I apologize for being a pain but I still got a problem with what I mentioned in the second last post (Getting there (BOM)). Is it accepable to create recurant blank fields in a table?

Pat, concerning the submarine in Aberdeen, I don't think there is any American Subs hosted in Aberdeen. I came over here 5 years ago from France and I've only seen fishing and offshore related boat in the harbour but maybe I'm wrong.

 

[L'apprentis]

In the TblPart, some items are related to a Tubing Diameter but not all of them. I will, therefore, have a TubingSize field in the TblPart which contains values only for the parts concerned, all the other records for that field are going to be null.

 

[L'apprentis]

Thanks Pat.