Database (foreign key) issue

Question

So I need to make a registration package for managing songs/CD's. My problem is that I need to link an artist to a track, but it's possible that it is a band or just an artist but I dont know how I would need to do this.

For now is have this: https://i.sstatic.net/GZQLN.jpg

In a song there is a possibility to add a band or an artist or both. So bands exist of artists, thats why there is a table between them.

The problem that occurs now is that it is not possible to leave one of them blank because of the foreign keys, but I feel it is more than that and that im doing this the wrong way.

Someone that can give me a push in the right direction?

Answer 1

You can define n_artist and n_band as allowing NULLs. Foreign key constraints aren't checked when a column is NULL.

You can also, then, include a CHECK constraint. You can enforce that at least one of these columns isn't null. Or you can enforce that exactly one of the columns isn't null. Which one is correct depends on your domain. For Bands and Artists, I'd usually think of the second one being correct (i.e. I would define a track as being by a Band, or by an Artist. I'm not sure what interpretation should be imposed on a track associated with both)

CREATE TABLE T (
     /* Other Columns */
     BandID int null,
     ArtistID int null,
     /* Other constraints */
     constraint FK_Bands FOREIGN KEY (BandID) references Bands (ID),
     constraint FK_Artists FOREIGN KEY (ArtistID) references Artists (ID),
     constraint CK_SingleAssociation CHECK (
          (BandID is null and ArtistID is not null) or
          (BandID is not null and ArtistID is null)
     )
)

Answer 2

track
    id
    title

artist
    id
    name

band
    id
    name

artist_band
    artist_id
    band_id

artist_track
    artist_id
    track_id

band_track
    band_id
    track_id