Composite primary key plus a separate (surrogate) id column for foreign key referencesHow do I design a...
Run a command that requires sudo after a time has passed
Publication rates for different areas of mathematics?
Should corporate security training be tailored based on a users' job role?
Copy the content of an environment
Workplace intimidation due to child's chronic health condition
Would life expectancy increase if we replaced healthy organs with artificial ones?
Found a major flaw in paper from home university – to which I would like to return
"Cheaper by the dozen" phrase origin?
How to explain one side of Super Earth is smoother than the other side?
Why is it a problem for Freddie if the guys from Munich did what he wanted?
Why does finding small effects in large studies indicate publication bias?
How to draw these kind of adjacent ovals with arrows in latex?
Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?
Which was the first story to feature helmets which reads your mind to control a machine?
Apparently I’m calling random numbers but nothing in call log?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
Identical projects by students at two different colleges: still plagiarism?
Why did Tywin never remarry?
Taking an academic pseudonym?
How to write a character overlapping another character
Is it possible to detect 100% of SQLi with a simple regex?
Why does Python copy numpy arrays where the length of the dimensions are the same?
Is there a technology capable of disabling the whole of Earth's satellitle network?
Father gets chickenpox, but doesn't infect his two children. How is this possible?
Composite primary key plus a separate (surrogate) id column for foreign key references
How do I design a database for continents, countries, regions, cities and POIs?Replacing composite key with surrogateIs it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?Can a surrogate key and a primary key be in the same table?Record versioning and promotionComposite Primary Key column orderA form including content from two tablesSurrogate vs composite key in hierarchical data structureForeign key as part of composite primary key or not?Avoiding foreign key repetition on related models in a relational DB
I am trying to build a database about locations like so:
- Country → Region → Town
Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:
The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.
database-design primary-key erd surrogate-key candidate-key
edited 2 hours ago
MDCCL
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
|
show 1 more comment
I am trying to build a database about locations like so:
- Country → Region → Town
Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:
The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.
database-design primary-key erd surrogate-key candidate-key
edited 2 hours ago
MDCCL
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
1
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48
|
show 1 more comment
I am trying to build a database about locations like so:
- Country → Region → Town
Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:
The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.
database-design primary-key erd surrogate-key candidate-key
edited 2 hours ago
MDCCL
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
I am trying to build a database about locations like so:
- Country → Region → Town
Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:
The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.
database-design primary-key erd surrogate-key candidate-key
database-design primary-key erd surrogate-key candidate-key
edited 2 hours ago
MDCCL
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
edited 2 hours ago
MDCCL
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
edited 2 hours ago
MDCCL
6,75731745
edited 2 hours ago
MDCCL
6,75731745
edited 2 hours ago
MDCCL
6,75731745
6,75731745
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
asked Jun 3 '14 at 19:00
the_peacockthe_peacock
133
133
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
1
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48
|
show 1 more comment
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
1
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
1
1
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48
|
show 1 more comment
2 Answers
2
active
oldest
votes
I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.
You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
add a comment |
I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
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
});
}
});
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%2f66495%2fcomposite-primary-key-plus-a-separate-surrogate-id-column-for-foreign-key-refe%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.
You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
add a comment |
I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.
You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
add a comment |
I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.
You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.
You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
answered Jun 3 '14 at 21:42
Jaaz ColeJaaz Cole
23817
23817
add a comment |
add a comment |
I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
add a comment |
I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
add a comment |
I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
answered Jun 4 '14 at 6:28
Elmozamil ElamirElmozamil Elamir
11
11
add a comment |
add a comment |
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%2f66495%2fcomposite-primary-key-plus-a-separate-surrogate-id-column-for-foreign-key-refe%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
town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
– Jaaz Cole
Jun 3 '14 at 21:02
@JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
– the_peacock
Jun 3 '14 at 21:08
1
Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
– Jaaz Cole
Jun 3 '14 at 21:18
@JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
– the_peacock
Jun 3 '14 at 21:34
I added it, it's been fun.
– Jaaz Cole
Jun 3 '14 at 21:48