Clinic Managment System ERD (With Database Structure)

Welcome to a sharing of an ERD of a simple clinic management system. So you have picked up a project for a clinic? Or stuck with this as a school assignment? Well, here is a sharing of my own simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

CLINIC MANAGEMENT SYSTEM ERD

To keep things simple, I have grouped this system into 3 sections.

• Users – Some may prefer “doctors and patients”, but I don’t think this needs an explanation.
• Appointments – Scheduled sessions.
• Sessions – Consultation, screening, treatment, etc…

 

FOR REFERENCE ONLY

I can hear the “behind-the-monitor professionals” banging on their keyboards. “This is dumb”, “this is stupid”, “it’s not accurate”. By any means, this is only a simple example.

• There must be a consultation/treatment session to hand out prescriptions… Because of drug control. If there is no such thing, go ahead and change it on your own.
• If you want to add “suppliers”, “stock control”, “or transactions”, go ahead and do it. Although these will cross into inventory and finance.
• If “customers” sound wrong, change it to “patients” on your own; If it makes sense to combine “staff and customer” into a single “users”, go ahead.

The point is, nobody is stopping you from creating your own “perfect system”. You decide your own rules, decide what works best for yourself.

 

 

 

PART 1) USERS

1A) CUSTOMERS

COLUMN	DATA TYPE	NOTES
customer_id	INTEGER	Primary key.
customer_name	VARCHAR	The customer’s full name.
customer_email	VARCHAR	The customer’s email. You can set this to unique to prevent duplicate registrations.
customer_tel	VARCHAR	The customer’s contact number.
customer_address	VARCHAR	The customer’s address.

Some people may prefer to use the term “patients”, but I am just being generic here. There are aesthetic clinics, and “patients” don’t sound right. “Customers” just sound better in any case.

 

1B) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_type	VARCHAR	Up to you to decide – “Doctor”, “Administrator”, “Receptionist”, etc…
staff_name	VARCHAR	Full name.
staff_email	VARCHAR	Email.
staff_tel	VARCHAR	Contact number.
staff_password	VARCHAR	Login password.

A common newbie mistake that I commonly see in “other clinic ERDs on the Internet” is – They only have doctors. Really? How about the other staff members? What if it is not a medical clinic? A staff table with “staff type” will better cover the different possibilities.

 

 

PART 2) SESSIONS & PRESCRIPTIONS

2A) SESSIONS

COLUMN	DATA TYPE	NOTES
session_id	INTEGER	Primary key.
session_date	DATE TIME	The session timestamp.
session_type	VARCHAR	Up to you to decide – “Consultation”, “treatment”, “screening”, etc…
session_notes	VARCHAR	Notes, if any.
customer_id	INTEGER	Foreign key, customer being attended to.
staff_id	INTEGER	Foreign key, staff attending to the customer.

Once again, some people may prefer the term “treatment”. But that may not always be the case – Some people may just want to get medical advice (consultation only), and some may just be a regular checkup (screening only). A “session” will better deal with the different possibilities.

 

2B) PRESCRIPTIONS

COLUMN	DATA TYPE	NOTES
prescript_id	INTEGER	Primary key.
prescript_name	VARCHAR	Drug name.
prescript_qty	VARCHAR	Quantity given.
prescript_dosage	VARCHAR	Drug dosage.
prescript_note	VARCHAR	Notes, if any.
session_id	INTEGER	Prescribed for this session.

Drugs that are handed out to the customers.

 

 

PART 3) APPOINTMENTS

COLUMN	DATA TYPE	NOTES
appointment_id	INTEGER	Primary key.
appointment_date	DATE TIME	Appointment date and time.
appointment_type	VARCHAR	For what kind of service – Consultation, screening, treatment?
appointment_notes	VARCHAR	Notes, if any.
customer_id	INTEGER	Foreign key, customer making the appointment.
staff_id	INTEGER	Foreign key, staff that will attend to the customer.

Finally, this should be pretty self-explanatory.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart