Multiple Many-to-many relationships

I’m new to database design, and I’m wondering what best practice would be to implement something like this in a relational database, MySQL specifically.

Suppose I have the following three concepts

• user: has access to view certain groups of patients
• patient_groups: groups of patients
• patient : discrete people that have to be watched by users

I want users to potentially have access to multiple patient_groups, and for patients to be able to exist in multiple patient groups. Therefore, the relationship between user and patient_groups is many to many, and the relationship between patient and patient_groups is many to many. I was wondering what the best practice is to set this up in a relational database

Answer

The best practice is to have five tables:

• users
• patients
• groups
• user_patients: association table with one row per patient begin watched by a user
• patient_groups: association table with one row per patient and group

Your description hints at a sixth table:

• user_groups with one row per user and group where the user has access to the group.