ERD – Multiple FKs with only one of them being filled for each record

database-designerdschema

I'm designing a database schema for a music platform.

Some of the entities as follows:

track
album
band
artist

A "track" can have an "artist". It can also have a "band" which makes the artist FK null.

Same goes with "album", an "album" can have an "artist" but if the "album" is from a "band" then the artist FK will be null. That's because an "album" by a "band" can't also have a single "artist" as well.

The two(or three) FKs will never be filled together and only one of them must be filled for each record.

Is this a good design practice?

Thanks

Best Answer

Good design practice is to implement and enforce the rules of the problem domain. So if your rules lead to this design then that's what you've got. Having mutually exclusive values is not uncommon.

They can be implemented in different ways. One is as you describe. Another is to use table inheritance. With this you would have tables track, artist and band as you do currently but also band_track and artist_track. These will have only the foreign keys specific to themselves, defined as not null. The problem then becomes one of enforcing exclusion between band_track and artist_track. Personally, I don't think this approach actually improves our position much.

For my two cents I'd go with what you have. Declare CHECK constraints to prevent illegal combinations within a row (or use triggers if you absolutely must).

Related Solutions

Many to Many and Weak Entities

I think you can, using a "diamond" relationship diagram:

diagram

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;

Extensible Asset Database Schema

Option #3

NULLs are your friend. After you select the platform, study how NULLs work for the platform and embrace them. In this case they accurately reflect the absence of a check-in. In Oracle NULLS are not indexed, so you could create a function based index that swaps the NULL state giving you a very small index that contains only the entries not checked-in.

Option #1 would need a join anytime you want the Check-In date or status. Joins are your friend too, but in this case there isn't a benefit to separating this data unless there are times when a single checkout can produce multiple checkins. Option #2 requires repeating data or even more NULLS than option #3. Here are the three concepts fleshed out to tables columns and data.

#1
CheckOut
   Asset CheckInOut User DateTime
   1     1          1    3/10/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

CheckIn
   CheckInOut DateTime   
   1            3/11/2013

#2
CheckInOut
   Asset CheckInOut User DateTime   InOrOut
   1     1          1    3/10/2013  O
   2     2          1    3/10/2013  O
   1     1          1    3/11/2013  I   
   3     3          2    3/11/2013  O

#3
CheckOut
   Asset CheckInOut User OutDateTime InDateTime
   1     1          1    3/10/2013   3/11/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

SQL Fiddle's showing Option #2 and Option #3 with the additional requirements Joel Brown added and how various questions would have to be answered in SQL.

#2 - http://www.sqlfiddle.com/#!4/55e72/25

#3 - http://www.sqlfiddle.com/#!4/e2f27/6

Best Answer

Related Solutions

Many to Many and Weak Entities

Extensible Asset Database Schema

Related Question