Best practice for PHP/MySQL Appointment/Booking system

Question

I need some people to battle a "best practice" for a PHP/MySQL appointment system for a hairdresser I'm currently working on. Hopefully, together we can clear some things up to avoid having to re-do the system afterwards. I've been looking at SO and Google for some tutorials, best practices etc. but I haven't found anything that fits the needs I have.

Basic Information

There are multiple hairdressers available each day, each hairdresser has his/her own agenda containing his/her appointments with customers. Connected to a hairdresser is a table containing the times he/she is available during the week.

Table: people

+----+------+-------+-----------+
| id | name | email | available |
+----+------+-------+-----------+
| 1  | John | [email protected] | Y         |
| 2  | Sara | [email protected] | N         |
+----+------+-------+-----------+

Table: times (there is a primary key for this table, I left it out of the schedule below)

+-----------+-------------+-----------+-----------+
| people_id | day_of_week | start     | end       |
+-----------+-------------+-----------+-----------+
| 1         | 1           | 08:00     | 12:00     |
| 1         | 1           | 12:30     | 17:00     |
| 1         | 3           | 09:00     | 12:00     |
| 1         | 4           | 10:30     | 16:00     |
| 1         | 5           | 09:00     | 17:00     |
| 1         | 6           | 09:00     | 12:00     |
| 2         | 1           | 09:00     | 12:00     |
| 2         | 2           | 09:00     | 12:00     |
| 2         | 3           | 09:00     | 12:00     |
+-----------+-------------+-----------+-----------+

As you can see there is a one to many relationship between people and times (obviously, since there are 7 days in a week). But besides that there is also an option to add a break between the hours of a day (see people_id = 1, day_of_week = 1: 08:00-12:00 and 12:30-17:00).

Furthermore there is a table called 'hairdress_types', this is a table containing the various types of appointments one can make (like coloring hair, cutting hair, washing, etc). This table contains the amount of time this appointment takes in minutes.

At last I have a table appointments which is pretty simple:

id, people_id, types_id, sex, fullname, telephone, emailaddress, date_start, date_end

Date start and date end would be full DATETIME fields in MySQL making it easier to calculate using MySQL query functions.

What's the best practice?

So, the way I set things up, a front-end user would select a date in a field triggering an ajax/jquery function that finds all hairdressers available at the choosen date. The user then specificies a hairdresser (this is not mandatory: a user can also choose to select 'Any' hairdresser option) and the type of appointment he/she wants to make.

After submitting the first form, the following information can be used:

1. date (day, month and year)
2. people_id (can be 0 for 'Any' or an ID if a hairdresser was selected)
3. hairdress_type (which is linked to an amount of minutes the appointment takes)

Using this information I would either select the available dates from the selected hairdresser OR I would loop al available hairdressers and their available dates.

This is where my minds gets a mental breakdown! Because what is the best way to check the available dates. The way I thought would be the best was:

1. Query the hairdressers times for the given date (1 at a time)
2. Query the appointments table using the startdate of the result of query1 + the amount in minutes the appointment type takes (so: SELECT * FROM appointments WHERE ((date_start >= $start AND date_start <= ($start+$time)) OR (date_end > $start AND date_end <= ($start+$time)) AND people_id = 1)
3. As soon as no results are found I assume this spot is free and this is presented as an option to the user

The biggers problem I'm facing is point 2: My mind is really going crazy on this query, is this the complete query I need to find appointments matching a specific timespan?

Thanks for reading & thinking along! :-)

// Edit: a little more "testdata":

John - Monday - 12:00 - 17:00. Appointments: - 12:00 - 12:30 - 14:30 - 15:30

A user wants to have an appointment which takes 2 hours, in the exampel above I would check:

1. Is there an appointment between 12:00 and 14:00? Yes,.. proceed to next spot
2. Is there an appointment between 14:00 and 16:00? Yes,.. proceed to next spot
3. Is there an appointment between 16:00 and 18:00? Error, not available after 17:00

Thus.. it might be a better option to use "timeblocks" of 10/15 minutes. Making the checks:

1. 12:00 - 14:00
2. 12:10 - 14:10
3. 12:20 - 14:20 etc..

This would find the available spot between 12:30 and 14:30.

// Edit 2: Possbile query to step 2

I've been working out some stuff on paper (a table with appointments and possible empty spots to use). And I came up with the following. An appointment can not be made in case:

1. There is an appointment with start_date BETWEEN $start and $end
2. There is an appointment with end_date BETWEEN $start and $end
3. There is an appointment with start_date < $start and end_date > $end

Querying the above to the appointment table together with people_id would result in either no rows (= free spot) or one/multiple row(s) in which case the spot is taken.

I guess the best way to find open spots is to query the database for blocks of X minutes with a start interval of 10 minutes. The bad side of this solution is that I would neet 6 queries for every hour, which would be about 48 queries for every hairdresser... Any ideas on how to reduce that amount of queries?

Answer 1

In the end I went for a system that generated timestamps for the start and end dates in the database. While checking I added one second to the start and subtracted one second from the end to avoid any overlapping time for appointments.

What did I end up doing

Obviously I'm not sure this is the best practice, but it did work for me. The user starts by selecting their sex and a preference for the day. This sends an AJAX request to get the available personel and various kinds of appointment types (f.e. coloring hair, cutting hair, etc).

When all settings have been choosen (sex, date, personel and type) I start by some simple validations: checking the date, checking if the date("N") is not 7 (sunday). If everything is okay, the more important stuff is started:

1) The appointment type is fetched from the database including the total amount of time this type takes (30 minutes, 45 minutes, etc) 2) The available personal is fetched (a complete list of people on that day or just a single person if one is chosen) including their available times

The personel (or one person) is then looped, starting with their own starting time. At this point I have a set of data containing:

$duration (of the appointment type)
$startTime (starting time of the person selected in the loop)
$endTime (= $startTime + $duration)
$personStart (= starting time of the person)
$personEnd (= end time of the person)

Let's take this demo data:

$duration = 30 min
$startTime = 9.00h
$endTime = 9.30h
$personStart = 9.00h
$personEnd = 12.00h

What I'm doing here is:

while( $endTime < $personEnd )
{
    // Check the spot for availability
    $startTime = $endTime;
    $endTime = $startTime + $duration;
}

Obviously, it's al simplified in this case. Because when I check for availability, and the spot is not free. I set the $startTime to be equal to the latest appointment found and start from there in the loop.

Example:

I check for a free spot at 9.00 but the spot is not free because there's an appointment from 9.00 till 10.00, then 10.00 is returned and $startTime is set to 10.00h instead of 9.30h. This is done to keep the number of queries to a minimum since there can be quiet a lot.

Check availability function

// Check Availability
public static function checkAvailability($start, $end, $ape_id)
{
  // add one second to the start to avoid results showing up on the full hour
  $start += 1;
  // remove one second from the end to avoid results showing up on the full hour
  $end -= 1;

  // $start and $end are timestamps
  $getAppointments = PRegistry::getObject('db')->query("SELECT * FROM appointments WHERE
    ((
        app_start BETWEEN '".date("Y-m-d H:i:s", $start)."' AND '".date("Y-m-d H:i:s", $end)."' 
          OR
        app_end BETWEEN '".date("Y-m-d H:i:s", $start)."' AND '".date("Y-m-d H:i:s", $end)."'
      ) 
    OR
      (
    app_start < '".date("Y-m-d H:i:s", $start)."' AND app_end > '".date("Y-m-d H:i:s", $end)."'
     ))
    AND
     ape_id = ".PRegistry::getObject('db')->escape($ape_id));

    if(PRegistry::getObject('db')->num_rows($getAppointments) == 0) {
      return true;
    } else {
      $end = 0;
      foreach(PRegistry::getObject('db')->fetch_array(MYSQLI_ASSOC, $getAppointments, false) as $app) {
        if($app['app_end'] > $end) {
          $end = $app['app_end'];
            }
    }
    return $end;
     } 
}

Since I'm storing appointments as "From:10.00 Till:11.00" I have to make sure to check spots from 11:00:01 till 11:59:59, because otherwise the appointment at 11:00 will show in the results.

At the end of the function, in case an appointment is found, I loop the results and return the latest end. This is the next start in the loop I mentioned above.

Hopefully this can be of any help to anyone. Just as info: ape_id is the ID of the "Appointment Person" it is linked with.