Little Normalization Question

MystedVeil

I am currently working on a database structure and site to store Music Information for registered members, so far i have the information working as I have it. I am just debating a few areas before furthering any work on this site.

I have 3 tables that deal with Artist, Albums and Tracks.

table: artist
artist_id (Primary INT)
artist_name (Text)
etc...

table: discography
disc_id (Primary INT)
disc_name (Text)
etc..

table:track
track_id (Primary INT)
track_name
etc...

The reason for this is multplicity, for example, is when the same song is performed on multiple albums or by multiple artist.

Should I break it down into one relational database? Thinking this would be the simplest but I also want it to function with simplicity.

ID ----------- Artist ----------- Album ----------- Track
1 ----------- Blue Oyster Cult ----------- Secret Treaties ----------- Astronomy
2 ----------- Metallica ----------- S&M ----------- Astronomy
3 ----------- Breaking Benjamin ----------- We Are Not Alone ----------- So Cold
4 ----------- Breaking Benjamin ----------- So Cold [EP] ----------- So Cold

table: relation1
rel_id (Primary INT)
artist_id
disc_id
track_id

OR should I make three?

table: relation1
rel1_id (Primary INT)
artist_id
album_id

table: relation2
rel2_id (Primary INT)
album_id
song_id

table: relation3
rel3_id (Primary INT)
song_id
artist_id

Shrike

I think you need the first 3 tables mentioned, plus a 4th since theres a many to many relationship between song and album. The 4th table would contain song_id and disc_id. From there you can build a result set containing the data needed.

I don't think there is a many to many relationship between artist and track. If two artists perform a song they are really different songs, at least from a logical point of view.

The last relationship is artist to disc. In most cases this is one artist to many albums, so you could just add an artist_id to the discography table. You might say that there is a many to many relationship when it comes to compilation albums, but the question is do you need to specify that level of detail?