[mkdevo]

this looks long, but if you know your stuff, PLEASE read through.. i beg of you!

i'm beginning to build a Songs & Shows database for my website (a fan site for the band, 'the breakfast') with PHP & MySQL. i'm pretty new to PHP, though i'm familiar with a lot of the code from editing and building off of templates before. also pretty new to MySQL, though i do know SQL pretty well.

i'm kind of diving in head first here.. i know what i want, i'm just not sure of the best way to achieve it, so i'm typing it all out to a) get it all out on 'paper', and b) see if i can get some help and suggestions. while i am looking for help, i really want this to be a learning experience, which is the reason i don't want to have someone else just do it for me.

in simple terms, here's what i want when i'm done:

SONGS: i want detailed, searchable information (composer, lyrics, etc. - see full list below.) for all of the band's songs - both originals and covers. i also want to be able to display the first time and the last time the song was played live, which ties into my next portion...

SHOWS: full setlist and details of every concert the band has ever played, and searchable by date, venue, city, state, song, etc. see my comments below about what i want to do with this.

--

Table Structure: here's where i need help (right now).. i don't know exactly what fields should be a table of their own, or how to tie the tables in together when they do.. here's a list of the fields i need, and comments about what i want to do with them:

songs table:
- song id
- song title
- composer
- album (can be null)
- length (null if album is null)
- original?
- instrumental?
- # of times played live
- first time played live
- last time played live
- lyrics

the 'composer' can be anyone in the world, but it can also be multiple people (comp1/comp2). the same composer will appear on many different songs. should i have a composer table of its own?

i was also thinking 'album' should maybe be its own table - should it?

the first time played and last time played is information i want to grab from the setlists database. while the first time played will always be the same, the last time played will always be changing.

shows table:
- show id
- date
- event (optional)
- venue
- city
- state
- number of sets
- number of encores
- setlist (containg SONGS, in a given order - details below)
- notes (? - see details below)
- download link (optional)
- listen link (optional)
- picture link (optional)
- discussion link (optional)

as i was typing that, it dawned on me that it would probably be best to have a 'venue' table, containg venue, city, state, etc. - right?
here's where i really have no clue what to do..

there is a setlist entry for every show the band plays on a given date.

there can be more than one show on a given date (would like to specify with 03/17/05a / 03/17/05b).

each show contains at least one 'set' of any number of SONGS, in a specific order.

an encore does not necessarily happen.

SONGS in a show can end (denoted by ','), or they can segue into each other (denoted by ' > ') (see below).

SONGS in a show can contain NOTES (see below). each song in a show which has a note should be 'flagged' differently (specifically in the following fashion: *, **, ^, ^^. #, ##, %, %%, @, @@, $, $$, &, &&)

as noted in the 'SONGS' structure above, i want to be able to report on how many shows a song has appeared in, the first time it was played, and the last (most recent) time it was played.

a sample of what the output of a given setlist would look like is below:

03/12/05 - The Call; Providence, RI
I: Cut Me Some Slack, Gravity*, Vera Street, Fresh Cut, Episode 1 (Happy) > Space Oddity > Dig > The Vermont Song

II: Drum Solo > What The Funk, Rufus > Hard Luck Harry > May Fly Disarray** > jam > Sleeping Beauty > The Other One > Rufus, Language Of The Gods^, jam > May Fly Disarray^^ > Hard Luck Harry > Message In A Bottle

E: Wild Pack Of Asscracks

* with 'Billie Jean' (Michael Jackson) teases.
** inverted.
^ with 'I Dream Of Jeanie' teases.
^^ with 'Power Of Equality' (Red Hot Chili Peppers) teases.

my guess is that i need to tackle the SONGS table first, and worry about the SHOWS after..

i know there's a lot here, but if there's an experienced PHP/MySQL user out there who's willing to help me out, it would be GREATLY appreciated.. if it's easier over email - mkdevo1@msn.com. thanks!