Database design - How should we design a teacher/student relationship when student records themselves also need to be associated to each other

Question

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:

• teachers_students_mappings

id	teacher_id	student_id
1	1	1
2	1	2
3	1	3
4	2	1

However, we have another complexity here to grapple with. A student record can come from different "sources", i.e.:

1. 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).
2. The student registered on their own (through an app), or
3. 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:

• students

id	name	id_number	source_type
1	Rachel Doe	9898123	created_by_system
2	Rachel Doe	9898123	app_user
3	John Doe	9833456	created_by_teacher

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 teachers_students_mappings table?

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).

Answer 1

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 SELECT):

INSERT INTO Students (id_number, name, foo, ...)
    SELECT id_number, name, foo, ...
        FROM other table ...
    ON DUPLICATE KEY UPDATE
        foo = VALUES(foo), ...;

Answer 2

Before entering the data of a new record you have to check if the id_number exists. In that case you have to return the identification and not insert that record.