MySQL relationships. User roles

Question

I have 3 type of user roles: Patient, Doctor, Pharmasist

And Tables:

Users

id | name | surname | username | password | etc..

---

Roles

id | name

---

1. Patient
2. Doctor
3. Pharmasist

users_roles

id | user_id | role_id

---

And I want to implement tables such as: doctor_info, patient_info, pharmasist_info.

For example:

doctor_info

id | experience | qualification | user_id

What relationship should I use to tie users with doctor_info, patient_info, pharmasist_info and how to implement it correctly?

Answer 1

Assuming all id column from each table is primary key.

users: id, name, ...

roles: id, name

users_roles: id, user_id, role_id (make user_id UNIQUE key, so 1 user can only have 1 role)

doctor_info: id, user_id, ... (make user_id UNIQUE key as well, so 1 user can only be 1 doctor)

patient_info: similar to doctor_info

pharmasist_info: similar to doctor_info

(OPTIONAL) If 1 user has 1 role and 1 role is belonged to many users, you could remove users_roles table completely, and just add role_id in users table.

One issue is that you can have: 1 user can be a doctor, a patient and a pharmasist altogether with your table structure. You'll have to add some validation in your code to make sure it won't happen.