Online Appointment System ERD (With Database Structure)

Last Updated: November 10, 2023

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

 

ER DIAGRAM

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

 

APPOINTMENT SYSTEM ERD

Online Appointment System ERD (click to enlarge)

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.

 

 

TABLE STRUCTURE

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

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key.
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

COLUMN DATA TYPE NOTES
location_id INTEGER Primary key.
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

COLUMN DATA TYPE NOTES
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

COLUMN DATA TYPE NOTES
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

COLUMN DATA TYPE NOTES
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.

 

 

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!