Welcome to a sharing of an ERD of an online appointment system. Yes, there are plenty of such “appointment system ERDs” all over the Internet. But they all seem to be fixed to doctor appointments. So here’s my own version, one that I try to keep as general as possible – Read on!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
APPOINTMENT SYSTEM ERD
To keep things simple, I have grouped this system into 4 sections.
- Users – The staff, consultants, customers, doctors, clients, etc…
- Locations – The clinics, offices, consultation rooms, etc…
- Appointments – Needs no explanation.
- Schedule – Sort of an “extra”… Will explain more below.
FOR REFERENCE ONLY
I can hear the “master professional trolls” go – “This structure is stupid”, “It’s inaccurate”, or “I will do this instead”. By all means, this is only a simple reference.
- If you only have one location, one venue – Go ahead and remove the locations.
- If you don’t want to track the history, go ahead and remove it.
- If the business is not running on a fixed schedule, feel free to remove it.
Yes, you are building your own system here. If any part of this “one size fits all” example doesn’t make sense, go ahead and change it to fit your own.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|user_type||VARCHAR||Up to you to decide – “A”dmin, “D”octor, “C”onsultant, “P”atient, etc…|
|user_name||VARCHAR||The user’s full name.|
|user_email||VARCHAR||The user’s email. Unique to prevent duplicates.|
|user_tel||VARCHAR||The user’s telephone number.|
|user_password||VARCHAR||The user’s password.|
Some people like to create multiple “user tables” – Customers, Doctors, Consultants, Patients, etc… Well, that only makes sense if all of them have wildly different datasets. But otherwise, it will be wise to stick with “user type”.
PART 2) LOCATIONS
|location_name||VARCHAR||Name of the location.|
|location_address||VARCHAR||Address of the location.|
Self-explanatory, but a quick disclaimer to make – This may not make sense for people who only have one location, or maybe run online appointments. You decide for yourself if “locations” is useful for your project.
PART 3) APPOINTMENTS
3A) “MAIN” APPOINTMENTS
|appointment_date||DATE||Primary key, date of the appointment|
|appointment_slot||VARCHAR||Primary key, it’s up to you to decide. It can be hourly slots, N hour slots, AM/PM, etc…|
|appointment_status||VARCHAR||Up to you to decide. Maybe “p”ending, “b”ooked, “c”anceled, “a”ttended.|
|location_id||INTEGER||Foreign key, where the session is being held at.|
|staff_id||INTEGER||Foreign key, User ID of the consultant.|
|customer_id||INTEGER||Foreign key, User ID of the customer.|
This is the “core” of the project – Which consultant, which customer, which location, and at what time.
3B) APPOINTMENT HISTORY
|history_date||DATE TIME||Primary key, the timestamp when the history entry is made.|
|appointment_date||DATE||Primary and foreign key.|
|appointment_slot||VARCHAR||Primary and foreign key.|
|history_status||VARCHAR||As above, the appointment status.|
|history_notes||VARCHAR||Notes, if any.|
To keep track of the appointments.
PART 4) SCHEDULE
|schedule_day||VARCHAR||Primary key. Take note, this is not “date” but “day” – Mon, Tue, etc…|
|schedule_slot||VARCHAR||Primary key, the time slot.|
|staff_id||INTEGER||Foreign key. This consultant will be servicing this time slot by default.|
|location_id||INTEGER||Foreign key, at which location.|
- If all your appointments have to be made manually, go ahead and skip this.
- This is only useful if you have a fixed schedule. For example, this doctor will be at this clinic on Mon/Wed/Fri.
- The idea of having a schedule is automation. For example:
- The system will run an automated script every Sunday night.
- Automatically create “open for booking” appointment entries.
- This also opens up the possibility of linking up with other systems – If a consultant is on leave for that particular day, the slot will not be open, or an admin has to reassign another consultant.
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!