Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.

Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums.

So we have a many-to-many relationship between the album and the tracks.

If the album is a weak entity, then its primary key is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing a many-to-many relationship.

The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?

share|improve this question
    
No, the album primary key would just be an integer that makes the album unique. You may then have a artist_id foreign key that references the artist. If you want a single track to be mapped to multiple albums then use a mapping table with track_id, album_id. Easy :) –  Phil Feb 4 '13 at 2:59
    

1 Answer 1

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
) ;
share|improve this answer
1  
+1 Would it make sense to you to add the following unique constraints to AlbumTrack table: (trackID, albumID) and (albumID, trackNo)? –  A-K Mar 6 '13 at 18:19
    
@AlexKuznetsov You are right, thnx. I'll "shrink" the PK to your suggested (albumID, trackNo) and add the other Unique constraint, too. –  ypercube Jul 4 '13 at 20:56
    
Remember to allow for albums that have no single nominal artist, either by having a dummy artist called "Various" or similar or by making the artist column of the album table nullable. In fact, you could have more than one artist per track, so you might need a many-to-many arrangement there too. –  David Spillett Sep 25 at 13:01
    
@DavidSpillett Yes, we could do that but it would complicate things and deviate form the question asked. The question assumes/dictates that every album has a single artist. Not possible to have different artists per track, neither many artists per album or track. It's not a very good representation of the real world, indeed. –  ypercube Sep 25 at 13:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.