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

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

CLINIC MANAGEMENT SYSTEM ERD

Clinic Management System ERD (click to enlarge)

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.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

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.

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *