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
schedule tables are our administrative tables. The other four tables deal with clients, client contacts, and the services provided.
We’ll describe the
service tables first, then move on to the
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.
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.
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.
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.
appointment table is the center of our model. Let’s take a look at each of its attributes:
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.
employee_id– These are references to the
employeetables. 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_idattribute 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_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_timewhen 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_fullvalue minus the amount of
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?