Help wanted for initial catalogue database design

[Gold_Ninja]

Hi, Thanks for taking the time to look at this, I hope I can explain it well . . .

I am trying to build a 'catalogue' database to assist pricing of large bolted assemblies.

I have a list of about 500 parts, such as 12mm bolt, 12mm nut, 1m girder etc.

I would like to be able to create & save sub-assemblies. These are collections of parts. An example sub-assembly might be: "1m A-Frame". Contains: 24 x 12mm Bolt, 24 x 12mm Nut, 6 x 1m Girder.

Finally the overall job/quote would be a collection of sub-assemblies.

Here's a screen shot of the current solution in Excel.

I would like for my users to be able to put together a quote by choosing from a list of Sub-assemblies (or creating a new one if necessary), and have it produce something like the above Excel sheet.

My question is: how do I go about storing the sub-assemblies? Some will have more than 100 parts.
Should I create a table with one field for "Sub-Assembly Name" followed by ~200 fields to store the parts (Plus another ~200 for storing each part's quantity)?


Any design advice would be greatly appreciated.

Thanks

 

[Norie]

Sounds like a many-to-many relationship.

A sub-assembly can have many parts and a part can be used in many sub-assemblies.

If that's right you should have a table for sub-assemblies, a table for parts and a 'junction' table to link the two.

The 'junction' table would have at least 2 fields, one for sub-assembly primary key and one for the part primary key.

So for a sub-assembly with 200 parts there would be 200 records in the 'junction' table, each with the sub-assembly primary key and the primary key for a part.

That should take care of the sub-assembly/parts relationship and get you started.

There are probably a similar relationship between job/quote and sub assemblies, so the table setup would be similar.

 

[Gold_Ninja]

Thanks! I will try this out tomorrow.

 

[Gold_Ninja]

Norie, Thanks for the help, I used your suggestion to get the sub-assemblies working perfectly.

I imagine that I will be able to create the job tables using a similar method.

 

[Gold_Ninja]

I was able to complete the bones of my database thanks to your help.