SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a "Best Practice" for this mySQL table structure question?

    I have a table which will hold music events held in a city. Each musical event is a row in the Events table. One of the items to associate with each event is the people that will be attending, so that when we view an event we can see:

    Event Name
    Event Date
    Event Organizer
    People attending this event: [list of people names and details from table PEOPLE]

    My question is: should the Events table have multiple rows per event for each person that will be attending, e.g.
    Event1 michael
    Event1 jane
    Event1 jessica
    Event2 bob
    Event2 chris

    OR

    should the Events table have one row per event with a column for persons attending but store this value as a comma separated value, e.g.
    Event1 michael,jane,jessica
    Event2 bob,chris

    OR

    is there a best practice around how I should be structuring such a table?

    Also, assume that each person will only ever attend one event. Should the PERSONS table also have a column for Event ID linking each person to an event? I find this to be unnecessary since the Events table will have a column for persons attending, but not sure if it's prudent to add an event ID to the persons table from the get-go.

    Help would be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bakhlawa View Post
    My question is: should the Events table have multiple rows per event for each person that will be attending,
    yes, there should be multiple rows, but obviously not in the Events table, but rather in a related table

    Quote Originally Posted by bakhlawa View Post
    should the Events table have one row per event with a column for persons attending but store this value as a comma separated value
    NOOOoooo..... !!!!!

    Quote Originally Posted by bakhlawa View Post
    Also, assume that each person will only ever attend one event.
    why? that's totally unrealistic

    Quote Originally Posted by bakhlawa View Post
    Should the PERSONS table also have a column for Event ID linking each person to an event?
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "yes, there should be multiple rows, but obviously not in the Events table, but rather in a related table"

    @r937: Are you saying-
    PERSONS table should have one row for each person.
    EVENTS table should have one row for each event.
    NEW TABLE (ATTENDANCE) should link EVENTS to PERSONS, i.e. each row has Event ID and Persons ID columns only?

    Event1 Michael
    Event1 Jane
    Event1 Jessica

    This application is for a very unique need where persons can only attend one event.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bakhlawa View Post
    This application is for a very unique need where persons can only attend one event.
    the "very unique need" certainly did not become apparent until now

    i imagined that "music events held in a city" would not have such a restriction

    my mistake, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was purposely being evasive

    The persons in question are in fact mystery shoppers. They get to attend only one event in the city. However, you've pushed my thinking on this and we can't absolutely rule out that the business might at some point in the future have the same mystery shopper attend two different events. I will rethink this piece.

    I'm assuming the ATTENDANCE table as I described is the way to go to show relationship between EVENTS and PERSONS (with the added benefit that if in the future the same person attends more than one event, we could just add one more row to reflect this)?

    Is the benefit of this approach basically that placing multiple rows in the events table for each person attending is essentially just duplicating the entire events row with only the person column value changed? i.e., space, performance, bandwidth consideration?


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
  •