Member Avatar for 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!

  • 2 Contributors
  • 3 Replies
  • 385 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by ikel
Member Avatar for 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.

Member Avatar for 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...

Edited by ikel because: quotation for clarity
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.