SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the best design for the Artists and Group names section of a music database

    What is the best way to design this? Basically, I want to avoid duplicates - but my question is, is it better to use more tables or have a small number of duplicate entries? Consider the following (sorry I can't space it out or use tabs so I've separated the fields with a slash):

    FirstName / LastName / Article / GroupName

    Paul / McCartney
    Paul / McCartney / and / Wings
    / / / Wings
    Mike / McCartney /
    Paul / Simon
    Diana / Ross
    Diana / Ross / and the / Supremes
    / / / The / Supremes

    I have separated the Articles for sake of sorting and so I don't have a silly printout of "Supremes (The)".

    So, is it better to have one table for each of FirstName LastName and Article GroupName (together) - in order to maintain the integrity of a single "Paul" entry? That way I could use "unique" in the database - but it seems like an excessive amount of tables. Or should I make one database with the above layout and handle duplicates with an "if" "where" there is a match for more than 1 field (ie if Paul McCartney is entered that's a duplicate - but you can still enter Paul Simon.)

    I would like the form to display the above 4 fields.

    Charles
    Last edited by charles_i; Jan 13, 2010 at 09:17. Reason: spacing doesn't work in table

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your slashes are really confusing

    or maybe it's the "article" column that's confusing me

    how would you classify Stevie Ray Vaughn?

    i would use only one table, and only one column for the name

    don't bother splitting into firstname,lastname, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    Quote Originally Posted by r937 View Post
    how would you classify Stevie Ray Vaughn?
    AWESOME choice of example

  4. #4
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, about the slashes but this input tool won't let me do this:

    http://whatwant.ca/music.gif

    I would put Stevie Ray under first name because the whole point of this is to have a search/sort by artist name and/or group - so you would never want to sort by middle name.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by charles_i View Post
    this input tool won't let me do this
    sure it will
    Code:
    FirstName   LastName    Article   GroupName
    Paul        McCartney 
    Paul        McCartney   and       Wings
                                      Wings
    Mike        McCartney 
    Paul        Simon
    Diana       Ross
    Diana       Ross        and the   Supremes
                            The       Supremes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    Oops! Sorry, I see it - if you hit "Advanced" - thanks.

    Code:
    
    FirstName	LastName	Article    GroupName
    Paul 		McCartney 
    Paul 		McCartney 	and 	  Wings
    					  Wings
    Mike		McCartney
    Paul		Simon
    Diana		Ross
    Diana		Ross		and The	  Supremes
    				The	  Supremes
    Stevie Ray	Vaughan

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i used bbcode code tags
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but there's no "tab"??? - obviously you did it inside "Code:"

    ...and back to my question, using one table and one column won't allow proper sorting and you'll get that stupid "The" list with a zillion groups starting with "The" who are then in no order at all.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, no tabs, just the right number of spaces

    as for sorting on titles that begin with The, there's a simple way around this issue -- http://www.sitepoint.com/forums/showthread.php?t=422393
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Location
    Australia, Victoria
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im guessing going the one table one column idea like suggested would be the best idea, because how would you handle the band. 'The Crazy World Of Arthur Brown'?

    There is just to many possibilities that you would have to cover, going down the path you currently are.


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
  •