10-31-09, 09:35 #1Registered User
- Join Date
- Oct 2009
How to map artist or band from diff. tables to a CD table?
I just started learning PHP and MySQL (yeah, total noob here...) and I am trying to work out a database design for a basic music database. I learned about normalization, so I decided to draw some tables that break up my data into the most basic parts and visualize the relationships. Please see the first image attachment.
What would be the proper way to model the relationship between the performer of a CD and the band or artist, as a CD could be performed by either a band or an individual artist?
Is my chart "legal"? I don't even know if I can combine two fields like that. I read something about joining fields in MySQL, but so far I'm not sure I'm heading in the right direction.
If it's any help, here is some additional information.
Some results I will want from the database would be:
- List CD's by a particular band
- List CD's by a particular artist
Listing a CD by an artist should also include bands, because I want to display both solo and collaboration albums on the artist's profile. In other words,
- Band's profile displays only the band's albums
- Artist's profile displays both the artist's and related bands' albums.
My solution to that is to create a separate table which contains band membership info with dates (joined/left the band). This way I can hopefully output album data relevant to a particular artist, because a band could exist before or after a given artist participated. My second attachment is this extra table.
Maybe I could incorporate this extra table to solve my original problem, I'm not sure. I apologize if this is a frequently asked question. I browsed forum and saw similar questions, but couldn't quite make the connection between the answers and my own problem. Thanks
10-31-09, 14:33 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
I think you are on the right track
A recording(track) may appear on more that one media (more than one CD, CD+MP3+LP+whatever)
an artist may perform solos, as part of a group.band or both
an artist may use a psuedonym when recording (eg Travelling Wilbury's and many guestr artists), and they may be doing both at the same time.
so you need to identify
compilations of tracks/songs/compositionsm
you need to know who composed what tracks/songs/compositions (so thats an intersection table identifying the composer and the composition)
you need to know who recorded a specific track so thats an intersection table identfying the performers and the track. (bear in mind there may well be more than one version of the same track (in some cases by the same performers, and in some cases by different performers) you need to decide if you need to know who composed the track, and then separately what are all the variants of that track. so you coudl record other stuff in here such as performancre time, the date of the recording and so on.
you need to allocate spefic performances of that composition to say an album/CD/MP3 package, so you would have an intersection table which associated a specific perfomrnace with a specific album, bearing in mind that the same track may appear on different formats (it mguth appear on the original album, the bands greatest hits, an annual/year best of compilation, a them compilation and so on. and the same composition may appear on different album/CD's eg you may have the 'standard' version and say an acoustic version.I'd rather be riding on the Tiger 800 or the Norton
11-02-09, 12:31 #3Registered User
- Join Date
- May 2008
Not a bad start.
I would recommend looking into a technique called Subtyping. I would subtype your Artist table into Band and Soloist, and then relate CD to Artist. Keep your Band Membership table, but use it to relate a Soloist to a Band.
As healdem mentioned, you will want to identify songs and/or tracks, not just CDs (i.e., Albums), as songs may be released on multiple Albums (greatest hits, etc). Also consider that Albums may be compilations of songs from multiple Artists (soundtracks, thematic albums, etc).
If you want to get really complex, you may want to consider whether or not a song may be performed by multiple artists.