2

I'm designing a MySQL database with some tables for a clinic and I need three different user types: admin, medic and patient.

What I did was to create a table called users where its columns are precisely the common fields shared by admins, medics and patients (there is of course a primary key called id_user which auto increments every time a user is added).

Then I created three tables regarding the specific data for each user type: admin, medic and patient in which I have a field called id_user which is a foreign key to id_user in the table users.

When I tried to establish the foreign key constraint for the three user type tables, phpMyAdmin doesn't allow me to set ON DELETE as "SET NULL" (I think that would make sense because if I delete a user from the users table then it should automatically set the fields as NULL in the medic, admin or patient tables, right?) and gives me the error "relation has not been added".

Doubt 1: What's happening here that I'm not aware of?

Doubt 2: Should I forget this way of relating tables and simply add the specific fields for each user type in the users table although some users will have some fields set as NULL?

Here is an image illustrating my database:

enter image description here

Improve this question

1 Answer 1

Reset to default
1

You should use Cascading Deletes instead of setting fields null, if you delete a user you want them gone if you null out the data you'll just have tables filled with nulls.

Also from what i understand you created 3 tables for each of the permission levels if this is the case you should maybe handle that in code with conditions checking the permissions level

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.