CODEWITHSUNDEEP
mysql
MiSo
MiSo

Reputation: 463

How to store multiple user IDs for each "event" ID in MySQL

I'm totally new to MySQL. I'm working on a university courses system where each course ("event") has many students("users") that are signed in to that course.

I want a to build a MySQL table that for each course ID will store all the students IDs that are signed in to it. I found two approaches:

  1. Serialize students IDs (using json for example)
  2. Create a new row for every new student with course ID and student ID

The first approach has a performance issue because of serialization/de-serialization of students IDs, and the second approach will cause the table to explode very quickly.

What do you think I should do? Is there a better solution?

Thanks!

Upvotes: 0

Views: 279

Answers (2)

Hazim
Hazim

Reputation: 1451

Your case is a Many-to-Many mapping.

You would have 3 tables.

  1. User
  2. Events
  3. User_Events

The third table will store the mappings, it should consist of two columns, user_id and event_id

user_id would be the foreign key for the User table, similarly, event_id would be the foreign key for the Event table. You can then retrieve data by Joins in queries.

so if you have users with ID 1, 2, and 3

and similarly courses with ID 1, 2, 3, 4

Here's how you will represent students that are signed in a course.

user_id, event_id
  1         2
  1         3
  2         1
  3         4

This means, user 1 is signed in course 2 and 3, user 2 in course 1 and user with ID 3 in course with ID 4.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Each student/user can be in several courses/events.

Each course/event can contain several students/users.

So you need a bridge table containing all relations:

  • user_event (userid, eventid)

with a unique constraint on userid + eventid.

(You can add a technical ID (e.g. user_event_id) to the table, but that's not necessary.)

Upvotes: 0

Related Questions