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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|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.
|staff_type||VARCHAR||Up to you to decide – “Doctor”, “Administrator”, “Receptionist”, etc…|
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
|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.
|prescript_note||VARCHAR||Notes, if any.|
|session_id||INTEGER||Prescribed for this session.|
Drugs that are handed out to the customers.
PART 3) APPOINTMENTS
|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.
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
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!