Foreign key relationships with the same table for two different foreign key columns

Question

Lets say I have a user and appointment table.

users - id, name, email, type(doctor/patient)

appointment- id, doctor_user_id, patient_user_id

My user table data-

And my appointment table data-

I could make two belongs to relationships from appointment with user table.

As you can see my appointment table, I want to store only user that type is doctor to doctor_user_id and patient to patient_user_id. But in this case i can add any user id to doctor_user_id field either it is doctor or patient but i want to add only user id as doctor_user_id only if its type is doctor.

I know how to achieve this with two different table but I was wondering is there any way to achieve this with single user table, Thanks.

Answer 1

Yes, you can achieve this by creating one user table only.

Create 2 foreign key for the "doctor_user_id" and "patient_user_id" of appointment table which references to User table.