-2

I want to assign a list of classes(courses) to my users in SQL. Users may have a different number of courses. I've looked around and haven't really found a good way to do it. I don't have a lot of experience in designing databases. Each user is simply a row of information on a table with a unique userid. Every course in the list needs to have a field for current grade, instructorid, and userid that it belongs to. And maybe more. I'm trying to avoid putting it all in 1 row with a bunch of slots and maybe even empty fields. is there a way to assign a whole table to a user? so that each row can be a course?

Johnny Lin
  • 11
  • 1
  • Can you show your current structure? – Blue Oct 22 '18 at 18:20
  • I think you simply need a couple of tables holding: users, courses, instructors, grades, and subscriptions (when a user subscribes to a course), etc. Whenever there's a link to another table you add a foreign key. So if an user gets a grade the foreign keys in the grades table would be 'userId' and 'courseId'. – KIKO Software Oct 22 '18 at 18:25
  • This is really too broad and off-topic for SO, and might be better on dba.stackexchange.com. But you will probably want a user table, a course table, and the a pivot table that will create a many-to-many relationship between the two. – aynber Oct 22 '18 at 18:27
  • 1
    Whatever you do, _don't_ create a courses column in your users table and fill it with comma separated values. – Don't Panic Oct 22 '18 at 18:54

1 Answers1

1

I would suggest to use associative table, which means using many-to-many relationship. For example structure like this:

Table Student (student_id, name, surname)

Table Course (course_id, name, ..)

Table StudentCourse (student_id, course_id, teacher_id, date_enrolled, date_passed, grade ...)

The StudentCourse is a connection between Student and Course table, as a student can have many courses and in a course can be enrolled many students.enter image description here

I would also suggest further reading on database normalisation, associative tables and maybe this article about many-to-many relationships.

Ivanka Eldé
  • 228
  • 1
  • 2
  • 12