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?
Best Answer
"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:
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),
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 aJOIN
andGROUP BY
andGROUP_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.