Database Design own Column or just Query
I have a spreadsheet with movies, in most countries these movies have the same name, except a few.
That's why I have an extra table for the movie names.
Movie:
id,
[...]
MovieName:
id,
movieID (FK Movie),
name,
country
I n case there are different names, every person should get an overview with all names.
Should I check each time if there are different name for this movie?
Or would an extra Movie column "differentNames" (Bool) be better?
database database-design
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
add a comment |
I have a spreadsheet with movies, in most countries these movies have the same name, except a few.
That's why I have an extra table for the movie names.
Movie:
id,
[...]
MovieName:
id,
movieID (FK Movie),
name,
country
I n case there are different names, every person should get an overview with all names.
Should I check each time if there are different name for this movie?
Or would an extra Movie column "differentNames" (Bool) be better?
database database-design
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
add a comment |
I have a spreadsheet with movies, in most countries these movies have the same name, except a few.
That's why I have an extra table for the movie names.
Movie:
id,
[...]
MovieName:
id,
movieID (FK Movie),
name,
country
I n case there are different names, every person should get an overview with all names.
Should I check each time if there are different name for this movie?
Or would an extra Movie column "differentNames" (Bool) be better?
database database-design
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
I have a spreadsheet with movies, in most countries these movies have the same name, except a few.
That's why I have an extra table for the movie names.
Movie:
id,
[...]
MovieName:
id,
movieID (FK Movie),
name,
country
I n case there are different names, every person should get an overview with all names.
Should I check each time if there are different name for this movie?
Or would an extra Movie column "differentNames" (Bool) be better?
database database-design
database database-design
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
asked Nov 18 '18 at 12:01
Lumpi01Lumpi01
103
103
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
What you're asking about is denormalization which is moving derivable data from a child table to a parent table.
Denormalization is used to avoid performance impacts, which can be helpful when it's needed. It has a downside though, which is that you have to do extra work to keep the data consistent between the parent table and the child table. Since there's extra work to do and more points of failure, there's a risk that these could get out of sync. Avoiding inconsistent data is one of the main purposes of normalization in the first place.
It's generally acceptable to use denormalization in situations where (a) no other practical options exist to make your code adequately performant, and (b) where the data is more or less read only (i.e. it isn't expected to change, or at least very much). The basic principles at play here are: 1. Don't pre-optimize and 2. Manage your risk.
Based on this, you can decide whether your situation justifies denormalization.
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
add a comment |
The proposed two-table solution is usual in case of data localization.
You may also store an original movie country in the Movie table and store only different names in MovieName. However, you need to maintain this data integrity (unique movie_id + name) in addition to unique movie_id + country.
answered Nov 19 '18 at 14:08
sergeserge
61047
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53360644%2fdatabase-design-own-column-or-just-query%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
What you're asking about is denormalization which is moving derivable data from a child table to a parent table.
Denormalization is used to avoid performance impacts, which can be helpful when it's needed. It has a downside though, which is that you have to do extra work to keep the data consistent between the parent table and the child table. Since there's extra work to do and more points of failure, there's a risk that these could get out of sync. Avoiding inconsistent data is one of the main purposes of normalization in the first place.
It's generally acceptable to use denormalization in situations where (a) no other practical options exist to make your code adequately performant, and (b) where the data is more or less read only (i.e. it isn't expected to change, or at least very much). The basic principles at play here are: 1. Don't pre-optimize and 2. Manage your risk.
Based on this, you can decide whether your situation justifies denormalization.
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
add a comment |
What you're asking about is denormalization which is moving derivable data from a child table to a parent table.
Denormalization is used to avoid performance impacts, which can be helpful when it's needed. It has a downside though, which is that you have to do extra work to keep the data consistent between the parent table and the child table. Since there's extra work to do and more points of failure, there's a risk that these could get out of sync. Avoiding inconsistent data is one of the main purposes of normalization in the first place.
It's generally acceptable to use denormalization in situations where (a) no other practical options exist to make your code adequately performant, and (b) where the data is more or less read only (i.e. it isn't expected to change, or at least very much). The basic principles at play here are: 1. Don't pre-optimize and 2. Manage your risk.
Based on this, you can decide whether your situation justifies denormalization.
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
add a comment |
What you're asking about is denormalization which is moving derivable data from a child table to a parent table.
Denormalization is used to avoid performance impacts, which can be helpful when it's needed. It has a downside though, which is that you have to do extra work to keep the data consistent between the parent table and the child table. Since there's extra work to do and more points of failure, there's a risk that these could get out of sync. Avoiding inconsistent data is one of the main purposes of normalization in the first place.
It's generally acceptable to use denormalization in situations where (a) no other practical options exist to make your code adequately performant, and (b) where the data is more or less read only (i.e. it isn't expected to change, or at least very much). The basic principles at play here are: 1. Don't pre-optimize and 2. Manage your risk.
Based on this, you can decide whether your situation justifies denormalization.
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
What you're asking about is denormalization which is moving derivable data from a child table to a parent table.
Denormalization is used to avoid performance impacts, which can be helpful when it's needed. It has a downside though, which is that you have to do extra work to keep the data consistent between the parent table and the child table. Since there's extra work to do and more points of failure, there's a risk that these could get out of sync. Avoiding inconsistent data is one of the main purposes of normalization in the first place.
It's generally acceptable to use denormalization in situations where (a) no other practical options exist to make your code adequately performant, and (b) where the data is more or less read only (i.e. it isn't expected to change, or at least very much). The basic principles at play here are: 1. Don't pre-optimize and 2. Manage your risk.
Based on this, you can decide whether your situation justifies denormalization.
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
answered Nov 18 '18 at 13:01
Joel BrownJoel Brown
11.2k33652
11.2k33652
add a comment |
add a comment |
The proposed two-table solution is usual in case of data localization.
You may also store an original movie country in the Movie table and store only different names in MovieName. However, you need to maintain this data integrity (unique movie_id + name) in addition to unique movie_id + country.
answered Nov 19 '18 at 14:08
sergeserge
61047
add a comment |
The proposed two-table solution is usual in case of data localization.
You may also store an original movie country in the Movie table and store only different names in MovieName. However, you need to maintain this data integrity (unique movie_id + name) in addition to unique movie_id + country.
answered Nov 19 '18 at 14:08
sergeserge
61047
add a comment |
The proposed two-table solution is usual in case of data localization.
You may also store an original movie country in the Movie table and store only different names in MovieName. However, you need to maintain this data integrity (unique movie_id + name) in addition to unique movie_id + country.
answered Nov 19 '18 at 14:08
sergeserge
61047
The proposed two-table solution is usual in case of data localization.
You may also store an original movie country in the Movie table and store only different names in MovieName. However, you need to maintain this data integrity (unique movie_id + name) in addition to unique movie_id + country.
answered Nov 19 '18 at 14:08
sergeserge
61047
answered Nov 19 '18 at 14:08
sergeserge
61047
answered Nov 19 '18 at 14:08
sergeserge
61047
answered Nov 19 '18 at 14:08
sergeserge
61047
61047
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53360644%2fdatabase-design-own-column-or-just-query%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
