Tutors, Students, Courses database design

Question

I have 3 entities: Tutors, Students, Courses

Tutors teach many courses

Students can be assigned to many courses that are taught by many tutors

Tutors and students need to be able to log into the system

I would like to represent this design using the following tables:

users
- id
- username
- password
- first_name
- last_name
- email
- password
- phone
- role_id (Student or Tutor or Admin)
- created
- modified

roles (Student, Tutor)
- id
- name
- created
- modified

courses (The courses that Tutors can teach and that Students can be assigned to)
- id
- name
- created
- modified

users_courses
- id
- user_id
- course_id
- created
- modified

The problem I have with the above design (users_courses table) is that, let's say we have Tutor A and Tutor B that teach Math. If student X is registered to that math course we can't know if student X is being tutored by Tutor A or Tutor B.

I would really like to be able to use a single users table to keep all the users to make things simple.

Any advice please?

What's the point of the roles table? Just to store the "student/tutor" attribute of the user? Otherwise, why not add a "tutor_id" onto the users_courses which points to the tutor for that course? — Gerik
I find that weird having in a table two columns that points to the same table. I'm not sure that's good practice... @Gerik — user765368
It's actually good practice because you're storing the most minimal amount of information while providing the data that you're asking for. If you look into topics such as self-relating tables you'll see what I mean. — Gerik
Actually..just to expand on this thought to be a little more clear: consider the structure of a family. If you had a table called person you might add attributes such as: mother, father, spouse, brother, sister (we'll make the assumption there's only one of each). The easiest way to store this information is to have each member reference the person table. Now, if you want to expand this so a person has more than one brother, etc, then you'd introduce a new mapping table. :) Hope this helps. — Gerik
Can you describe what that person table relationship would look like please? Preferably in an answer that I can accept. I think you're getting closer to what I want here... @Gerik — user765368

reaanb reaanb · Accepted Answer · 2018-03-18T08:25:30

The problem is that you want to handle students and teachers differently (students enroll in a course taught by a specific teacher) but you modeled them the same (they're just users).

Instead of roles, create subtypes of users. Create tables for teachers and students - you can reuse the primary keys from users, but you'll be able to handle subtype-specific attributes and relationships.

While it's possible to get away with only those entity sets, I suggest you add the concept of a section as well. It has different names in different systems or parts of the world, but most school systems I've seen have something like it to represent a partition of a course's students with an associated teacher.

users
- id PK
- username
- password
- first_name
- last_name
- email
- password
- phone
- created
- modified

students
- user_id PK FK

teachers
- user_id PK FK

courses
- id PK
- name
- created
- modified

sections
- section_id PK
- teacher_id FK
- course_id FK
- created
- modified

students_sections
- student_id PK FK
- section_id PK FK
- created
- modified

In a real world system, you'll also need to take time into account - students and teachers normally attend different courses in different years or semesters.

Tutors, Students, Courses database design

2 Answers