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
  • 421 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by ikel

Dani AI

Generated

Good call by to separate UI concerns from storage, and useful to know tried the single-table route. Practical, maintainable pattern for this domain is a hybrid: one canonical users table for shared identity fields, a roles/user_roles mapping for flexible membership, and dedicated tables for stable, sensitive, role-specific data (residents, nurses, staff). Use JSONB only for truly dynamic, non-critical attributes. For healthcare fields (medications, emergency contacts, qualifications, schedules) favor proper relational tables so constraints, indexing and auditability work well.

A minimal example (Postgres) showing the core idea:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  phone TEXT,
  dob DATE,
  address TEXT
);

CREATE TABLE roles ( id SMALLINT PRIMARY KEY, name TEXT UNIQUE NOT NULL );
CREATE TABLE user_roles ( user_id INT REFERENCES users(id) ON DELETE CASCADE, role_id SMALLINT REFERENCES roles(id), PRIMARY KEY(user_id,role_id) );

CREATE TABLE residents (
  user_id INT PRIMARY KEY REFERENCES users(id),
  unit_number TEXT,
  food_allergies TEXT[],
  nurse_in_charge INT REFERENCES users(id)
);

CREATE TABLE medications (
  id SERIAL PRIMARY KEY,
  resident_id INT REFERENCES residents(user_id),
  drug_name TEXT,
  dose TEXT,
  schedule TEXT
);

Operational tips and cautions: store medications, qualifications and shifts as rows (not comma lists) to enable queries and auditing. Enforce "user must have role X" with a small trigger that checks user_roles before inserting into a role table. Use partial indexes and GIN indexes on JSONB when needed. For production healthcare data, add audit-triggering (who/when), encryption at rest, and Postgres Row Level Security (RLS) to limit exposure. Plan migrations from a single-table design by backfilling role tables, adding triggers to keep both representations in sync, switching reads, and then removing deprecated columns once stable.

Recommended Answers

All 3 Replies

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

,

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.