Database Design - Healthcare App with many roles. How should I approach it?

[ikel]

Hi,

I am developing a mobile app for a health care institution. I'd like to get a suggestion on how to approach my database design.
There are many user types of this app; residents, nurse, and general-staff.

The "resident" has many information that other types don't have; unit_number, food_allergy, emergency_contact, next_kin_name, next_kin_phone, emergency_doctor, nurse_in_charge, medications.
The "nurse" has distinct information than others; working days, shift, patients, qualifications, etc.
The "general-staff" has; department identifier (clinic, store location)

I have put common attributes in one table ("user_table"), such as; id(pk), name, phone no, date of birth, address.
My question is, how do I approach these diverse types of user in my database. I am using Postgresql.

Should I add 3 additional tables and refer to the user_table.id as a foreign key?
Should I combine the lot into a table? Then, it does not make sense, as I do not expect to see general-staff's medication!

[pritaeas]

Should I combine the lot into a table? Then, it does not make sense, as I do not expect to see general-staff's medication!

Storing it all in one table does not mean it should be viewable in your app. Don't let UI determine your storage. Storing all person related information in a single table has nothing to do with how you want to show it in your app.

Suppose you create three tables and someone decides it would be useful to have one of those fields also for the other types of staff. Or you get in a situation where an employee changes function, e.g. a general staff person decides to become a nurse.

[ikel]

@pritaeas,

You got me there. The ui of the app has been distracting me so much.

Suppose you create three tables and someone decides it would be useful to have one of those fields also for the other types of staff. Or you get in a situation where an employee changes function, e.g. a general staff person decides to become a nurse.

Redundancy and oh dear... headaches...

[ikel]

I case others are wondering how I approached my design, I resolved to put all types of user into a single table, or Single Table Inheritance.

Fewer joins and column types can be used to distinguished user types. Also, as per Pritaeas comment above, the gui does not dictate the design of the database.