Alexander  Tolkachev Alexander Tolkachev - 4 months ago 7x
MySQL Question

MySQL reletionships. User roles

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

And Tables:


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


id | name

  1. Patient

  2. Doctor

  3. Pharmasist


id | user_id | role_id

And i want to implement tables such as: doctor_info, patient_info, pharmasist_info. For example:


id | experience | qualification | user_id

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

Tin Tin

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.