Creating a database for a scenario involving students, teachers and courses

Question

I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup:

Models

• Users(id, first name, last name, gender, password, email)
• Roles(id, name)
• Course(id, name, level, day, start time, end time)

Attempted relationships (might be wrong)

• User 1->has->N Roles (I think I need a user/roles bridging table)
• Students(user role 6) N->has(studies)->M Courses
• Teachers(user role 3-5) N->has(teaches)->M Courses

Example data

Table: Courses

id name    level    teacher   day start_time end_time students (0NF)
--+-------+--------+---------+---+----------+--------+--------------
1  English Beginner 1         Mo  12:00      13:00    101,102...
2  English Beginner 1         Tu  12:00      13:00    101,124...
3  English Beginner 1         Fr  13:00      14:00    101,105...
4  English Expert   1         We  14:00      15:00    145,155...
5  Maths   Single   2         Mo  12:00      13:00    135,163...
6  Maths   Single   3         Tu  13:00      14:00    192,123...
7  Maths   Full     3         Tu  15:00      16:00    134,101...

Example report

Student                       (Id = 101)
-----------------------------

Attends English for Beginners (Taught by Teacher 1)
-----------------------------
Mon - 12:00 to 13:00
Tue - 12:00 to 13:00
Fri - 13:00 to 14:00

Attends Maths (Full)          (Taught by Teacher 3)
-----------------------------
Tue - 15:00 to 16:00

Please forgive me for any inconsistencies in structure - I am a beginner in DB design and DBA StackExchange also. Could I have the steps explained to arrive at a solution?

Answer 1

• "Roles" has a different meaning.

• A bridging ('mapping') table is how specify a 'relationship' between two tables that is "many-to-many". Example: Students:Classes.

• 1-many works differently. It needs a column in one table to link to the other. Example: Worker:Manager

In designing an "Entity-Relationship" ("ER") schema do something like this:

1. One table per "entity": Student, Teacher, Course, Class. (This assumes there are no 'student teachers'.) Each entity table would have numerous columns for attributes such as name (most tables), date (for class),

2. Establish the relations between them. Many:many: Student:Class, Teacher:Class; 1:many: Class:Course

Building a list of students as 101,103,... is best done via a JOIN and GROUP BY and GROUP_CONCAT(), not by having a column with such a list.

Since a "class" might multiple times a week need to be another table session, and 1:many for class:session.