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 < 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) >= CAST('2019-10-10 09:00:00' AS DATE)
AND CAST(ts AS DATE) <= 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') >= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') <= '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 < 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) >= CAST('2019-10-10 09:00:00' AS DATE)
AND CAST(ts AS DATE) <= 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') >= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') <= '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 < 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) >= CAST('2019-10-10 09:00:00' AS DATE)
AND CAST(ts AS DATE) <= 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') >= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
AND DATE_FORMAT(ts, '%a') != 'Sat' -- <<======== no work on the weekends
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);
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 & 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 & 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 & 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 & 2 work 09 - 18
ORDER BY slot_begin, doctor_id;
Now, we do the same for doctors 3 & 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 &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 & 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 & 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 > 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 > 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.
