1

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.

Jared Sheila Kat
  • 13
  • 3
  • 1
    This is a many-to-many relationship (A user has many classes, a class has many users). You might check out this article: http://www.tonymarston.net/php-mysql/many-to-many.html – Marc Baumbach Sep 19 '15 at 05:30
  • wrote one up similar [here](http://stackoverflow.com/a/32620163). Same topic, no stretch of the imagination needed – Drew Sep 19 '15 at 05:32

2 Answers2

2

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

Julio Soares
  • 1,200
  • 8
  • 11
2

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
mynawaz
  • 1,599
  • 1
  • 9
  • 16