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.

yes, there should be multiple rows, but obviously not in the Events table, but rather in a related table

NOOOoooo… !!! :nono:

why? that’s totally unrealistic

no

“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.

the “very unique need” certainly did not become apparent until now :rolleyes:

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

my mistake, eh

:smiley:

I was purposely being evasive :slight_smile:

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?