Table frenzy

[r4tional]

Hi all,

I have a question regarding tables. I hope I can get some clarity and ideas from you all.

I have a table of mechanical parts, eg. pistons, bearings, valves, lubricants etc.

tPARTS
- ID_PARTS
- PART NUMBER
- MANUFACTURER
- QTY PER PACK

I want to store additional information about each part, eg. dimensions, materials etc. To do this, I thought of creating another table:

tSPECS
- ID_SPECS
- ID_PART
- FIELD 1
- FIELD 2
.
.
.


However, the set of fields for each part type varies. I have listed some below to illustrate this point:

PISTONS:
- MATERIAL
- DIAMETER
- COMPRESSION HEIGHT
- BOWL DEPTH
- PIN DIAMETER
- PISTON DESIGN

BEARINGS:
- HOUSING BORE
- BEARING WIDTH
- BEARING POSITION

VALVES:
- HEAD DIAMETER
- SKIRT DIAMETER
- SEAT ANGLE
- SKIRT MATERIAL

LUBRICANTS:
- APPLICATION TYPE
- VISCOSITY
- FLASH POINT
- SERVICE RATING

So, if I create a table that contains all the above fields, each row (record relating to a part in the tPARTS table) would only have data in a few of the columns (specifications fields that are applicable for that part), while the rest are blank. That seems inefficient. Am I right?

How else can I store the specifications? Do I create a table of specs for each part type?

tSPECS_PISTONS
- ID_SPECS_PISTONS
- ID_PART
- MATERIAL
- DIAMETER
- COMPRESSION HEIGHT
- BOWL DEPTH
- PIN DIAMETER
- PISTON DESIGN

.
.
.

tSPECS_LUBRICANTS
- ID_SPECS_LUBRICANTS
- ID_PART
- APPLICATION TYPE
- VISCOSITY
- FLASH POINT
- SERVICE RATING

If I take this approach, how do I link each specs table to the tPARTS table? Does this approach break any database design rules?

This has been driving me a little insane, so I appreciate you taking the time to read this. I welcome all your your ideas and clarifications.

Thank you.

 

[KenHigg]

What you have will work. Another way would be to have a generic specs table. It eould link back to the parts table. This one be a one to one relationship. In this generic table you would have, say 20 flds named something like fld1, fld2, etc.. Then you have another table which would contain spec definitions. One fld would be spec name. Here you would put 'Lubricant', or 'Piston', etc.. Then you would have a text fld for each fld in the generic specs table. so... What happens is you have a part in the main table. This table has a fld for part type. This tables links to the generic specs table for the spec value, like 'Flash point' or 'Diameter'. Then you go to the spec definition table for the spec fld name for each type of part. Or you could just have say 20 generic spec flds in the main table and not have a seperate table. Hope this makes sense. Not sure I would go to all this trouble myself... How many part number records do you anticipate and how many different types of parts?

 

[AlexN]

With the information given, and more I could imagine, the attached schema should do the job.

On your data entry form, lookup (combobox) controls will help you enter all data needed.

But,

You have to define the information you have, how it is related, and the information you need as an output prior to design tables. This will make things easier and save you a lot of trouble later on.

 

[jdraw]

You may get some ideas in the dialog and links re subclassing referenced in this post.

 

[r4tional]

Thank you for the replies and ideas. Much appreciated!

AlexN thank you for taking the time to do up the example. The approach feels elegant. I am looking into it right now.

jdraw thank you for that link! No way I would have found that otherwise. I am reading my way through that thread.

KenHigg, there are about 20 different part types and over 1000 part number records. I need to get this spot on because I want to build inventory control etc. on top of it.