Pupeno <[EMAIL PROTECTED]> wrote on 01/26/2005 07:00:34 AM: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello MySQLers, > I'm trying to define a structure for my database and I'm experience some
> problems, any comments would be appretiated. > This is for a (web) application to handle information about music, lyrics and > resources for playing songs. So, the data I'll be managing are: persons, > groups, songs, albums and some song-related data that is not important right > now. > Let's start with the idea that I have a table for each of those kind of data > I'm managing, my problem is when I try to relate songs to musicians (a > musician can be a person or a group). The relationship happens thru a third > table that can stablish if the person or group (the musician) is the author > of the lyrics, of the music, an interpreter or whatever is needed. In short, > for some relationships, persons and groups are the same sings, and for some > others, they are very different things. > I've found the following three possible solutions: > 1) Have three totally separated tables: persons, groups, songs. Have another > table that relates songs to persons or groups, with an enum field that tells > if it's relating to one table or the other. The bad thing about this is that > I'll have a lot of redundant information: because if I link to a band 1000, > I'll be specifing 1000 that it is a band, only one should be enough. > 2) Have the persons and the groups in one table: musicians, with a field that > indetifies if it's a person or a group. The good thing is that the > relationship from songs to to musicians is very simple and it's specified in > only one place if it's a band or a person. The bad part is that it's hard to > separate groups from persons, it's not as easy as if they were in two > separate tables. Soem fields are valid for one type of musician and some > fields for another, so, the forms (to submit, modify, show and delete) for > each type need extra care. > 3) Have master table, musicians, with only the common fields between groups > and persons and then have two tables, one for persons and one for groups. > This seems like a cleaner solution, but it requires two inserts for each > insert of data (wich I would put in a transaction, but I'm stuck with MySQL > 3.x) and it my have other problems. > > Now that I'm thinking about a fourth solution: Have two totally separate > tables for groups and persons (this is what I really like) and then, one > table to relate songs to persons, and another table to relate songs to > groups. The problem with that is that, sometimes, I need to get all the > musicians that are related to a song, including both, persons and groups, to > just list them, BUT, with some identification if it's a person or a group, so > in the listing I can do some exceptions. > > Any comment is very well appretiated. > Thank you. > - -- > Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com > Reading Science Fiction ? http://sfreaders.com.ar > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.6 (GNU/Linux) > > iD8DBQFB94ZlfW48a9PWGkURAnoXAJwKJ/8pUWfWFrSvAUG+lzOWIjB6HQCePc/m > bLKQuSNJE6ZsYrWEyPQAhw0= > =fdbJ > -----END PGP SIGNATURE----- > If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. I would define Group as "one or more people that perform a version of a song". Each member of a Group will be an Artist (person). A Performance is a unique combination of GROUP - SONG - RECORDING. Some Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. This will help to differentiate studio recordings from live recordings of the same song. Groups can form and reform over the course of several years but keep the same name so (especially if you are creating a database to deal with performance royalties) you should also keep up with group lineups by date ranges. You can do this either by creating a new Group record for each line up (each with the same name but for different date ranges) or you handle this on your Groupmembership table. Disjoint membership spans would get two or more records. For example "Joe" is part of "<insert band name here>" from 1988 to 1990 then leaves (for whatever reason) and eventually rejoins the band from 1995 until their breakup in 1996. That would be one Group record with two Groupmembership records that associates Joe to his band for two different date ranges. Artists are in general just people. Performers, producers, lyricists, arrangers, backup musicians, engineers, etc. This is your master table of Who's-who. There should be a table or tables that associates the construction of each Song to one or more Artists. You could create separate tables for lyricists, composers, and arrangers (the normalized approach) or one table and include a value for how that artist contributed to the song (sometimes faster to work with but takes up more room). I think you were on the right track but were just trying to merge too many objects into the same containers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
