A Database Model to Manage Appointments and Organize Schedules

Today’s most popular services may well be the ones where people sell their time. Many of these services are largely based on human contact: psychologists, tutors, dentists, hairdressers, etc. In these professions, the saying that“Time is money” is literally true. Therefore, these professionals need to be organized.

True, you don’t absolutely need an application to be organized. But the right app can make your life much easier, improve client satisfaction, and make your business better in general. If your client pays you for your time, they shouldn’t experience any stress resulting from your bad organization – for example, fighting over a single (yet double-booked) time slot.

In this article, we’ll talk about the functionalities such an app requires, and we’ll take a look at the database model behind it.

The App and Its Desired Features

We want to build an application that is very intuitive and easy to use. It would likely be a web application, but a mobile app could also work.

To understand how this app needs to function, think of your local hair salon. (Throughout this article, we’ll use the example of a hairdressing business to illustrate app features and requirements.) In many hair salons, employees receive incoming calls and use a computer, laptop or tablet to organize their schedules. So, in just a few clicks, our app should be able to:

• Insert a new client’s data
• View all appointments on a certain date
• Arrange a new appointment and assign it to a certain employee
• Enter new appointment data without assigning it to an employee
• Manage basic employee data
• Maintain a future schedule for weeks or months in advance

When we’re arranging a new appointment, we need to check existing appointments and use additional controls to avoid overlapping customers (scheduling two people for the same time slot and employee). For the purposes of this model, we’ll assume that every employee has their own workspace, either a chair in the main salon or their own separate room. If several employees share the same resources (tools and a workspace) we’ll need to make modifications to this model.

Introducing The Model

The model is fairly straightforward, but there are a few things that require special attention.

The central part of our model is the appointment table, which stores all the details about appointments. We have only one dictionary, the service table. The employee and schedule tables are our administrative tables. The other four tables deal with clients, client contacts, and the services provided.

We’ll describe the employee, client and service tables first, then move on to the schedule and appointment tables.

Employees, Clients and Services

As you might guess, the employee table stores a list of all our employees. It contains only basic data (names and employee ID numbers) rather than any specific details (like usernames or passwords). We’ll use it in our application to record which employee created an appointment and to designate which employee should take care of a client. We’re assuming that all employees have the same level of rights in the application, so there is no need to implement roles and user rights. We’re also assuming that all salon employees can take care of any task, from haircutting to answering calls to sweeping the floor.

We’ll use the client table to store simple data about our regular clients. If we know the client’s name, there is no reason not to store it in the database. That way we’ll be able to relate our appointment history with the client’s history. We could use this information to send the client promotional materials or contact them when we have special offers.

The service dictionary stores a list of all the services we provide. The service_name attribute is unique. We expect it to have values like “cut”, “color”, “style”, “shampoo”, etc. For each service, we’ll store an estimated duration in minutes and an estimated current price. These two attributes will help us calculate the appointment’s expected end time (where we know the appointment start time) and the overall price the client will pay.

Scheduling and Appointments

Scheduling is crucial in service businesses like hair salons. We must know ahead of time when each employee is available. Most salons set their schedule a week or two in advance; when a client calls, the salon personnel know immediately when there’s time for an appointment and which hairdresser is available. In case the client asks for a specific employee, this app’s schedule can tell us the time when he or she is available.

The service_booked table stores a list of the services each client requested, plus the service price at that time. This information could be used to avoid asking the client what they want (again) when they arrive at the salon.

The service_provided table is a list of all the services the client received during their appointment. This information is used to calculate the overall price and to keep a history of services provided to the customer.

The appointment table is the center of our model. Let’s take a look at each of its attributes:

• date_created and employee_created – These refer to 1) the date and time when a record was inserted in the table; and 2) the employee who inserted the record.
• client_id and employee_id – These are references to the client and employee tables. Both are arbitrary. We may not know who the client is, e.g. somebody who called without giving us his name or who walked in and asked for an appointment. The employee_id attribute refers to the specific employee requested by the client. Remember, a client may not ask for any stylist in particular; also, the appointment can be set so far in advance that the schedule is not yet finalized.
• client_name and client_contact – These are mandatory text fields where we can enter details related to specific appointments. If we don’t know the values, we can fill these fields with placeholder text (e.g. “unknown”, “unavailable”, etc).
• start_time – This is the time when the appointment should start.
• end_time_expected – This is calculated as start_time + (the sum of all booked service times). The expected end time is theoretical; we’ll store the real end_time when we finish with the appointment.
• end_time – This is the actual time when the appointment ended.
• price_expected – This is the sum of all the booked services’ prices.
• price_full – Here we find the sum all of provided services’ prices; it can differ from price_expected. Maybe the client changed their mind or added a service. Also, the price of the service could change between the booking time and the time the service was provided.
• discount – This attribute stores the discount amount that we’ll offer to a client for one appointment. (The discount is stored as a numerical value.) Clients can be given a discount because they combine certain services, are given a loyalty bonus or a special offer, or a combination of these.
• price_final – This is the price_full value minus the amount of discount.
• canceled – If the appointment was canceled for any reason, we’ll set this attribute to “True”.
• cancellation_reason – We’ll store the reason for a canceled appointment here.

Before we can insert a new appointment into the app’s schedule, we need to see which employees are scheduled to be at work during the requested time. In this step, we won’t check whether employees actually have appointments at the desired time or not. The query below will simply return every employee who will be present during a given time slot.

SELECT employee.first_name, employee.last_name, schedule.from, schedule.to
FROM schedule, employee
WHERE schedule.employee_id = employee.id
AND schedule.from >= @desired_time
AND schedule.to <= @desired_time;

If a client specifically asks for a stylist, we need to see if that employee already has other appointments set for the desired time:

SELECT COUNT(*) AS appointment_no
FROM appointment, employee
WHERE appointment.employee_id = employee.id
AND appointment.start_time >= @desired_time
AND appointment.end_time_expected <= @desired_time
AND appointment.employee_id = @desired_employee;

If the query returns any number greater than zero – one would be the expected maximum – then the desired employee is occupied and we should offer an appointment with another stylist.

If the query returns a zero, we still need to check before we make an appointment. Maybe we have three hairdressers present at that time and three appointments without anyone assigned to them. In that case, we can’t make a new appointment. We’ll also do this check when the client hasn’t asked for a specific employee. The query below will give us the information we need:

SELECT a.desired_time, a.employees_working, b.appointments_set
FROM

(
	SELECT @desired_time AS "desired_time", COUNT(*) AS employees_working
	FROM schedule
	WHERE schedule.from >= @desired_time
	AND schedule.to <= @desired_time
) AS a

LEFT JOIN

(
	SELECT @desired_time AS "desired_time", COUNT(*) AS appointments_set
	FROM appointment, employee
	WHERE appointment.employee_id = employee.id
	AND appointment.start_time >= @desired_time
	AND appointment.end_time_expected <= @desired_time
) AS b 

ON a.desired_time = b.desired_time

For all of the above queries, we should quickly get an overview of the schedule for the selected employee and the desired date. But what if the stylist has an appointment that starts five minutes after the requested time? We need to see that. The query below will list all the appointments for an employee:

SELECT client.client_name, appointment.start_time, appointment.end_time_expected
FROM appointment, client
WHERE appointment.client_id = client.id
AND Date(appointment.start_time) = Date(@desired_time)
AND appointment.employee_id = @selected_employee;

What Could We Add to This Model?

There are many possible improvements we could add to this model. Suppose an employee gets sick; it would be good to be able to quickly review their upcoming appointments, contact their scheduled clients, and set new times for the clients to come in. We could also upgrade our app by adding functionalities to handle billing, calls, client contacts etc. And as mentioned before, we could easily adjust this model to support any business that has a similar process.

The model itself is not complicated. As a rule, these applications are easy to work with — as long as everything is running smoothly. But in some cases, we may need a different approach.

We also need to ensure that we’ve implemented proper checks before we set a new appointment. You don’t want to have two clients fighting over the same chair! How would you change this model to fit your needs?