Re: A problem of structure
José Pablo Ezequiel Fernández [EMAIL PROTECTED] wrote on 01/26/2005 03:49:50 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 After the good comments I've got on this mailing list, I think I have the structure more or less complete. In some cases I follow the comments, in some others, I've improvised (hehehe). So, this is the (explained) structure, what do you think ? Anything I can improve ? monster snip So, in general, what do you think ? Thank you! - -- Pupeno: [EMAIL PROTECTED] - http://pupeno.com Reading Science Fiction ? http://sfreaders.com.ar [1] I'm making that table public in my web page here: http://pupeno.com/misc/languagesDB -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB+AJxfW48a9PWGkURAvC8AJ9YeNxHCt+ZgfJrl4nvcbYxCJy+lwCfX4Rk HxtIQOtUBlI2lQZmMMakoPw= =IZH6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I think you have an excellent prototype database and can begin your initial site development against it. You may find that you will need an additional column or two but I think it should be good enough to begin working with. Incremental refinements are just part of the fun! Who knows? you may hit an idea that completely changes this design and makes everything else work much faster! Don't let it discourage you. Some of my best database improvement ideas have come from scrapping 80% of one design and reworking it another way. One thing that I know you will be adding soon will be indexes. Pattern your indexes after your most common queries and everything should hum right along. Remember, MySQL generally uses only one index per query (see the EXPLAIN documentation for more details. I also refer you to the manual sections on optimizing). A few, well chosen, multi-column indexes will generally give you better average performance than many single-column indexes. The manual has excellent advice on this topic. Come back to the list if you have any major problems, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
A problem of structure
-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]
Re: A problem of structure
Pupeno wrote: 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. - how about... - 6 tables: PERSON * person_id * name GROUP * group_id * name PERSON-TO-GROUP LINK TABLE * person_id * group_id ALBUM * album_id * title SONG * song_id * title * album_id SONG-TO-PERSON-OR-GROUP LINK TABLE * song_id * person_id * group_id * role (performer, lyricist, etc.) - left joins across song-to-person-or-group and person and group will return whichever is the case. - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
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
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Je Merkredo Januaro 26 2005 15:34, Ian Sales (DBA) skribis:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* person_id
* group_id
* role (performer, lyricist, etc.)
What I don't like about that, is that half the person_id fields would be empty
and half the group_id fields would be empty. I was thinking about:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* musician_id
* type ('person', 'group')
* role (performer, lyricist, etc.)
But this is the solution that has so many redundant data. It's not very clear,
but it's there.
- --
Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com
Reading Science Fiction ? http://sfreaders.com.ar
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
iD8DBQFB95G5fW48a9PWGkURAkhJAJ41HNEEI9v1ccLIAIuAajxA/oL59wCffdLE
MNgYp2L6UrjgQcB3WWfCI10=
=yDWO
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno wrote:
What I don't like about that, is that half the person_id fields would
be empty
and half the group_id fields would be empty. I was thinking about:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* musician_id
* type ('person', 'group')
* role (performer, lyricist, etc.)
But this is the solution that has so many redundant data. It's not very clear,
but it's there.
- and conversely, I don't like the idea of column that can join to
either of two tables depending on the value of a switch (the type
column) :-)
- perhaps using separate columns in the link for each role might work...
performer_group_id, performer_person_id, lyricist_person_id, etc. There
will be one row per recording of a song, although some columns on that
row may be empty.
- ian
--
+---+
| Ian Sales Database Administrator |
| |
| If your DBA is busy all the time... |
| ...he's not doing his job properly |
| eBuyer http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Shawn Green, Je Merkredo Januaro 26 2005 15:42, [EMAIL PROTECTED] skribis: If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. Well, the thing is that I was just doing a site for holding information for musicians (that is, lyrics with chords to play in the guitar or piano), but the thing started to grow as I am a very structured person I wanted to have a very nice structure. What you say makes sense, but the thing starts to be even bigger, I'm not sure that anyone would be able to introduce data if it's so complicated. Anyway, I'm already tempted to introduce your ideas and I think I can't be untempted. The thing is that this is art, and art is hard to structure. What is a song ? ok, Freddie Mercury composed a song, he wrote the music, he wrote the lyrics and he recorded with a band called Queen. Latter, someone else comes and record it, but changes the lyrics, is it still the same song ? What if the music is changed and the lyrics stay ? what if everything changes, but the title/name remains ? As I can't ensure when it is still the same song, I would take the asumption that all of them are different songs. But then, my asumption might be wrong. For example, the last song of the album Queen (the first album by the band Queen) has the same name as the last song of the album Queen II, the music is similar, slightly changed, and the second version has lyrics while the first one doesn't. Is it the same song or two separate songs ? the same songs and two different performances ? 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). Ok. A Performance is a unique combination of GROUP - SONG - RECORDING. Some A performance can be a unique combination of artist (person) - song - recording as well! Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. Indeed. This will help to differentiate studio recordings from live recordings of the same song. Agreed. 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. This was in my mind... I was trying to convince myself that the system was good enough without this information, but again, I'm tempted to implement your solution. 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). Takes more room because of the extra field ? Can you tell me more about 'the normalized approach' ? I think you were on the right track but were just trying to merge too many objects into the same containers. Then I have albums, which are collections of songs, with a track number and have one or more musicians (which can be persons or groups) asociated. For the album It's a kind of magic, the main artists would be Queen, while a lot of other people participated in the album, even as musicians (that is, playing an instrument or doing something). I'm still stuck with the problem that groups and artists are interchangable things, how would you solve this ? Thank you for your comments, they were greatly appretiated. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB96F3fW48a9PWGkURAnZKAJ9y9+H/vhB+0lLPEQxw2LMDyWfNiwCfVaRz SzvKTgyHZ3YBovGfT3+GuPk= =Moax -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
On Wed, Jan 26, 2005 at 01:56:05PM +, Pupeno wrote: Takes more room because of the extra field ? Can you tell me more about 'the normalized approach' ? http://en.wikipedia.org/wiki/Database_normalization -Jason Martin -- If you cannot convince them, confuse them. This message is PGP/MIME signed. pgpGY7lV887VI.pgp Description: PGP signature
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 08:56:05 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Shawn Green, Je Merkredo Januaro 26 2005 15:42, [EMAIL PROTECTED] skribis: If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. Well, the thing is that I was just doing a site for holding information for musicians (that is, lyrics with chords to play in the guitar or piano), but the thing started to grow as I am a very structured person I wanted to have a very nice structure. What you say makes sense, but the thing starts to be even bigger, I'm not sure that anyone would be able to introduce data if it's so complicated. Anyway, I'm already tempted to introduce your ideas and I think I can't be untempted. The thing is that this is art, and art is hard to structure. What isa song ? ok, Freddie Mercury composed a song, he wrote the music, he wrote the lyrics and he recorded with a band called Queen. Latter, someone else comes and record it, but changes the lyrics, is it still the same song ? What if the music is changed and the lyrics stay ? what if everything changes, but the title/name remains ? As I can't ensure when it is still the same song, I would take the asumption that all of them are different songs. But then, my asumption might be wrong. For example, the last song of the album Queen (the first album by the band Queen) has the same name as the last song of the album Queen II, themusic is similar, slightly changed, and the second version has lyrics while the first one doesn't. Is it the same song or two separate songs ? the same songs and two different performances ? Same composer + same title + roughly same melody = same song, different arrangement. This arrangement had words and a slightly different structure but I count them as same song 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). Ok. In answer to your question below. I believe you are confusing a Group consisting of a single person with the entity we are calling Artist. A Group can represent one or more people working together under a common name. We should relate the artists to the groups they are in through a separate table. The relationship is an entity itself and can have other information (like dates of join/leave, band position, etc.) Take a look at this sample data Group table -- Queen Wham George Michael Ziggy Stardust David Bowie Van Halen David Lee Roth Artists table Brian May Freddy Mercury George Michael David Bowie David Lee Roth Sammy Hagar Eddie Van Halen Steve Vai Groupmembership (Group - Artist - primary role) - Queen - Brian May - Lead Guitar Queen - Freddy Mercury - Lead Singer Wham - George Michael - Lead Singer Wham - Andrew Ridgely - Lead Singer George Michael - George Michael - Lead Singer Ziggy Stardust - David Bowie - Lead Singer David Bowie - David Bowie - Lead Singer Van Halen - Eddie Van Halen - Lead Guitar Van Halen - Sammy Hagar - Lead Singer Van Halen - David Lee Roth - Lead Singer David Lee Roth - David Lee Roth - Lead Singer David Lee Roth - Steve Vai - Guitar If you wanted to allow for multiple roles for a Groupmember then we would need two more tables to make that association. You could create a table with nothing in it but roles (lead singer, steel guitar, fiddle, rhythm guitar, saxophonist, keyboard, drummer, etc.) and a table to match Grouproles to Groupmembers. Each row in that matching table would be a combination of what that person did while a member of that group. If someone did 4 things, they would have 4 records in that matching table. Make sense? A Performance is a unique combination of GROUP - SONG - RECORDING. Some A performance can be a unique combination of artist (person) - song - recording as well! Not exactly, this is where I think you are still confusing the entity GROUP with the entity ARTIST. A group may be composed of only one person (person = artist) but the group is what makes a PERFORMANCE. An artist is part of the performance only by virtue of being a member of the GROUP that created it. For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. Indeed. This will help to differentiate studio recordings from live recordings of the same song. Agreed. Groups can form and reform over the course of several years but keep the same name so (especially if you are
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There where two cases where I have roles: How an artist participated in a song (creating the lyrics, creating the musici, etc, etc) and how an artist participates in a group. Do you think that enums are good for this kind of things ? or another table ? If in another table, how would you manage translations (that is, the system is multilingual), having the translations in that table, or another table ? or what ? 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) iD8DBQFB98n+fW48a9PWGkURAjlAAJsEMwnQDJOqcLMFrSdm6FrLJym6wACdHQxp XRfhhyEP1ccRuPEqh5f6rqM= =CzC2 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Je Merkredo Januaro 26 2005 18:56, [EMAIL PROTECTED] skribis: For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) If for example, someone played the drums for Eric Clapton on that performance, would he have 'joined' the 'Eric Clapton' group during that performance ? Or for example, when Paul McCartney goes doing a tour as Paul McCartney, do the musicians who go with them join the group Paul McCartney ? Thanks. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar PS: I'm not sure how to handle the interface for this, I mean, people won't really understand that Eric Clapton IS a group if I list him under Groups. Any recomendations ? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB98r0fW48a9PWGkURAn42AJ0XdACT/PYjbseVg7q9ftLgOB9JnACggtHK +3x3oiWxKpi6RLpOA6QjIis= =LRI1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 11:48:59 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There where two cases where I have roles: How an artist participated in a song (creating the lyrics, creating the musici, etc, etc) and how an artist participates in a group. Do you think that enums are good for this kind of things ? or another table ? I prefer 1 table per association. If I need to associate people (Artists table) to Recordings (song+group+...) as Engineers, I would create a table called Engineer to store that relationship. If in another table, how would you manage translations (that is, the system is multilingual), having the translations in that table, or another table ? or what ? Proper names (groups, artists, albums, etc.) do not need to be translated. Jimi Hendrix is Jimi Hendrix in Spanish, Thai, German, or Swahili. It's the other names (roles, relationships) that shift from language to language. It's rather simple to have a master table in one language and translation. Really, the topic of internationalization is something you need to research on your own as it really doesn't apply well to this list. 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) iD8DBQFB98n+fW48a9PWGkURAjlAAJsEMwnQDJOqcLMFrSdm6FrLJym6wACdHQxp XRfhhyEP1ccRuPEqh5f6rqM= =CzC2 -END PGP SIGNATURE-
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 11:53:06 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Je Merkredo Januaro 26 2005 18:56, [EMAIL PROTECTED] skribis: For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) If for example, someone played the drums for Eric Clapton on that performance, would he have 'joined' the 'Eric Clapton' group during that performance ? Or for example, when Paul McCartney goes doing a tour as Paul McCartney, do the musicians who go with them join the group Paul McCartney ? Thanks. I guess that all depends on the level of detail you want to keep. Legal membership (with contracts and all that) is one condition but acting as a studio or backup player is another. If you want to make that distinction then you would assign backing artists to the Recording and not make them members of the group. The group 'M' is actually only 1 person, Robin Scott. However, on his single 'Pop Muzik' he had two female backup singers. Legally, they weren't part of the group (because he was the only official member) but they do appear on the recording. What you need to decide, now and not later, is if you are even interested in this level of detail. If you are, I would associate those ladies with the Recording not the Group. Otherwise, just leave that information out of your database. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar PS: I'm not sure how to handle the interface for this, I mean, people won't really understand that Eric Clapton IS a group if I list him under Groups. Any recommendations ? I think they will so long as they understand that in the context of their search that the name Groups means who recorded the music. Off the top of my head Eric has recorded as himself, as a member of the group Cream, as a member of Derrick and the Dominoes, and as a supporting musician for a lot of other artists. If you are searching by group names, you only turn up Eric Clapton. If you are searching for recordings that have Eric on them, you will find them all. How you design your search page will make all the difference in what the user thinks they are looking for (perceptions are 90% of the battle). How you organize your database will make all the difference in how well your application responds. There is one VERY important thing to remember. What you call the entities in your database can be entirely different than what an end user thinks they are. Whatever label you put on your web site or in your application's GUI *DOES NOT* need to correspond to the actual name of the data you are presenting. If your users understand what you mean, you can call it anything you like on the back end so long as your back end name makes sense to you. In our case, we need to name a table to store data about an entity that creates a recording. A few possible names for that table would be Group, Band, Artist, or Orchestra. We also need to name a table to contain the names and other biographical information of the actual people that participate in the recording industry. Possible names are Person, People, Artists, Humans, Gente, Hombres, Mujeres, etc. However there are problems with most of those names. They are just not generic enough to fit our needs. If we create one table for just men and another for just women, then you would need to reference and coordinate between two different sets of IDs when looking up anyone's name (not fun). The name Artists appear in both lists of potential names. That means that we need to decide which entity we call Artist (if we even use that name) and which one gets an alternate name. How about we not use the table name of Artist and instead call it Person? That way you can't be confused between the name of the thing (BAND/GROUP/ORCHESTRA) that works together on a recording and the people that make up that thing. It's all a matter of _definition_. We _DEFINE_ that GROUP (or whatever) will the name of the table of those things that make recordings and People is the name of the table of those things that make up a GROUP. People can also be producers, engineers, etc. so that name fits rather well, I think. The basic rules of normalization says that you should only store 1 copy of any entity in your database. That means that we shouldn't have a list of people that were engineers and a separate list of people that were part of groups and another list of people that did arrangements. We need one list of people and whenever one of those people did something (they were a member of a group, the arranged a song, etc.) we create an entry in another table that shows that
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
After the good comments I've got on this mailing list, I think I have the
structure more or less complete. In some cases I follow the comments, in some
others, I've improvised (hehehe).
So, this is the (explained) structure, what do you think ? Anything I can
improve ?
Everything starts with persons, actual human beings (this is a very simple
table):
CREATE TABLE `persons` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`birthdate` date default NULL,
`deathdate` date default NULL,
`bio`,
PRIMARY KEY (`id`)
)
Then we have the groups of (one or more) people:
CREATE TABLE `groups` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
)
Since the groups or made of people, there's a table which says who belonged to
what group and for what period of time:
CREATE TABLE `memberships` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`person` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`from` date default NULL,
`to` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `artistGroupFromTo` (`person`,`group`,`from`,`to`)
)
As some extra information, it can be specified what did this persons do in
that group (for that period of time) in the following table, since what a
person can do in a group is likely to change and grow (specially grow, I can
even say that in the future we'll invent more instruments so more roles will
be added as people perform those instruments in a group):
CREATE TABLE `membershipRoles` (
`membership` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `membership` (`membership`,`role`)
)
Now, another point of entry to the system. The songs... this table defines the
abstract concept of song (language is a three letter code of the language of
the song, to be matched agains another table[1]):
CREATE TABLE `songs` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`language` char(3) NOT NULL default 'eng',
`lyrics` longtext,
PRIMARY KEY (`id`)
)
I was tempted to add a field 'translationOf' to easily hold translations of
songs. What do you think about that ?
Now, a song can be performed, so, I have the following table for performances
(either live or studio):
CREATE TABLE `performances` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`song` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
And then who (which group) did what (which role) in a song, the role is the
same as for `membershipRoles`:
CREATE TABLE `performancesGroups` (
`performance` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0'
UNIQUE KEY `performanceGroupRole` (`performance`,`group`,`role`)
)
The third end of this whole thing, are albums:
CREATE TABLE `albums` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`cover` longblob,
`coverFormat` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
Now, the albums can have a lot of related information, like, who made them,
that is the main group, like Queen, The Beatles, Eric Clapton:
CREATE TABLE `albumsGroups` (
`album` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`group`)
)
The, to specify who did what in that album (for example, Freedie Mercury:
Vocals, John Lenon: Guitars, Whoever Knowshim: Producer, etc):
CREATE TABLE `albumsPersons` (
`album` bigint(20) unsigned NOT NULL default '0',
`person` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`person`,`role`)
)
Now, each album contains a set of performances (not songs), in a specific
order:
CREATE TABLE `albumsTracks` (
`album` bigint(20) unsigned NOT NULL default '0',
`performance` bigint(20) unsigned NOT NULL default '0',
`track` tinyint(3) unsigned NOT NULL default '0',
UNIQUE KEY `albumPerformanceTrack` (`album`,`performance`,`track`)
)
And at last, the table of roles (the scope fields specifies what can be done
for each kind of data, it's more of a helper than anything else, a helper for
the GUI):
CREATE TABLE `roles` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`scope` set('album','performance','song') NOT NULL default
'album,performance',
PRIMARY KEY (`id`)
)
which for example, can contain the following data:
(1, 'Music composer', 'song');
(2, 'Lyrics composer', 'song');
(3, 'Vocals', 'album,performance');
(4, 'Guitars', 'album,performance');
(5, 'Bass Guitar', 'album,performance');
(6, 'Percussion', 'album,performance');
(7, 'Piano', 'album,performance');
So, in
