Multiple nullable foreign key vs multiple resource tables
We are designing a system where we have two types of entities Company and Property. Both Property and Company have their own media resources (video, photo) so we are discussing two approaches of dealing with this on the database level.
First is to have one Media table that would have nullable foreign keys to both Company and Property
The second one is that for both Company and Property we would have CompanyMedia and PropertyMedia
Which one of these approaches makes more sense?
EDIT:
Should be killed for suggesting solution No2 :).
database database-design foreign-keys relational-database
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
add a comment |
We are designing a system where we have two types of entities Company and Property. Both Property and Company have their own media resources (video, photo) so we are discussing two approaches of dealing with this on the database level.
First is to have one Media table that would have nullable foreign keys to both Company and Property
The second one is that for both Company and Property we would have CompanyMedia and PropertyMedia
Which one of these approaches makes more sense?
EDIT:
Should be killed for suggesting solution No2 :).
database database-design foreign-keys relational-database
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28
add a comment |
We are designing a system where we have two types of entities Company and Property. Both Property and Company have their own media resources (video, photo) so we are discussing two approaches of dealing with this on the database level.
First is to have one Media table that would have nullable foreign keys to both Company and Property
The second one is that for both Company and Property we would have CompanyMedia and PropertyMedia
Which one of these approaches makes more sense?
EDIT:
Should be killed for suggesting solution No2 :).
database database-design foreign-keys relational-database
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
We are designing a system where we have two types of entities Company and Property. Both Property and Company have their own media resources (video, photo) so we are discussing two approaches of dealing with this on the database level.
First is to have one Media table that would have nullable foreign keys to both Company and Property
The second one is that for both Company and Property we would have CompanyMedia and PropertyMedia
Which one of these approaches makes more sense?
EDIT:
Should be killed for suggesting solution No2 :).
database database-design foreign-keys relational-database
database database-design foreign-keys relational-database
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
edited Nov 13 '18 at 20:29
kljuco
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
asked Nov 13 '18 at 18:41
kljucokljuco
2711415
2711415
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28
add a comment |
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28
add a comment |
1 Answer
1
active
oldest
votes
The second approach is a no-no IMHO. The media_url and media_type attributes must be unique in the database. Otherwise you risk duplication and synchronization problems.
Ex of problems with model no2:
- One media is linked to Company 1. Its type is "video".
- The same media (i.e. URL) is linked to Property 1. It's type is "blog".
- What happens now if you want a list of all media and their types? Which one would you take?
- And you have to query 2 tables which is inefficient.
I see 4 tables here. Company, Property, Media and MediaType. A media type should also have it's own table, to avoid duplication.
Hence:
Company
idCompany
CompanyName
Property
idProperty
PropertyName
Media
idMedia
MediaURL
idMediaType, FK to MediaType
MediaType
idMediaType
Type
And link tables:
Property_has_Media
idProperty
idMedia
Company_has_Media
idCompany
idMedia
Model:
This structure would be what I suggest if one media is never linked to both a Company and a Property. From your question this is what I understand. And conceptually, a media does not define a link between a Company and a Property, so having 2 separate link tables makes more sense. It will also avoid the "IS NOT NULL" all over your queries.
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
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%2f53287564%2fmultiple-nullable-foreign-key-vs-multiple-resource-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The second approach is a no-no IMHO. The media_url and media_type attributes must be unique in the database. Otherwise you risk duplication and synchronization problems.
Ex of problems with model no2:
- One media is linked to Company 1. Its type is "video".
- The same media (i.e. URL) is linked to Property 1. It's type is "blog".
- What happens now if you want a list of all media and their types? Which one would you take?
- And you have to query 2 tables which is inefficient.
I see 4 tables here. Company, Property, Media and MediaType. A media type should also have it's own table, to avoid duplication.
Hence:
Company
idCompany
CompanyName
Property
idProperty
PropertyName
Media
idMedia
MediaURL
idMediaType, FK to MediaType
MediaType
idMediaType
Type
And link tables:
Property_has_Media
idProperty
idMedia
Company_has_Media
idCompany
idMedia
Model:
This structure would be what I suggest if one media is never linked to both a Company and a Property. From your question this is what I understand. And conceptually, a media does not define a link between a Company and a Property, so having 2 separate link tables makes more sense. It will also avoid the "IS NOT NULL" all over your queries.
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
add a comment |
The second approach is a no-no IMHO. The media_url and media_type attributes must be unique in the database. Otherwise you risk duplication and synchronization problems.
Ex of problems with model no2:
- One media is linked to Company 1. Its type is "video".
- The same media (i.e. URL) is linked to Property 1. It's type is "blog".
- What happens now if you want a list of all media and their types? Which one would you take?
- And you have to query 2 tables which is inefficient.
I see 4 tables here. Company, Property, Media and MediaType. A media type should also have it's own table, to avoid duplication.
Hence:
Company
idCompany
CompanyName
Property
idProperty
PropertyName
Media
idMedia
MediaURL
idMediaType, FK to MediaType
MediaType
idMediaType
Type
And link tables:
Property_has_Media
idProperty
idMedia
Company_has_Media
idCompany
idMedia
Model:
This structure would be what I suggest if one media is never linked to both a Company and a Property. From your question this is what I understand. And conceptually, a media does not define a link between a Company and a Property, so having 2 separate link tables makes more sense. It will also avoid the "IS NOT NULL" all over your queries.
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
add a comment |
The second approach is a no-no IMHO. The media_url and media_type attributes must be unique in the database. Otherwise you risk duplication and synchronization problems.
Ex of problems with model no2:
- One media is linked to Company 1. Its type is "video".
- The same media (i.e. URL) is linked to Property 1. It's type is "blog".
- What happens now if you want a list of all media and their types? Which one would you take?
- And you have to query 2 tables which is inefficient.
I see 4 tables here. Company, Property, Media and MediaType. A media type should also have it's own table, to avoid duplication.
Hence:
Company
idCompany
CompanyName
Property
idProperty
PropertyName
Media
idMedia
MediaURL
idMediaType, FK to MediaType
MediaType
idMediaType
Type
And link tables:
Property_has_Media
idProperty
idMedia
Company_has_Media
idCompany
idMedia
Model:
This structure would be what I suggest if one media is never linked to both a Company and a Property. From your question this is what I understand. And conceptually, a media does not define a link between a Company and a Property, so having 2 separate link tables makes more sense. It will also avoid the "IS NOT NULL" all over your queries.
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
The second approach is a no-no IMHO. The media_url and media_type attributes must be unique in the database. Otherwise you risk duplication and synchronization problems.
Ex of problems with model no2:
- One media is linked to Company 1. Its type is "video".
- The same media (i.e. URL) is linked to Property 1. It's type is "blog".
- What happens now if you want a list of all media and their types? Which one would you take?
- And you have to query 2 tables which is inefficient.
I see 4 tables here. Company, Property, Media and MediaType. A media type should also have it's own table, to avoid duplication.
Hence:
Company
idCompany
CompanyName
Property
idProperty
PropertyName
Media
idMedia
MediaURL
idMediaType, FK to MediaType
MediaType
idMediaType
Type
And link tables:
Property_has_Media
idProperty
idMedia
Company_has_Media
idCompany
idMedia
Model:
This structure would be what I suggest if one media is never linked to both a Company and a Property. From your question this is what I understand. And conceptually, a media does not define a link between a Company and a Property, so having 2 separate link tables makes more sense. It will also avoid the "IS NOT NULL" all over your queries.
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
edited Nov 15 '18 at 0:29
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
answered Nov 13 '18 at 20:08
Nic3500Nic3500
3,33281829
3,33281829
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
add a comment |
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
WOW. I cannot believe I've suggested solution no2 as a possible answer. Basically, I hade your proposed solution in my head (just without MediaType as a separate table). So I will go with option No2 from my head with your modification :). Property and Company should never have the same media (even though they are related, company owns many properties)
– kljuco
Nov 13 '18 at 20:25
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
:-) a second set of eyes is what SO provides!
– Nic3500
Nov 13 '18 at 20:29
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
@kljuco Your #1 is a common anti-pattern for implementing inheritance/subtyping. Also there are no normalization issues with your #2. (You might not like taking unions, which are needed for the supertype queries mentioned in the answer (despite it's rhetorical questions implying there is a problem), but there are variations that avoid that.) See the duplicate link & many others like it re inheritance/subtyping/polymorphism for options.
– philipxy
Nov 13 '18 at 23:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
The way he setup his #2, don't you have to scan both media tables when adding a new one to avoid duplication? Unless you already know that it will be a Company or a Property media to start with of course. Isn't the structure I proposed more general in that context?
– Nic3500
Nov 15 '18 at 0:28
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%2f53287564%2fmultiple-nullable-foreign-key-vs-multiple-resource-tables%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
Possible duplicate of How can you represent inheritance in a database?
– philipxy
Nov 13 '18 at 23:23
This queston may have an answer here.
– Walter Mitty
Nov 14 '18 at 0:28