Database Design - How Should We Design A Teacher/student Relationship When Student Records Themselves Also Need To Be Associated To Each Other
So this is something that we've been struggling to try and figure out how to optimize.
In a simple world, this might be solved by a simple many-to-many bridging table like this:
However, we have another complexity here to grapple with. A student record can come from different "sources", i.e.:
- The student was created by another system transferring the records of that student and their classes to us (most student and teacher-student relationship records are created this way).
- The student registered on their own (through an app), or
- The student was manually created by a teacher (via a teacher portal)
We therefore have cases where student records, from different sources, should be related to each other too:
e.g. Above, we have a case where student_ids 1 & 2, Rachel Doe, are actually the same person. The first record was generated automatically by the system, and the 2nd record was created when Rachel registered in the system on her student app. Both records share an
id_number, which is a unique identifier in the school.
If we made it so both student_ids 1 and 2 were linked to all of Rachel's teachers, her teachers would be seeing duplicated records, i.e. both Rachel's app account, and her record created by the system. It seems it might therefore be smarter to relate both these
student_id records to each other, but how would that impact the
It is also theoretically possible that 3 unique student records exist for the same student (i.e. the teacher manually created a record, then the system created it, then the student created an account on the app).
Since you already have an
id_number that is unique and provides the mechanism to realize that you have one, not two Rachel Does, use it as the
PRIMARY KEY of the
Students table. Do not also have another
id for each student.
On the other hand, if you have a
StudentHistory table that records various actions taken by or for each student, it would need its own
PRIMARY KEY. This seems to be a separate topic.
So, when "inserting" or "Updating" a row for a student, use
INSERT INTO Students (id_number, name, foo, ...) VALUES ("9898123", "Rachel Doe", 123, ...) ON DUPLICATE KEY UPDATE foo = VALUES(foo), ...;
Or (if coming from a
INSERT INTO Students (id_number, name, foo, ...) SELECT id_number, name, foo, ... FROM other table ... ON DUPLICATE KEY UPDATE foo = VALUES(foo), ...;
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key