scheduling medical appointment challenge I'm working on a medical appointment scheduling system project.

What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment?

database structure:

appointments table:


doctor_id
patient_id
appointment_start_time
appointment_end_time


doctors_working_hours table:


doctor_id
day_of_week
start_time
end_time


When a patient asks for an appointment with a doctor, I want to schedule the first next free time of the doctor according to the doctor's working hours.

the default duration for each appointment is 15 minutes.

What is the most efficient way to do this?
What I would do is something like the process outlined below. I've left in links to pages which I found helpful and I've also left in fields which aren't strictly necessary. I'll outline my thought process as I go.

CREATE and populate doctor and patient tables:

CREATE TABLE doctor
(
  doctor_id   INTEGER NOT NULL AUTO_INCREMENT,
  doctor_name VARCHAR (50) NOT NULL,
  CONSTRAINT doctor_pk PRIMARY KEY (doctor_id)
);
INSERT INTO doctor (doctor_name)
VALUES ('Doctor_1'), ('Doctor_2'), ('Doctor_3'), ('Doctor_4');
SELECT * FROM doctor;


and:

CREATE TABLE patient
(
  patient_id INTEGER NOT NULL AUTO_INCREMENT,
  patient_name VARCHAR (50),
  CONSTRAINT patient_pk PRIMARY KEY (patient_id)
);
INSERT INTO patient (patient_name)
VALUES ('p1'), ('p2'), ('p3'), ('p4'), ('p5'), ('p6'), ('p7'), ('p8'), ('p9'), ('p10'), 
('p11'), ('p12'), ('p13'), ('p14'), ('p15');
SELECT * FROM patient;


Then, we have to construct a date range - I chose 2019-10-10 09:00:00 to 2019-10-15 21:00:00. This range covers a weekend when the doctors don't work. It's best to prototype with a manageable number of records, rather than letting yourself be swamped.

I make use of a RECURSIVE CTE - very powerful and worth getting to know. PostgreSQL has a GENERATE_SERIES function which is very handy for this sort of thing (see PostgreSQL fiddle which duplicates what's done here for MySQL). MariaDB has a sequence engine which also has an analogous capability.

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts &lt; STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
)
SELECT
  DATE_FORMAT(ts, '%a') AS DOW,
  WEEKDAY(ts), 
  WEEKDAY(ts) % 6,
  ts from date_range
WHERE CAST(ts AS DATE) &gt;= CAST('2019-10-10 09:00:00' AS DATE) 
AND   CAST(ts AS DATE) &lt;= CAST('2019-10-15 21:00:00' AS DATE)
-- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
AND DATE_FORMAT(ts, '%H:%i:%s') &gt;= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') &lt;= '21:00:00'
AND DATE_FORMAT(ts, '%a') != 'Sat'
AND DATE_FORMAT(ts, '%a') != 'Sun';


Result (1st 10 records):

DOW WEEKDAY(ts) WEEKDAY(ts) % 6 ts
Thu 3   3   2019-10-10 09:00:00
Thu 3   3   2019-10-10 09:15:00
Thu 3   3   2019-10-10 09:30:00
Thu 3   3   2019-10-10 09:45:00
Thu 3   3   2019-10-10 10:00:00
Thu 3   3   2019-10-10 10:15:00
Thu 3   3   2019-10-10 10:30:00
Thu 3   3   2019-10-10 10:45:00
Thu 3   3   2019-10-10 11:00:00
Thu 3   3   2019-10-10 11:15:00
…
10 rows of 196


So, now we have a range of times for the range that we want. But, an appointment has a start_time and an end_time.

So we perform a self JOIN on this table of dates:

First, in order to store these slot times, we create a table:

CREATE TABLE slot
(
  slot_id    INTEGER NOT NULL AUTO_INCREMENT,
  slot_begin TIMESTAMP NOT NULL,
  slot_end   TIMESTAMP NOT NULL,
  CONSTRAINT slot_pk PRIMARY KEY (slot_id),
  CONSTRAINT slot_begin_end_uq UNIQUE (slot_begin, slot_end)
);


And then derive the slot data as follows:

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts &lt; STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts), 
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) &gt;= CAST('2019-10-10 09:00:00' AS DATE) 
  AND   CAST(ts AS DATE) &lt;= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') &gt;= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') &lt;= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'
  AND DATE_FORMAT(ts, '%a') != 'Sun'
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);


Result (first 10 records):

slot_begin          slot_end
2019-10-10 09:00:00 2019-10-10 09:15:00
2019-10-10 09:15:00 2019-10-10 09:30:00
2019-10-10 09:30:00 2019-10-10 09:45:00
2019-10-10 09:45:00 2019-10-10 10:00:00
2019-10-10 10:00:00 2019-10-10 10:15:00
2019-10-10 10:15:00 2019-10-10 10:30:00
2019-10-10 10:30:00 2019-10-10 10:45:00
2019-10-10 10:45:00 2019-10-10 11:00:00
2019-10-10 11:00:00 2019-10-10 11:15:00
2019-10-10 11:15:00 2019-10-10 11:30:00
…
10 rows of 192


And, also, we scroll down to the end to check that there are no boundary issues:

2019-10-15 20:15:00 2019-10-15 20:30:00
2019-10-15 20:30:00 2019-10-15 20:45:00
2019-10-15 20:45:00 2019-10-15 21:00:00
192 rows


So, perfect. We can see that the last slot is from 20:45 to 21:00 which is the desired result.

-- The origin of this syntax is explained in a link below - check out the PostgreSQL
-- fiddle to see how it should be done!

INSERT INTO slot (slot_begin, slot_end)
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts &lt; STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts), 
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) &gt;= CAST('2019-10-10 09:00:00' AS DATE) 
  AND   CAST(ts AS DATE) &lt;= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') &gt;= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') &lt;= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'   -- &lt;&lt;======== no work on the weekends
  AND DATE_FORMAT(ts, '%a') != 'Sun'   -- &lt;&lt;========
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);


Just a quick check - had issues with various boundary conditions - always check as you go!

SELECT -- A check - I had a few wierd errors and this helped!
       -- There are 4 slots/hour over the whole day 12 working
       -- hours (docs do shifts of 8 hours, so, in the case of 4 days = 48 slots!
    COUNT(DAYNAME(s.slot_begin)) AS cnt,
    DATE_FORMAT(s.slot_begin, '%a') AS the_day
FROM slot s
GROUP BY  DATE_FORMAT(s.slot_begin, '%a');


Result:

cnt the_day
48  Thu
48  Fri
48  Mon
48  Tue


So, this is 12 working hours/day by 4 slots/hour = 48 - so far, so good.

-- Double checking - in particular the boundaries between the days,
-- making sure there are no problems with edge cases.

SELECT  DATE_FORMAT(s.slot_begin, '%a') AS the_day, WEEKDAY(s.slot_begin) AS dow, 
        DATE_FORMAT(slot_begin, '%Y-%m-%d %H:%i') AS start, 
        DATE_FORMAT(slot_end,   '%Y-%m-%d %H:%i') AS end
FROM slot s -- WORKS!
ORDER BY slot_begin;

-- https://stackoverflow.com/questions/6718759/how-to-select-date-and-time-without-the-seconds-in-mysql
-- SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i')`date`, '%Y-%m-%d %H:%i')
-- Result  No days x 12 hours x 4 15 minute slots (4 days = 192 = 4 x 48) 

Result:

the_day dow start   end
Thu 3   2019-10-10 09:00    2019-10-10 09:15
Thu 3   2019-10-10 09:15    2019-10-10 09:30
Thu 3   2019-10-10 09:30    2019-10-10 09:45
Thu 3   2019-10-10 09:45    2019-10-10 10:00
Thu 3   2019-10-10 10:00    2019-10-10 10:15
Thu 3   2019-10-10 10:15    2019-10-10 10:30
Thu 3   2019-10-10 10:30    2019-10-10 10:45
Thu 3   2019-10-10 10:45    2019-10-10 11:00
Thu 3   2019-10-10 11:00    2019-10-10 11:15
Thu 3   2019-10-10 11:15    2019-10-10 11:30
…
10 rows of 192


4 x 48 = 192 ✓ End conditions also checked - not shown.

We now create an Associative Entity - better known as a joining or linking table between the doctors and the slots.

CREATE TABLE doc_slot
(
  slot_id   INTEGER NOT NULL,
  doctor_id INTEGER NOT NULL,
  CONSTRAINT doc_slot_pk PRIMARY KEY (doctor_id, slot_id),
  CONSTRAINT ds_doctor_id_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id),
  CONSTRAINT ds_slot_id_fk   FOREIGN KEY (slot_id)   REFERENCES slot   (slot_id)
);


and poplulate it with a CROSS JOIN.

So, doctors 1 &amp; 2 have both been associated with every possible slot between 09:00 and 17:45 - i.e. work till 18:00. They also have a lunch break from 13:00 to 14:00.

-- A CROSS JOIN between slot and doctor for doctors 1 &amp; 2. They work from 09:00 till
-- 18:00 - last appointment at 17:45. They also have an hour off between 13:00
-- and 14:00 - no appointments!
INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 1 &amp; 2)
SELECT s.slot_id, d.doctor_id
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 9 AND 17  -- 17:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 13  -- no appts. between 13 and 13:45 - lunch
AND   d.doctor_id IN (1, 2)                  -- docs 1 &amp; 2 work 09 - 18
ORDER BY slot_begin, doctor_id;


Now, we do the same for doctors 3 &amp; 4 who will work from 12:00 to 20:45 - finish at 21:00. They also get an hours break from 16:00 to 17:00.

-- For a bit of realism (or maybe not! - variety is the...), the other two 
-- doctors (3 $ 4) are scheduled to work from 12:00 to 21:00 - last appointment
-- 20:45 - and a break between 16:00 and 17:00. Legistation/practices &amp;c. may
-- vary from country to country.

INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 3 &amp; 4)
SELECT s.slot_id, d.doctor_id              -- = 256 for 4 days
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 12 AND 20  -- 20:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 16  -- no appts. between 16 and 16:45
AND   d.doctor_id IN (3, 4)                  -- docs 3 &amp; 4 work 12 - 21
ORDER BY slot_begin, doctor_id;


The check for these two INSERTs is given in the fiddle.

Now, we CREATE an appointment table:

CREATE TABLE appointment 
(
  appointment_id SERIAL,
  doctor_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  appt_begin_time TIMESTAMP NOT NULL,
  appt_end_time TIMESTAMP NOT NULL,
  CONSTRAINT app_pk PRIMARY KEY (appointment_id),
  CONSTRAINT app_doc_uq UNIQUE (doctor_id, appt_begin_time),  -- a doc can only have one appt at a time
  -- CONSTRAINT app_pat_uq UNIQUE (patient_id, appt_begin_time), -- a pat can only have one appt at a time
  -- because of the use of the RAND function, this constraint can only be enabled later!
  CONSTRAINT app_doc_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id)  -- but this will have to wait
);


and populate:

-- I got this strange syntax here:
-- https://mydbops.wordpress.com/2019/01/06/common-table-expression-cte-mysql-8-0/
-- Also useful:
-- https://www.techonthenet.com/mysql/functions/rand.php (RAND())
INSERT appointment (doctor_id, patient_id, appt_begin_time, appt_end_time)
WITH cte1 AS
(
  SELECT             -- the no of doc_slots = no. of days x 32/slots/day x 4 docs
    d.doctor_id AS did,
    FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no, 
    -- DATE_TRUNC('MINUTE', s.slot_begin), 
    -- TO_CHAR(s.slot_begin,'YYYY-MM-DD HH24:MM') AS t_mins,
    s.slot_begin AS a_begin,
    s.slot_end AS a_end  
    -- s.slot_id,       -- (= 256 for 4 days) 

    -- ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,
    -- d.doctor_id AS d_did, d.doctor_name AS d_name
  FROM slot s
  JOIN doc_slot ds
    ON s.slot_id = ds.slot_id
  JOIN doctor d
    ON ds.doctor_id = d.doctor_id
  ORDER BY ds.slot_id, ds.doctor_id
)
SELECT * FROM cte1;


And now our appointment table is populated with every possible combination of doctor and slot + a random patient (FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no,). 

Because of the random nature of the patient_id, there are some duplicates of patient_id and appointment start_time. We need to delete these, and first we need to identify them. To do this, we use the ROW_NUMBER() function.

-- Because of the RAND function, some patients have two appointments at the same time.
-- We need to get rid of these duplicates - scroll down and you'll see some records
-- where rn_p is &gt; 1 - these are duplicate appointment times for the same patient.

SELECT
  appointment_id,
  appt_begin_time,
  doctor_id,
  patient_id,
  ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
FROM appointment
ORDER BY appointment_id;


Scrolling down through the results reveals some records with rn_p = 2 (and sometimes 3 or more).

-- Now to "cleanse" the appoinment table of duplicates!
-- i.e. patients who (from the RANDOM() above have 2 or more appointments at the same time)
-- Don't have to worry about the doctors because they've been assigned via the 
-- CROSS JOIN.
DELETE FROM appointment
WHERE appointment_id IN
(
  SELECT t1.appointment_id
  FROM
  (
    SELECT
      appointment_id,
      appt_begin_time,
      doctor_id,
      patient_id,
      ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
    FROM appointment
    ORDER BY appointment_id  
  ) AS t1 WHERE rn_p &gt; 1
);


A quick SELECT (not shown, see fiddle) shows that about 40 records (on average) are eliminated in this manner.

We can now add a UNIQUE constraint as follows:

ALTER TABLE appointment 
ADD CONSTRAINT appt_patient_appt_begin_uq UNIQUE(patient_id, appt_begin_time);


The appointments table will now be pruned. About 40 records (of the 512 CROSS JOIN) have been deleted, but we need to remove a few more in order to give a reasonable number of available appointments for our queries down the line. This necessitates a weird nested SELECT in the DELETE - see the link for details:

-- The convoluted nested SELECT syntax below was explained here:
-- https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause
-- I got it by searching for
-- "mysql  - You can't specify target table 'appointment' for update in FROM clause"
-- take a look at the PostgreSQL fiddle for how it **_SHOULD_** be done!

DELETE FROM appointment 
WHERE appointment_id IN
(
  SELECT appointment_id FROM
  (
    SELECT 
      appointment_id FROM appointment   -- takes out approx 25 rows - will vary because 
    WHERE                               -- of RANDOM() for patient_id - you can vary this
     -- MOD(appointment_id, 2)  = 0     -- by removing the commented MOD()s.
     -- OR MOD(appointment_id, 3)  = 0 
     -- OR MOD(appointment_id, 4)  = 0
     -- OR MOD(appointment_id, 5)  = 0
     -- OR MOD(appointment_id, 7)  = 0
     -- OR MOD(appointment_id, 11) = 0
     -- MOD(appointment_id, 18) = 0
     appointment_id % 23 = 0
   ) AS t1
);


The number of DELETEd records can be varied by adding or removing WHERE conditions.

So, now, we want to delete the slots that correspond to appointments in the appointment table - otherwise it would be possible to double-book the doctors for a given start_time.

-- Here we delete the doc_slots corresponding to the appointments which
-- are filled! This necessitates JOINing to the slot table (for the begin_time)
-- and then deleting the corresponding doc_slots - ~ 450/512 with 
-- MOD(appointment_id, 23) = 0 above. You can increase the amount of
-- available appoinments by adding more MODs to the WHERE conditions.

DELETE FROM doc_slot ds
WHERE (ds.slot_id, ds.doctor_id) IN
(
  SELECT s_id, ds_did FROM
  (
    SELECT  
      a.appt_begin_time, a.doctor_id, 
      t1.s_id, t1.ds_did 
    FROM appointment a 
    JOIN 
    ( 
      SELECT  
        ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,  
        s.slot_id AS s_id, s.slot_begin AS s_begin  
      FROM   
        doc_slot ds  
        JOIN slot s ON ds.slot_id = s.slot_id   
      ORDER BY s.slot_begin, ds.doctor_id  
    )  t1  
    ON a.appt_begin_time = t1.s_begin  
    AND a.doctor_id = t1.ds_did
  ) AS t2
);


This process normally leaves about 45 - 55 records in the doc_slot table available for future bookings.

-- Finally, the answer to the question!

SELECT 
  d.doctor_name, ds.doctor_id ds_di, ds.slot_id ds_is,
  s.slot_id, s.slot_begin
FROM doc_slot ds
  JOIN slot s ON ds.slot_id = s.slot_id
  JOIN doctor d ON ds.doctor_id = d.doctor_id
WHERE CAST(s.slot_begin AS DATE) = CAST('2019-10-11' AS DATE)
-- AND d.doctor_name = 'Whatever' 
-- many people will have a regular/preferred doctor
-- depending on the circumstances.
ORDER BY s.slot_begin ASC, ds.doctor_id ASC;

-- Even though ASC is the default ORDER BY, I included it for clarity,
-- So, we have ~ 15 - 20 appointments with app % 23 and 4 days and 4 doctors


Result (will vary according to the output of RAND()):

doctor_name ds_di   ds_is   slot_id slot_begin
Doctor_2    2       53      53      2019-10-11 10:00:00
Doctor_4    4       62      62      2019-10-11 12:15:00
Doctor_1    1       63      63      2019-10-11 12:30:00
Doctor_4    4       70      70      2019-10-11 14:15:00
Doctor_4    4       75      75      2019-10-11 15:30:00
Doctor_3    3       76      76      2019-10-11 15:45:00
Doctor_3    3       81      81      2019-10-11 17:00:00
Doctor_2    2       84      84      2019-10-11 17:45:00
Doctor_3    3       95      95      2019-10-11 20:30:00
9 rows


So, when you have an app that allows surgery staff to book appointments, you will have to perform two operations within  a transaction.

1) CREATE a record in the appointment table

AND

2) DELETE the corresponding record in the doc_slot table. 

I'm not sure what the limit is on booking appointments into the future, but once your system is setup, whenever you add a new set of time slots, you can populate doc_slot table with the new potentially available times. These doc_slot records will then be moved into the appointment table and removed from the doc_slot one as new appointments are booked. Your app should allow doctors to mark themselves as unavailable.

Keeping the data in the appointment and doc_slot slot tables for later analysis may provde useful (when no-shows are likely, busy/non-busy periods - other criteria).

You could also search for open source doctor appointment software and see how they do it - that's the beauty of F/LOSS - you can pick 'n mix and borrow ideas.