Way around Composite Primary Key requiring Composite Foreign KeyComposite primary key plus a separate...
Why are "square law" devices important?
How can guns be countered by melee combat without raw-ability or exceptional explanations?
Can I do anything else with aspersions other than cast them?
Euler and minus sign
Coworker is trying to get me to sign his petition to run for office. How to decline politely?
Why do we interpret the accelerated expansion of the universe as the proof for the existence of dark energy?
In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?
Why is quixotic not Quixotic (a proper adjective)?
Now...where was I?
Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once
Isn't a semicolon (';') needed after a function declaration in C++?
What does an unprocessed RAW file look like?
Are all power cords made equal?
What happens if both players misunderstand the game state until it's too late?
Reduce Reflections
Build ASCII Podiums
Not sure how to set up the Laplacian/Poisson Equation
Is it Safe to Plug an Extension Cord Into a Power Strip?
Taking an academic pseudonym?
How Create a list of the first 10,000 digits of Pi and sum it?
How bad is a Computer Science course that doesn't teach Design Patterns?
What does @ mean in a hostname in DNS configuration?
Coworker asking me to not bring cakes due to self control issue. What should I do?
What is the name of this perspective and how is it constructed?
Way around Composite Primary Key requiring Composite Foreign Key
Composite primary key plus a separate (surrogate) id column for foreign key referencesComposite Primary Key column orderVARCHAR primary key - MySQLNormal form definitionsSurrogate vs composite key in hierarchical data structureConverting a composite primary key to composite unique, and adding an auto-incremented PK columnCan a Key Preserved Table have a Composite Primary Key, on Oracle db?Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)Composite Primary Key on partitioned tables, and Foreign KeysWhen should a primary key be meaningful?
Here is a table called Make (where name is the PK)
name|other columns
----------------------
A | ...
B | ...
C | ...
And a table called Type (where make, name together is the PK and make references Make.name as FK)
make|name|other columns
------------------------
A |SUV | ...
B |SUV | ...
B |UTE | ...
C |UTE | ...
Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)
make|type|name|other columns
-----------------------
A |SUV |CRV | ...
A |SUV |HRV | ...
B |UTE |DMAX| ...
It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.
What is the way to get rid of this redundancy?
I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?
schema normalization primary-key
asked 10 mins ago
user1589188user1589188
1164
add a comment |
Here is a table called Make (where name is the PK)
name|other columns
----------------------
A | ...
B | ...
C | ...
And a table called Type (where make, name together is the PK and make references Make.name as FK)
make|name|other columns
------------------------
A |SUV | ...
B |SUV | ...
B |UTE | ...
C |UTE | ...
Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)
make|type|name|other columns
-----------------------
A |SUV |CRV | ...
A |SUV |HRV | ...
B |UTE |DMAX| ...
It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.
What is the way to get rid of this redundancy?
I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?
schema normalization primary-key
asked 10 mins ago
user1589188user1589188
1164
add a comment |
Here is a table called Make (where name is the PK)
name|other columns
----------------------
A | ...
B | ...
C | ...
And a table called Type (where make, name together is the PK and make references Make.name as FK)
make|name|other columns
------------------------
A |SUV | ...
B |SUV | ...
B |UTE | ...
C |UTE | ...
Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)
make|type|name|other columns
-----------------------
A |SUV |CRV | ...
A |SUV |HRV | ...
B |UTE |DMAX| ...
It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.
What is the way to get rid of this redundancy?
I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?
schema normalization primary-key
asked 10 mins ago
user1589188user1589188
1164
Here is a table called Make (where name is the PK)
name|other columns
----------------------
A | ...
B | ...
C | ...
And a table called Type (where make, name together is the PK and make references Make.name as FK)
make|name|other columns
------------------------
A |SUV | ...
B |SUV | ...
B |UTE | ...
C |UTE | ...
Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)
make|type|name|other columns
-----------------------
A |SUV |CRV | ...
A |SUV |HRV | ...
B |UTE |DMAX| ...
It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.
What is the way to get rid of this redundancy?
I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?
schema normalization primary-key
schema normalization primary-key
asked 10 mins ago
user1589188user1589188
1164
asked 10 mins ago
user1589188user1589188
1164
asked 10 mins ago
user1589188user1589188
1164
asked 10 mins ago
user1589188user1589188
1164
asked 10 mins ago
user1589188user1589188
1164
1164
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230468%2fway-around-composite-primary-key-requiring-composite-foreign-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230468%2fway-around-composite-primary-key-requiring-composite-foreign-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
