1

For example, I have frame and voltage tables, both of which relate to a motor object:

frame(id, motor_attribute);
voltage(id, voltage_attribute);

I can pick a frame and pick a voltage and for each combination of those there is an assigned efficiency attribute.

I can thus create an efficiency table, but how? I see two choices:

efficiency(frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)

OR

efficiency(id, frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)
//primary key = (id)

Do I include a non-composite primary key into the table?

  • 2
    Don't do anything without a reason. A composite key is perfectly fine for this example. Generally non logical keys should be avoided. There are reasons to generate an identity based key or a random guid based key but it usually is when there is no logical value to use for the key. That is not the case here. – Shooter McGavin Feb 8 at 17:23
0

What are voltage and efficiency? Simple numbers (SMALLINT) or FLOATs? Then do not normalize them and do not use a many:many table. Simply have the value in the table instead of an id pointing to the value. There is such a thing as "over-normalization"; it does not scale well.

Use many:many mapping tables for relating things that have lots of attributes.

Back to your question... A many:many table does not need a surrogate id for the PK. It is not just OK to have a composite PK, it is actually more efficient to have such. Save AUTO_INCREMENT for cases where there is no good "natural PK".

Other tips on many:many tables. Note that it advises having a secondary key for when you need to 'go the other direction'.

(Based on Comment):

Using ENUMs:

voltage ENUM('unknown', "460/60/3", ...)  NOT NULL
efficiency ENUM('unknown', "ie1", ...)  NOT NULL

Using a string:

voltage VARCHAR(15)   CHARACTER SET ascii NOT NULL DEFAULT ''
efficiency VARCHAR(8) CHARACTER SET ascii NOT NULL DEFAULT ''

Normalizing:

voltage_id TINYINT UNSIGNED NOT NULL ... and another table
efficiency_id ...

If there are very few choices, and the list of choices changes only very rarely, ENUM may be optimal. The 'string' option is not too bad since the strings are rather short. I feel that the normalizing is "over-normalizing" in this case, namely only a few short strings.

  • voltage in my case is a description string, such as "460/60/3" (voltage, hz, phase). Efficiency is a string such as "ie1". There are only three of each, so a cross-product will produce 9 values. Are you suggesting just creating a single table that contains a cross-product of all those? – Dennis Feb 11 at 23:04
  • @Dennis - I added to my Answer. I don't think I am recommending a cross-product. As for the PK. we need to see not only the full table, but also some typical queries. – Rick James Feb 11 at 23:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.