SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relational DB advice, record label site

    Hi there,

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there is such a thing as a 3-way relationship table

    you nailed it on the first try

    nice one!

    as for guests, i would have both guests and band members in a "person" table, and put their role (member/guest) as an attribute (non-key) column in the appropriate relationship table(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow cool, I'm getting the "idea" behind it then now just to pull it off haha. One followup. I have always wondered about this aspect. Obviously there will be a lot of work adding the data to these databases for this site especially if I were to go about this manually. Once I get the template together and the databases in place, the "right/easy" way to do this is to build myself a CMS to add the different information in? I imagine that way I will not be adding the same info (band id, etc.) to 4 different databases by hand and will just let the CMS do its job?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that's right, you would have "admin" pages on the web site that require a login by a user with admin rights, and then you'd use forms to collect the data to be inserted into the tables

    to insert a row in a relationship table, you'll need the key values of the parent tables, and these are most often provided by making a selection from dropdown lists, which were populated by simple queries of the parent tables when producing the form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whew...some site I picked for my first database project eh?! HAha... ok here goes.. <deep breath!> Thanks Rudy!

  6. #6
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I have a follow-up question I could use "good practice" advice on please. This DB design was quite a mammoth as my first go-round at this haha.

    As I was putting this DB structure together I came across a hurdle. A lot of the CDs this label releases come with 1-3 CDs, and Special/Limited Editions, and also on the individual album pages I sometimes note "CD notes" like facts about the album etc. Also some have video/multimedia tracks. Heck some even come with DVDs and 2 CDs in one special edition package! With that said:

    I have a table named "tracks". (I assumed I should make a completely separate table for tracks as opposed to lumping them in one field in the "albums" DB.).

    In the tracks DB I have:
    number (track number), title, albumid, cd (for cd number 1, 2, etc.), notes (to note the track as standard, limited edition, bonus track, dvd/video track, etc.)

    In the albums DB I have:
    id, title, bandid, year, catid (catalog #), notes (for album specific notes)

    Lookup table for: tracks > albums OR I will select from tracks table where albumid = x

    Would that look like the correct way to do this? With album notes I can have up to 1 - 5 different notes about the album which would mean I would be lumping that into one field as opposed to making a separate notes table. Or, should I have a separate notes table? I'm note sure how yet (if it is in the same field) I wll do that. Either with <p> tags built in or <li> tags?

    I will post my complete DB structure after feedback. i downloaded that cool program that lays it out graphically from the DB. Thanks!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not sure about anything you're asking but the last part

    i would not put the <p> or <li> into the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Basically asking if having album notes in one field within the "album" table is correct way to do that? (as opposed to having a separate notes table) .

    And also for the tracks tale if I properly have that thought out in terms of the different columns in that table. I'm thinking ahead for when I go to start the query statements and pull the info into the individual album pages which will break down and organize things like:

    Album, band, album cover, track listing (my biggest fret), producer, album notes, individual track notes (if any given track only appears on a limited edition release, etc.).

    I'm nervous if I go ahead and set up my tables as in the post above...when I actually go to start doing all the SELECT blah FROM blah WHERE.... am I going to get my desired results.

    So like you said above by not adding the <p> or <li> ... a sample album notes content could be something like:

    2nd Ayreon album remixed and re-recorded, incl. a Bonus DVD with 5.1 Mix

    DVD
    · 5.1 mix Actual Fantasy Revisited 2004
    · Actual Fantasy – 1996 version (original version)
    · Videoclip ‘The Stranger from Within’ in 5.1 and 2.0
    · Featurette: Recording documentary
    I'm not sure what I should do as the best way to organize that info in a database, unless I should have a separate "Notes" database for only info like this, as opposed to lumping it into the albums DB.
    Last edited by MetalAges; Jan 29, 2005 at 18:45.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •