SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Music artist table schema: first/middle/last name vs. band name?

    I want to create a mini artist bio database, but I'm wondering how best to construct the artist table schema.

    method #1: stick everything in one artist name column...
    Code:
    CREATE TABLE artist (
      id INT(11) NOT NULL AUTO_INCREMENT,
      band_name VARCHAR(50),
      PRIMARY KEY(id)
    )
    ...the above obviously doesn't take first/middle/last name into account, which just seems wrong.

    method #2: split up the fields...
    Code:
    CREATE TABLE artist_alternative (
      id INT(11) NOT NULL AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      middle_name VARCHAR(50),
      band_name VARCHAR(50),
      PRIMARY KEY(id)
    )
    ...the above would result in a lot of null values because a band_name (eg, Rolling Stones) would have no need for the first/last/middle name fields.

    How should this be properly handled?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i doubt you're ever going to want to access first/middle/last separately, so just make it one common column -- name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since artist to band is a one to many relationship, and in some cases a many to many relationship, you'd probably want to have a separate artist table without a band name, and a band table that contains the band name. Then you'd have a third table that relates artists to bands.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Gator99
    Since artist to band is a one to many relationship
    i know what you mean, but that isn't always true

    how do you record an artist that performs solo? do you create a fictitious band? what name do you give this band? the same as the artist name? and why would you link joe schmoe to a band called joe schmoe? it just doesn't seem right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i know what you mean, but that isn't always true
    Say the band is the "Rolling Stones", there are several artists, albiet well past their prime, in the band.

    Quote Originally Posted by r937
    how do you record an artist that performs solo?
    You'd put the band name as the artist name, for example "Celine Dion" would be under the band "Celine Dion". This would be the exception, and would be handled well by the set up I described.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    handled, yes -- handled well, i disagree
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL. I even used a Canadian in the example and you still don't get it. This is my last post on this thread. Undoubtedly, you will post again as you have before simply to get the last word in.


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
  •