Is this implementation for the “one” side of the relationship correct?

Question

Say I have the following ER diagram:

The "one" side of the relationship of the above diagram means the following:

Each student can meet 0 or 1 teacher.

So I can't do something like this:

But can I do something like this?:

I think the answer is No, because these two implementations are just two different ways of recording the same fact (which is that student with id of 1 met with teacher with id of 1 twice).

Am I correct?

Answer 1

My Interpretation of ER diagrams such as your

which labels the relationship like a Chen Diagram. Is that you can ignore the "met". It's simply a descriptive term and does not indicate a linking table.

Thus you would have the database structure

Teacher
    Id PK

Student
    Id PK
    TeacherId nullable FK -> Teacher.id

If you wanted to have meetings as an entity, then you need to add them to the diagram as a box.

Limiting the relationships to prevent a student ever meeting more than one teacher becomes complex once you introduce this extra table. I'm not sure you can do it with just FKs unless you abandon the immutability of meeting attendees

Answer 2

I am not sure the "Each student can meet 0 or 1 teacher" means what you are saying. The relationship of meetings for a teacher with a student is 1 to many makes sense, but only in specific contexts, e.g., a class/course. For a school, it does not (many teachers, and students that take many classes).

But if there is only one teacher and one course, the first solution is fine. That is, keep a list of meetings (though I might have some sort of timestamp/schedule id as a way of temporally separating the meetings). Then the link table Teacher_met_student can record all student teacher meeting for an organization.

For me, the missing piece is the Course, which would have 1 teacher (and IRL, this is too restrictive, many courses have multiple instructors), many students, and meetings would have teacher, course, student to aggregate the meetings in a particular context.

Answer 3

The shown ER diagram describes the tables and their foreign key relationships, but it leaves out the precise columns in each table and their names. So it is up to you what columns you add to each table.

Your first implementation example does not match the diagram because we can assume the diagram is intended to show all tables and relationships (or at least all between Student and Teacher), but there is no Teacher_met_Student table in the ER diagram. The second implementation, however, is fine, and yes, you can add a number_of_times... column, if it is required.

This has nothing to do with both implementations having almost the same semantics, this is purely about "syntax", nothing else.