Slot Time Challenge - Doctor Appointment database schema I am trying to make a doctor appointment system where patient online will have two options:


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


Constraints:


Price will be different based on wither its a first time/follow up
Doctor might have break times between slots
System interface will support both options when booking the slot
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: 


What is the best database schema to achieve a solution that satisfy all possible scenarios?
What is the best method to deal with time entity from SQL Server/ASP.NET POV?
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) &gt; = 9 AND DATEPART(HOUR, StartTime) &lt; = 16),
    CONSTRAINT CHK_EndTime_BusinessHours CHECK (DATEPART(HOUR, EndTime) &gt; = 9 AND DATEPART(HOUR, DATEADD(SECOND, -1, EndTime)) &lt; = 16),
    CONSTRAINT CHK_EndTime_After_StartTime CHECK (EndTime &gt; 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 &lt; 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) &lt; a.EndTime AND CONVERT(time(0), s.EndTime) &gt; a.StartTime)
                        AND a.DoctorID = @doctorID
                        AND a.[Date] = CONVERT(date, s.StartTime))
    AND DATEPART(HOUR, s.StartTime) &gt;= 9
    AND DATEPART(HOUR, DATEADD(MINUTE, -1, s.EndTime)) &lt;= 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.