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 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' ?
1 table for Songs, one for Artists (people). Then one table for each kind
of relationship a person can have to a song: Lyricist, Composer, Arranger,
etc.
Partial CREATE TABLE statements:
CREATE TABLE Song (
id INT auto_increment
, name varchar(50) not null
...
)
CREATE TABLE Artist (
id INT auto_increment
, name varchar(50) not null
...
)
CREATE TABLE Lyricist (
song_id int not null
, artist_id int not null
, primary key(song_id, artist_id)
)
The same situation exists between recordings and artists. You will need
tables for engineers, producers, background musicians, etc.
Each row in one of those "middle" tables represents the relationship
(link) between a person and another entity (song, recording, group, etc.).
Which "middle" table the row is in identifies the type of relationship.
>
> > 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) associated.
> 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).
Actually, albums represent a group of individual RECORDINGS (in my model).
Each recording on an album may be from a different GROUP but all the same
SONG. I have a CD of a 7" single that has 6 different dance remixes of
Depeche Mode singing Personal Jesus. Same song, same GROUP, different
engineers, different recording titles, different producers, remixed in
different studios, etc.
If you want to be accurate, then you must be able to differentiate between
different recordings of the same song, even if they are done by the same
GROUP. Another event happens when the exact same recording appears on more
than one album. For instance, the original studio album "A Day at the
Races" and "Queen's Greatest Hits". The exact same recording appears on
both albums. You only need to list the recording once and the album once
and have a table that maps recordings to albums.
More partial SQL:
CREATE TABLE Album (
id int auto_increment
, title varchar(50) not null
...
)
CREATE TABLE Recording (
id int auoto_increment
, song_id int not null
, group_id int not null
...
)
CREATE TABLE AlbumRecordings (
Album_id int not null
, Recording_id int not null
, Tracknum int not null
, primary key (Album_id, Recording_id, Tracknum)
)
>
> I'm still stuck with the problem that groups and artists are
interchangeable
> things, how would you solve this ?
In my mind, a Group is something that can make a recording. It is composed
of one or more Artists. Artists cannot make recordings, only groups can.
If someone does a solo project, create a Group named after that person and
assign that person to the Group as its only member. It may /seem/ to be
wasting space but it isn't as you are preserving your entity relationships
this way. Sometimes as a DBA we have to "ignore" certain connotations in
our language so that our data models work. In this case we have to
"ignore" the fact that we say "The Artist so-and-so recorded.." because in
our model, only GROUPS make recordings. It's an artificial convention
(distinction) but one that works for our model.
>
> Thank you for your comments, they were greatly appreciated.
My pleasure. That's what this list is for. 8-D
> - --
> 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-----
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
