Multiple nullable foreign key vs multiple resource tables

Question

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 :).

Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Nov 13 '18 at 23:23
This queston may have an answer [here](https://dba.stackexchange.com/questions/193394/is-it-a-bad-practice-to-have-several-mutually-exclusive-one-to-one-relationships/193401#193401). — Walter Mitty, Nov 14 '18 at 00:28

Nic3500 · Accepted Answer · 2018-11-15T00:29:44.647

1

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.

edited Nov 15 '18 at 00:29

answered Nov 13 '18 at 20:08

Nic3500

8,144
10
29
40

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) – emirkljucanin 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 00:28

Multiple nullable foreign key vs multiple resource tables

1 Answers1