-----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----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
