Slot Time Challenge - Doctor Appointment database schema

Question

I am trying to make a doctor appointment system where patient online will have two options:

1. First time visit (this visit should be 20 mins)
2. Follow up visit (this visit should be 10 mins)

Constraints:

1. Price will be different based on wither its a first time/follow up
2. Doctor might have break times between slots
3. System interface will support both options when booking the slot
4. we need the least gap time between slots to make max. usage of doctors availability

The model we finished so far was based on creating a Doctor Slot table that's like the below

Consider the below Example if DoctorSlot is N and N=10 mins

Case First Time if(N*2)== Free Make reservation block two slots instead of one

Case Follow Up if(N)==Free Make Reservation Block one slot

Example Doctor: 9:00 to 9:10 Doctor: 9:10 to 9:20 Doctor: 9:20 to 9:30

Case First Time Show to patient 9:00 to 9:20

Challenges:

• Append time from 9:00 to 9:20 (there could be a buffer time (break for doctor) between slots for doctors)
• We will have two slot IDs from database instead of one (which SlotID will be used with Order)
• How to show user in run time based on his case which time generic model we will use and update prices later on accordingly
• If user booked a first time slot, then another user booked a follow up, there will be gaps and how to handle time in SQL Server in database

Questions:

1. What is the best database schema to achieve a solution that satisfy all possible scenarios?
2. What is the best method to deal with time entity from SQL Server/ASP.NET POV?

Answer 1

I would suggest an Appointment table which stores the current appointments for each doctor. We can add some constraints on this table which limit the appointment start times to even ten-minute times (e.g. 9.00, 9.10, 9.20) plus add some other common sense checks like EndTime after StartTime and doctor can't have two appointments starting at the same time. Assume that you'd also like doctors to only work between 9am and 5pm, because everyone needs some work-life balance.

CREATE TABLE Appointment (
    DoctorID char(1) NOT NULL,
    [Date] date NOT NULL,
    StartTime time(0) NOT NULL CONSTRAINT CHK_StartTime_TenMinute CHECK (DATEPART(MINUTE, StartTime)%10 = 0 AND DATEPART(SECOND, StartTime) = 0),
    EndTime time(0) NOT NULL CONSTRAINT CHK_EndTime_TenMinute CHECK (DATEPART(MINUTE, EndTime)%10 = 0 AND DATEPART(SECOND, EndTime) = 0),
    Status char(1) NOT NULL,
    UserID char(1) NOT NULL,
    Price int NOT NULL,
    CONSTRAINT PK_Appointment PRIMARY KEY CLUSTERED (DoctorID, [Date], StartTime),
    CONSTRAINT CHK_StartTime_BusinessHours CHECK (DATEPART(HOUR, StartTime) > = 9 AND DATEPART(HOUR, StartTime) < = 16),
    CONSTRAINT CHK_EndTime_BusinessHours CHECK (DATEPART(HOUR, EndTime) > = 9 AND DATEPART(HOUR, DATEADD(SECOND, -1, EndTime)) < = 16),
    CONSTRAINT CHK_EndTime_After_StartTime CHECK (EndTime > StartTime));
CREATE INDEX iDoctor_End ON Appointment (DoctorID, [Date], EndTime);

We can insert some data to this table to see what it looks like. Note that the third insert will fail because it is prevented by our constraint. The doctor can't have two appointments starting at the same time.

INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:10:00', 'P', '1', '0');
INSERT INTO Appointment VALUES ('A', '20170420', '09:20:00', '09:40:00', 'C', '2', '10');
INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:20:00', 'C', '2', '10');

Let's assume that you have a numbers table. If you don't many other people have described how to create one. If all else fails, this could create one for you but it's probably not the best way.

CREATE TABLE Numbers (Number int PRIMARY KEY CLUSTERED);
DECLARE @number int = 0;
WHILE @number < 1000
BEGIN
    INSERT INTO Numbers VALUES (@number);
    SET @number += 1;
END 

Now if we want to see free slots for a particular doctor, all we need to do is specify which doctor, and how long the slot is that we're looking for:

DECLARE @doctorID char(1) = 'A';
DECLARE @length tinyint = 20;
WITH Slots AS (
    SELECT StartTime = DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))),
           EndTime = DATEADD(MINUTE, @length, DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))))
      FROM Numbers)
SELECT TOP 15 DoctorID = @doctorID,
    s.StartTime,
    s.EndTime
  FROM Slots AS s
  WHERE NOT EXISTS (SELECT 1 
                      FROM Appointment AS a
                      WHERE (CONVERT(time(0), s.StartTime) < a.EndTime AND CONVERT(time(0), s.EndTime) > a.StartTime)
                        AND a.DoctorID = @doctorID
                        AND a.[Date] = CONVERT(date, s.StartTime))
    AND DATEPART(HOUR, s.StartTime) >= 9
    AND DATEPART(HOUR, DATEADD(MINUTE, -1, s.EndTime)) <= 16
ORDER BY s.StartTime;

That looks a little awkward, so if anyone can improve that date logic happy to take suggestions.

If a doctor wants a break, then enter the break as an appointment and it won't be available for booking.

Note that the table constraints don't enforce non-overlapping appointments. This is possible but it's more complicated. If this were my system I'd think about some system (e.g. trigger) to finally verify that the appointment doesn't overlap with an existing one at the time of insert, but that's up to you.

Answer 2

Here is a functionally equivalent (and IMO easier to read) version of mendosi's code for MariaDB/MySQL, with some additional exposition and slightly simplified logic in some areas.

Here's a sufficient schema for an Appointment table. In a moment, we'll also add an INSERT trigger which will ensure that new entries don't clash with existing entries.

CREATE TABLE Appointment (
    doctorID    INT UNSIGNED    NOT NULL,
    `date`      DATE            NOT NULL,
    startTime   TIME(0)         NOT NULL,
    endTime     TIME(0)         NOT NULL,

    CONSTRAINT PRIMARY KEY (doctorID, `date`, startTime),

    CONSTRAINT mustStartOnTenMinuteBoundary CHECK (
        EXTRACT(MINUTE FROM startTime) % 10 = 0
        AND EXTRACT(SECOND FROM startTime) = 0
    ),
    CONSTRAINT mustEndOnTenMinuteBoundary CHECK (
        EXTRACT(MINUTE FROM endTime) % 10 = 0
        AND EXTRACT(SECOND FROM endTime) = 0
    ),
    CONSTRAINT cannotStartBefore0900 CHECK (
        EXTRACT(HOUR FROM startTime) >= 9
    ),
    CONSTRAINT cannotEndAfter1700 CHECK (
        EXTRACT(HOUR FROM (startTime - INTERVAL 1 SECOND)) < 17
    ),
    CONSTRAINT mustEndAfterStart CHECK (
        endTime > startTime
    )
);

First, we define a function to determine whether a given time slot can be allocated as a new appointment:

DELIMITER //

CREATE FUNCTION slotIsAvailable(
    doctorID            INT,
    slotStartDateTime   DATETIME,
    slotEndDateTime     DATETIME
) RETURNS BOOLEAN DETERMINISTIC
BEGIN
    RETURN NOT EXISTS (
        -- This table will contain records iff the slot clashes with an existing appointment
        SELECT *
        FROM Appointment AS a
        WHERE
                CONVERT(slotStartDateTime, TIME) < a.endTime   -- These two conditions will both hold iff the slot overlaps
            AND CONVERT(slotEndDateTime,   TIME) > a.startTime -- with the existing appointment that it's being compared to
            AND a.doctorID = doctorID
            AND a.date = CONVERT(slotStartDateTime, DATE)
    );
    END;
END; //

DELIMITER ;

Now here's that INSERT trigger mentioned earlier to ensure no clashing appointments are stored:

DELIMITER //

CREATE TRIGGER ensureNewAppointmentsDoNotClash
    BEFORE INSERT ON Appointment
    FOR EACH ROW
BEGIN
    IF NOT slotIsAvailable(
        NEW.doctorID,
        CAST( CONCAT(NEW.date, ' ', NEW.startTime)  AS DATETIME ),
        CAST( CONCAT(NEW.date, ' ', NEW.endTime)    AS DATETIME )
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Appointment clashes with an existing appointment!';
    END IF;
END; //

DELIMITER ;

Now that the Appointment table is properly set up, we can insert some valid sample entries:

INSERT INTO Appointment VALUES
(1, '2019-10-06', '09:20', '09:30'),
(1, '2019-10-06', '09:40', '09:50'),
(1, '2019-10-06', '11:00', '11:20'),
(1, '2019-10-06', '11:20', '11:40'),
(1, '2019-10-06', '11:40', '12:00'),
(1, '2019-10-06', '13:00', '14:00'),
(1, '2019-10-06', '16:00', '16:40');

If you try to insert an invalid appointment entry, an error will be thrown as a result of the ensureNewAppointmentsDoNotClash trigger. In fact, this trigger will throw an error even before the primary key constraint is checked, so that could be deemed redundant; for my solution, I opted to have an ID field for the Appointment table, rather than using a compound primary key.

Now here's the procedure to get a result set of available time slots of a given length, with a given doctor. Notice that we make use of our slotIsAvailable function that we defined earlier and also used in our INSERT trigger.

-- The ID of the doctor to book the appointment with.
SET @doctorID = 1;

-- The moment from which to start searching for availble time slots
SET @searchStart = CURRENT_TIMESTAMP;

-- The duration of the appointment to book, in minutes.
SET @duration = 20;

WITH
    SlotStart AS (
        -- This table will list all the 10-minute-aligned timestamps that occur after `@searchStart`
        SELECT
            CONVERT(@searchStart, DATE)
            + INTERVAL (EXTRACT(HOUR FROM @searchStart)) HOUR
            + INTERVAL ( EXTRACT(MINUTE FROM @searchStart) DIV 10 + seq + 1 ) * 10 MINUTE
        AS startDateTime
        FROM seq_0_to_1023
    ),
    Slot AS (
        SELECT
            startDateTime,
            startDateTime + INTERVAL @duration MINUTE   AS endDateTime
        FROM SlotStart
    ),
    AvailableSlot AS (
        SELECT
            @doctorID   AS doctorID,
            startDateTime,
            endDateTime
        FROM Slot AS s
        WHERE
                slotIsAvailable(@doctorID, s.startDateTime, s.endDateTime)
            AND EXTRACT(HOUR FROM s.startDateTime) >= 9
            AND EXTRACT(HOUR FROM (s.endDateTime - INTERVAL 1 MINUTE)) <= 16
    )
SELECT *
    FROM AvailableSlot
    WHERE
            CONVERT(startDateTime, DATE) = CONVERT(@searchStart, DATE)
        AND CONVERT(endDateTime,   DATE) = CONVERT(@searchStart, DATE)
    ORDER BY startDateTime ASC;

The above query, with the above sample records for Appointment, and with @searchStart equal to '2019-10-06 06:00', yields:

+----------+---------------------+---------------------+
| doctorID | startDateTime       | endDateTime         |
+----------+---------------------+---------------------+
|        1 | 2019-10-06 09:00:00 | 2019-10-06 09:20:00 |
|        1 | 2019-10-06 09:50:00 | 2019-10-06 10:10:00 |
|        1 | 2019-10-06 10:00:00 | 2019-10-06 10:20:00 |
|        1 | 2019-10-06 10:10:00 | 2019-10-06 10:30:00 |
|        1 | 2019-10-06 10:20:00 | 2019-10-06 10:40:00 |
|        1 | 2019-10-06 10:30:00 | 2019-10-06 10:50:00 |
|        1 | 2019-10-06 10:40:00 | 2019-10-06 11:00:00 |
|        1 | 2019-10-06 12:00:00 | 2019-10-06 12:20:00 |
|        1 | 2019-10-06 12:10:00 | 2019-10-06 12:30:00 |
|        1 | 2019-10-06 12:20:00 | 2019-10-06 12:40:00 |
|        1 | 2019-10-06 12:30:00 | 2019-10-06 12:50:00 |
|        1 | 2019-10-06 12:40:00 | 2019-10-06 13:00:00 |
|        1 | 2019-10-06 14:00:00 | 2019-10-06 14:20:00 |
|        1 | 2019-10-06 14:10:00 | 2019-10-06 14:30:00 |
|        1 | 2019-10-06 14:20:00 | 2019-10-06 14:40:00 |
|        1 | 2019-10-06 14:30:00 | 2019-10-06 14:50:00 |
|        1 | 2019-10-06 14:40:00 | 2019-10-06 15:00:00 |
|        1 | 2019-10-06 14:50:00 | 2019-10-06 15:10:00 |
|        1 | 2019-10-06 15:00:00 | 2019-10-06 15:20:00 |
|        1 | 2019-10-06 15:10:00 | 2019-10-06 15:30:00 |
|        1 | 2019-10-06 15:20:00 | 2019-10-06 15:40:00 |
|        1 | 2019-10-06 15:30:00 | 2019-10-06 15:50:00 |
|        1 | 2019-10-06 15:40:00 | 2019-10-06 16:00:00 |
|        1 | 2019-10-06 16:40:00 | 2019-10-06 17:00:00 |
+----------+---------------------+---------------------+

Answer 3

First of all, why is your time slot of fixed length? Even with current design, you can have a slot with start_time = 9:00 and end_time = 9:20, and it can be one id. Furthermore, you can make a bridge table to connect type of exams (i.e. type 1 = first exam, type 2 = control exam etc) to length of slots, like this:
exam_type slot_length_in_minutes
1 20
2 10
3 15
In this case, when you book time for the exam, in the UI you would select an exam type from some kind of select box (combo-box) and and then search for a free slot of appropriate size.

Also you didn't give us your reservations table structure, so we can help you with that. But I guess you have some sort of time slot id there, and also the id of a selected doctor...? In that case, just by joining time slots and reservations could show you the complete timetable. Hope it was of any help. If not, feel free to ask more.

Answer 4

Here is Jivan Pal's solution converted to SQL Server syntax. I'm not that great at sql so there could be cleaner syntax but this is what I got to work. Also be aware that I have changed some things to match my naming conventions and specific implementation.

Create Numbers Table:

CREATE TABLE numbers (number INT CHECK (number > 0) PRIMARY KEY);
GO

CREATE PROCEDURE populateNumbers
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @x int;
    SET @x = 0;
    WHILE @x < 1024 BEGIN
        INSERT INTO Numbers VALUES (@x);
        SET @x = @x + 1;
    END;
    SET @x = NULL;
END; 
GO

EXEC populateNumbers;
DROP PROCEDURE populateNumbers;

Create Appointments Table:

CREATE TABLE appointments (
    did INT NOT NULL,
    pid INT NOT NULL,
    appointmentdate DATE NOT NULL,
    startTime TIME(0) NOT NULL,
    endTime TIME(0) NOT NULL,
    
    PRIMARY KEY (did, pid, appointmentdate, startTime),
    
    CONSTRAINT mustStartOnThirtyMinuteBoundary CHECK (
        DATEPART(MINUTE FROM startTime) % 30 = 0
        AND DATEPART(SECOND FROM startTime) = 0
    ),
    CONSTRAINT mustEndOnThirtyMinuteBoundary CHECK (
        DATEPART(MINUTE FROM endTime) % 30 = 0
        AND DATEPART(SECOND FROM endTime) = 0
    ),
    CONSTRAINT cannotStartBefore0900 CHECK (
        DATEPART(HOUR FROM startTime) >= 9
    ),
    CONSTRAINT cannotEndAfter1700 CHECK (
        DATEPART(HOUR FROM DATEADD(SECOND, 1, startTime)) < 17
    ),
    CONSTRAINT mustEndAfterStart CHECK (
        endTime > startTime
    )
);

Create isSlotAvailable Function:

CREATE FUNCTION slotIsAvailable(
    @did INT,
    @slotStartDateTime DATETIME2(0),
    @slotEndDateTime DATETIME2(0)
) RETURNS BIT 
BEGIN
    RETURN CASE WHEN EXISTS (
        SELECT 'TRUE'
        FROM appointments AS a
        WHERE
                CAST(@slotStartDateTime AS  TIME(0)) < a.endTime
            AND CAST(@slotEndDateTime AS    TIME(0)) > a.startTime
            AND a.did = @did
            AND a.appointmentdate = CAST(@slotStartDateTime AS  DATE)
    ) THEN 'FALSE' ELSE 'TRUE'
    END;
END; 
GO

Create ensureNewAppointmentsDoNotClash Trigger:

CREATE TRIGGER ensureNewAppointmentsDoNotClash ON appointments
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @did int,
            @appointmentdate DATE,
            @startTime TIME,
            @endTime TIME
    SELECT  @did = did,
            @appointmentdate = appointmentdate,
            @startTime = startTime,
            @endTime = endTime
    FROM INSERTED
        IF NOT dbo.slotIsAvailable(@did,
            CAST(CONCAT(@appointmentdate, ' ', @startTime) AS DATETIME2(0)),
            CAST(CONCAT(@appointmentdate, ' ', @endTime) AS DATETIME2(0))
        ) = 1
        BEGIN
            RAISERROR('Appointment clashes with an existing appointment!', 16, 1);
            ROLLBACK TRANSACTION
        END
ELSE
    BEGIN   
        INSERT INTO appointments(did, pid, appointmentdate, startTime, endTime)
        SELECT did, pid, appointmentdate, startTime, endTime FROM INSERTED
    END
END; 
GO

Test Insert Statements: They are the same so I didn't change anything.

Appointment Search Procedure: I didn't create this b/c I don't think I'll be using it. I'll come back and re-edit my answer if I end up using it.