Database Design own Column or just Query
up vote
0
down vote
favorite
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 at 12:01
Lumpi01
123
add a comment |
up vote
0
down vote
favorite
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 at 12:01
Lumpi01
123
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 12:01
Lumpi01
123
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 at 12:01
Lumpi01
123
asked Nov 18 at 12:01
Lumpi01
123
asked Nov 18 at 12:01
Lumpi01
123
asked Nov 18 at 12:01
Lumpi01
123
asked Nov 18 at 12:01
Lumpi01
123
123
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
accepted
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 at 13:01
Joel Brown
11.2k33652
add a comment |
up vote
0
down vote
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 at 14:08
serge
54037
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
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 at 13:01
Joel Brown
11.2k33652
add a comment |
up vote
0
down vote
accepted
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 at 13:01
Joel Brown
11.2k33652
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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 at 13:01
Joel 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 at 13:01
Joel Brown
11.2k33652
answered Nov 18 at 13:01
Joel Brown
11.2k33652
answered Nov 18 at 13:01
Joel Brown
11.2k33652
answered Nov 18 at 13:01
Joel Brown
11.2k33652
11.2k33652
add a comment |
add a comment |
up vote
0
down vote
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 at 14:08
serge
54037
add a comment |
up vote
0
down vote
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 at 14:08
serge
54037
add a comment |
up vote
0
down vote
up vote
0
down vote
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 at 14:08
serge
54037
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 at 14:08
serge
54037
answered Nov 19 at 14:08
serge
54037
answered Nov 19 at 14:08
serge
54037
answered Nov 19 at 14:08
serge
54037
54037
add a comment |
add a comment |
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
