在单个字段中存储多个用户

Question

I have a simple events app where there are events and users, users can join multiple events. Each event is a single row in the events table. The users have there own users table.

I'm trying to find the easiest and economical way to store all those users that are attending an event. At the moment im thinking of storing the users id in a attendees field on the events table - i would have to separate them with a comma.

I'm find with that part but im struggling to think of a way to read this field, split them into an array and then fetch the full name of each user.

Hope that makes sense.

Answer 1

Don't do that. MySQL is a relational database model, so go ahead and use relations. In that case, create a new attendance table with two columns: user id and event id.

It will make your tasks even easier; if you need to find all events for one user it's as simple as finding all users for one event.

For example, to get all attendees' names, it would be something like

SELECT u.name
FROM user u
    INNER JOIN attendance a ON a.userid = u.id
    INNER JOIN event e ON a.eventid = e.id
WHERE e.id = <youreventid>;