I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on...
In order to make this question useful for future visitors I like to keep this question universal since managing parts in a DB is very common no matter what parts are in the DB (CDs, cars, food, students, ...).
I am thinking of 3 different DB designs:
Using a parts table and derived tables for specialized part attributes.
Parts (id, part_type_id, name) PartTypes (id, name) Wires (id, part_id, lenght, diameter, material) Contacts (id, part_id, description, picture)
Using only specialized part tables.
Wires (id, name, lenght, diameter, material) Contacts (id, name, description, picture)
Using a Parts-, PartTypes-, ValueTypes- and PartValues table that contain all values.
PartTypes (id, name) ValueTypes (id, part_type_id, name) Parts (id, part_type_id, name) PartValues (part_id, value_type_id, value)
Which one to prefer and why? Or is there a better one?
I am concerned about the DB queries. I don't want the queries become overly slow or complicated.
The number of types in the DB are pretty much given and static since they rest on a international standard and will be enhanced seldomly.