CODEWITHSUNDEEP
mysql
Jared Sheila Kat
Jared Sheila Kat

Reputation: 13

MySQL: Storing a list in separate columns? Or is there something better?

So, I'm moderately experienced in PHP/MySQL, I've done a few things before like creating a small chat website or even a small page analytics app. This most recent project, though, is challenging my abilities.

So, I'm designing this app for a school. I have a list of users and a list of classes. I would like to be able to assign a user to multiple classes (Right now I'm storing a single assignment by referencing a UID for the entry in the classes table). Would I have to achieve this by putting in additional columns for each possible assigned class (Having a column for their first class, second class, and so forth to some limit)? Would I have to limit my users to a number of assigned classes? Or is there a more elegant solution? I know that it's recommended to not do a comma separated list in the single cell either (And I can agree on that, as I plan to search for students based on the class UID and such).

So, sorry that I am a bit new to this, but I'm really not sure how to do this. The column for each assigned class would work, but I feel like there should be a more elegant solution.

Anyway, please do let me know, thank you.

Upvotes: 1

Views: 63

Answers (2)

mynawaz
mynawaz

Reputation: 1594

Use a third table to handle the many-many relationships. The class_roll table contains two fields, class_id & student_id. Both are primary to avoid duplicates. This was a class can have zero or many students and a student can be assigned zero or many classes

class
-----
+ class_id
  class_name
  ...

student
-------
+ student_id
  student_name
  ...

class_roll
----------
+ class_id
+ student_id

Upvotes: 2

Julio Soares
Julio Soares

Reputation: 1190

In short you need a third table to track relationship between users and classes

user 1 - class 1

user 1 - class 2

...

user 2 - class 2

...

Make the 2 fields as PK to be sure user 1 cannot be twice enrolled in class 1 for instance.

You will need to make sure deletions in the users and/or classes tables are somehow propagated here and you will be in the right track

Upvotes: 2

Related Questions