We have a database of electrical components and I am trying to make sure I have a good design. I am not a database designer (my comfort zone is designing with these hardware components), so I am interested in some pointers that have more application to me than name/address type examples. I have read some basic blurbs on First, Second and Third Normal Forms.
The tables shown bellow are just examples, there are many more points of data and categories of data, but want it to be a good, efficient design. The columns of my first table where all data is required for every entry might be:
Internal_Part_Number(Set as PRIMARY KEY [PK])
Genre values is
'Capacitor', we need additional information where the first three, but there are several optional columns that should be provided like ESR if the data is available:
ESR(optional, not all
Componentshave these specified)
However, if the
Genre value is
'Resistor', we need different additional information with similar constraints on data as above:
Temperature_Coefficient(optional, but this never applies to
- Should I be splitting up my first table?
- Or, do all data columns that always exist for a given row be in a single table even though the data is not very well grouped?
For reference, our internal status of the part does not really care about the manufacturer and vice versa, But they are both necessary to describe
Internal_Part_Number, so I think they both belong in the same table don't they?
Then there are more data that further define a given
Part. Do I
JOIN those tables to the first table on
Genre when creating a view for that specific
How about when there are 50% of the attributes that are optional to populate to describe that
Part, should each one be its own table that you would
JOIN them all on
Obviously, we have a
Part_Number that can be used as PK.
If I normalize this, I would have my master table as above, but would be creating a separate table called genres(cap and res, etc). Then another table called parametric list( where all parametric names live), another table to relate genres to parametrics, then finally a table that would have part Id, parametric Id, and value.
However, sometimes that value is a string, sometimes it's an enumeration, sometimes it's a number with units.
How would I link these tables to each other and how would I create a sql query that could create a table specific to the genre?