CODEWITHSUNDEEP
mysqldatabasedatabase-designerd
Solace
Solace

Reputation: 9020

Can't there be a "nonidentifying" N:M relationship?

My database ERM (Entity Relationship Model) has a TEACHER entity type, which is related to the SUBJECT_MODULE entity type (since different modules in each subject are taught by different teachers, and for some modules, even different chapters in each module can be taught by different teachers) by a TEACH relationship type.

It is a N:M relationship, since each teacher can teach numerous subject modules, and each module can be taught by several teachers.

But in my opinion not an identifying relationship because the subject module is not dependent on the teacher to be uniquely identified; Please say so if you disagree with this.

But from this tutorial (although it just says,"Identifying relationships are typically used for the join tables created from a many-to-many relationship. "), and finding out that the ERD modelling tool of MySQL Workbench contains only one tool to model a M:N relationship, which is for "identifying N:M relationship", at least the tool tip says so... I wonder if an N:M relationship is always or is not always an identifying relationship. Please clarify me on this. Thank you.

enter image description here

Upvotes: 4

Views: 2162

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

I wonder if an N:M relationship is always or is not always an identifying relationship.

The M:N relationship itself is never "identifying" (or "non-identifying" for that matter), but can be comprised from identifying or non-identifying 1:N relationships.

This is not about identifying any of the endpoints (teacher and subject module), this is about identifying the connection between them. In this case, it looks that the combination of keys migrated from the endpoint tables identifies the connection (by being part of the connection's key):

enter image description here

So while M:N relationship itself is not "identifying", the 1:N relationships it is comprised from are.

(The tool UI you quoted probably uses the term "identifying M:N" simply as a shorthand for the above.)

That being said, you could have left the connection without a key on top of these migrated keys:

enter image description here

It would still technically be M:N, but would now allow duplicated connections (i.e. the same teacher connected to the same subject module more than once), in which case the 1:N relationships above would no longer be "identifying".

Upvotes: 2

Related Questions