2

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.

Sample tables

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])
  • Status
  • Genre
  • Description
  • Manufacturer
  • Manufacturer_Part_Number

If the 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:

  • Internal_Part_Number
  • Capacitance
  • Voltage
  • ESR (optional, not all Components have these specified)

However, if the Genre value is 'Resistor', we need different additional information with similar constraints on data as above:

  • Internal_Part_Number
  • Resistance
  • Power_Rating
  • Temperature_Coefficient (optional, but this never applies to Capacitors)

Questions

  • 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 Genre?

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 Internal_Part_Number?

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.