mysql - single field array or multiple rows

Question

To preface this question I should say I know very little about database efficiency which will become glaringly apparent very shortly.

This is a fairly simple question:

I'd like to store attendance to events within a mysql database. I've thought of two possible methods (there are more, I'm sure):

Create an additional attendance row in the events table, containing a comma separated list of user ids
Create an attendance table, containing two columns, event_id and user_id

My instinct says that method 2 would be the most efficient -- although multiple rows would have to be returned and traversed, this is likely less costly than searching for a particular user_id within a concatinated string...

Can anyone confirm this?

P.S. any other options are also welcome!

Ed_
– Ed_

2012-05-23 21:19:51 +00:00
Commented May 23, 2012 at 21:19 — Ed_
– Ed_, Commented May 23, 2012 at 21:19

Orbiting Eden · Accepted Answer · 2012-05-23 21:40:55Z

3

create a new table!

Otherwise you are not taking advantage of the relational aspect of MySQL and every query you run will have to parse the results of the csv entries (horrbily inefficient)

edited May 23, 2012 at 21:40

answered May 23, 2012 at 21:22

Orbiting Eden

1,51214 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JHS · Accepted Answer · 2012-05-23 21:27:09Z

1

The second option is better. Searching in a comma separated string is time consuming.

However in the 2nd option, make sure the event_id and the user_id are FOREIGN KEYS.

This will INDEX the columns and you will have better searching results.

answered May 23, 2012 at 21:27

JHS

7,8812 gold badges34 silver badges54 bronze badges

Collectives™ on Stack Overflow

mysql - single field array or multiple rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related