0

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:

  • Studies:
    • study_id PK
  • User
    • user_id PK
  • Enrollments:
    • enrollment_id PK
    • user_id FK
    • study_id FK
  • Eligibilities
    • eligibility_id PK
    • user_id FK
    • study_id FK

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.

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
WillJones
  • 907
  • 1
  • 9
  • 19
  • Does this answer your question? [Modeling multiple many to many relationships between the same entities in a relational database](https://stackoverflow.com/questions/41079568/modeling-multiple-many-to-many-relationships-between-the-same-entities-in-a-rela) – philipxy Apr 27 '20 at 13:48
  • Hi philipxy - thanks for your response. The answer you linked did not answer the question that I had. It is a much more general example that did not clearly guide me in my current situation. Additionally, the "How to Ask" link describes the steps to "improve your chances of getting an answer". I'm very happy with the answer I received. Therefore, clearly my question was phrased adequately. – WillJones May 06 '20 at 09:27
  • Getting an answer does not imply your question was phrased adequately. Also I didn't say it wasn't phrased adequately. Also you don't quote everything from the link, and unclear & duplicate questions can be closed (have answers blocked). Also your question is a commonly asked one re DB/SQL subtyping/inheritance. – philipxy May 06 '20 at 16:29

1 Answers1

3

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
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71