Ygthkb

How can I put a period right after the algorithm's number in the algorithm's title?

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

Why is it that Bernie Sanders is always called a "socialist"?

Anti-aromatic and Non-aromatic compounds

Is there any danger of my neighbor having my wife's signature?

Why do objects rebound after hitting the ground?

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

Including proofs of known theorems in master's thesis

Problems formatting part entries in ToC with `titletoc`

I am a giant among ants

Was Opportunity's last message to Earth "My battery is low and it's getting dark"?

Is Screenshot Time-tracking Common?

GPSD issues on 16.4

Isn't a semicolon (';') needed after a function declaration in C++?

Can I legally make a website about boycotting a certain company?

Putting a vertical line in each Histogram using GraphicsGrid

Is there a way to pause a running process on Linux systems and resume later?

Is the UK legally prevented from having another referendum on Brexit?

Explicit Riemann Hilbert correspondence

Performance and power usage for Raspberry Pi in the Stratosphere

Is "accuse people to be racist" grammatical?

Is it possible to map from a parameter to a trajectory?

How to deal with an underperforming subordinate?

A sequence of orthogonal projection in Hilbert space

When creating a many to many relation with an attribute, should I include a non-composite primary key?

Single Identity column and composite key, which to make primary?How is INDEX on Composite Primary Key in mysql?MySQL non-sequential primary key of certain lengthComposite primary key from multiple tables / multiple foreign keysCreate relationship between two tables with composite primary keyNon-sequential Primary Key performance in PostgreSQL 9.3Do I need a composite primary key in a junction table?Duplicate key error for composite primary key when running update query without having duplicateComposite vs auto incrementing integer as Primary keyGetting around Primary Key requiring non-nullable columns

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?

mysql database-design primary-key

share|improve this question

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

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?

mysql database-design primary-key

share|improve this question

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

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?

mysql database-design primary-key

share|improve this question

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

frame(id, motor_attribute);

voltage(id, voltage_attribute);

efficiency(frame_id, voltage_id, efficiency_attribute);

//composite key = (frame_id, voltage_id)

efficiency(id, frame_id, voltage_id, efficiency_attribute);

//composite key = (frame_id, voltage_id)

//primary key = (id)

share|improve this question

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

share|improve this question

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

edited 1 min ago

MDCCL

6,84331745

edited 1 min ago

MDCCL

6,84331745

asked Feb 8 at 17:18

DennisDennis

1063

asked Feb 8 at 17:18

DennisDennis

1063

1 Answer
1

active

oldest

votes

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.

share|improve this answer

edited Feb 11 at 23:13

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229251%2fwhen-creating-a-many-to-many-relation-with-an-attribute-should-i-include-a-non%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

edited Feb 11 at 23:13

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

edited Feb 11 at 23:13

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

edited Feb 11 at 23:13

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

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

efficiency ENUM('unknown', "ie1", ...)  NOT NULL

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

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

voltage_id TINYINT UNSIGNED NOT NULL ... and another table

efficiency_id ...

share|improve this answer

edited Feb 11 at 23:13

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259

answered Feb 9 at 3:15

Rick JamesRick James

42.9k22259