CODEWITHSUNDEEP
sqldatabasedatabase-designm2m
WillJones
WillJones

Reputation: 905

Is it a good idea to use multiple M2M relationships between two entities?

We have a situation where we appear to need to use two different M2M relationships between two entities in a database.

The entities are Users and Studies. Users can enroll in studies, but also might be eligible for studies.

Therefore, we are considering modelling this with two different tables: Enrollments and Eligibilities.

With schemas looking like:

My question is: is this a good idea? I know that this would create two M2M relationships between two entities when it is advised to combine them into one. The issue when combining these relationships into one table is that these relationships are independent. For example, a user might be eligible for a study and not enroll, and a user might enroll in a study but not be eligible.

Upvotes: 0

Views: 254

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Yes, perfectly OK; just pay attention to keys.

I know that this would create two M2M relationships between two entities when it is advised to combine them into one.

No it is not advised. Simply focus on logic, predicates and constraints, as opposed to jargon (m2m ...).

-- User USR exists.
--
user {USR}
  PK {USR}

-- Study STY exists.
--
study {STY}
   PK {STY}

-- User USR is eligible for study STY.
--
eligibility {USR, STY}
         PK {USR, STY}

FK1 {USR} REFERENCES user  {USR}
FK2 {STY} REFERENCES study {STY}

If a user enrolled into a study, then that user must be eligible for that study.

-- User USR enrolled in study STY.
--
enrollment {USR, STY}
        PK {USR, STY}

FK {USR, STY} REFERENCES eligibility {USR, STY}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

Upvotes: 3

Related Questions