scheduling medical appointment challenge
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.