Relational DB advice, record label site
I am putting together a site for a record label I work with and have a question I would like advice on.
Scenario: Label has many bands, members of the bands happen to appear in multiple groups on the label. Sometimes a member may play guitar on one band's album, whereas he may sing lead/guitar on another band album.
So, my question is: I currently have:
- band table with "id, name, bio, url, active"
I am assuming I am going to add:
- members table with "id, member"
- albums table with "id, album, band"
- instruments table with "guitar, lead vocals, back vocals, bass, drums, etc." (Would this be needed or am I think too much about how to go about this?)
- guests table with "id, guest" (I am assuming to keep guests separate from members since they usually fall into a separate sub area if I were to list album players, as you will see on my example URL below.)
Ok here's where I am getting a little lost as what to do next (properly, following good DB design). I need to:
- associate members with albums (lookup table for members/albums I assume?)
- associate members with instruments on an album by album basis. (is there such thing as a lookup table with 3 columns for say: memberid, instrumentid, albumid?) Not sure how to properly go about this one.
- I'll have to associate guests in the same manner.
That's all right now. Here is the page I am working on in setting up my initial template/database: http://www.insideoutmusic.com/band.php?id=51
Just with this one band alone, as you can see the guys play many instruments. On top of that, Nick and Ryo are examples of guys who have their own bands on this label as well and who play different things on those albums. Heck they even play different instruments within the band Spock's Beard on other Spock's albums. Pretty crazy!
This is my firstreal SERIOUS databased site so while I am doing all this I want to learn how to properly do it and not cut corners like I easily could. I have the MySQL/PHP book from Sitepoint I have been learning by and it's helping a lot. Any guidance here appreciated.