Hi all,

I am designing a database to keep my company's product details.
There are parts, subassemblies and assemblies.
They go all in the table PART. And then I am suing a table ASSEMBLY
to define the hierarchies.

So far so good.

The problem is, I don't know whete to keep the part specific details
(for example weight, colour, size, power,etc)

I have about 70 different part types which are described by very different
attributes (like for engines the horsepower, rpms, weight might be interesting. For pumps it is only necessary to store the flow and pressure increase)

Up to now I was thinking of using a separate table for every part type and link them to the table PART as necessary. In this way I'll end up having some 70 tables...there must be a better solution.
Any suggestions??

Thank you for your comments.

being a bit of a fanatic on data normalisation I probably go too far the other way, so the way I coded a catalogue system where I had/have no idea what it was going to be used for is;

An options table where the names of the attributes are listed;

id             autoincrement (integer);
active       enum;    Y or N 
name       varchar;  
comment  varchar;   information about  the record

option_details this is where the variables for each option are defined

id                 autoincrement (integer);
option_id       integer; foreign key to options.id
value            varchar;  value for this attribute
name            varchar;  name for the value (used for user friendly info)

options_prod Table for mapping the product table IDs to the options group and values

id                   autoincrement;
opt_group_id   integer; foreign key to options.id
prod_id           bigint;  foreign key to products.id 
opt_details_id  text; foreign keys to option_details.id.  this field is stored as a comma seperated value so can be used with a IN(csv) query

to use taking clothes as an example;

options table would contain records of
1. material,
2. weight,
3. colour,

option_details would contain records of
1. 1. Silk
2. 1. Cotton
3. 1. Wool
4. 2. Size 10
5. 2. Size 12
6. 2. Size 14
7. 3. Red
8. 3. Blue
9. 3. Purple

options_prod would be,
1. 1. 5. 1,2
2. 2. 5. 4,5,6
3. 3. 5. 8,9

which equates to prod Id 5 is available in silk & cotton, with sizes of 10, 12 & 14 and in colours of Blue & Purple.