時隙挑戰 - 醫生預約數據庫架構

我正在嘗試建立一個醫生預約系統，線上患者將有兩種選擇：

1. 第一次訪問（這次訪問應該是20分鐘）
2. 跟進訪問（這次訪問應該是10分鐘）

約束：

1. 價格會因第一次/後續的枯萎而有所不同
2. 醫生可能在插槽之間有休息時間
3. 預訂時系統界面將支持這兩個選項
4. 我們需要插槽之間的最小間隔時間才能達到最大值。醫生可用性的使用

到目前為止，我們完成的模型是基於創建一個 Doctor Slot 表，如下所示

如果 DoctorSlot 為 N 且 N=10 分鐘，請考慮以下範例

Case First Time if(N*2)== Free Make reservation block 兩個槽而不是一個

案例跟進 if(N)==Free Make Reservation Block one slot

範例 醫生：9:00 至 9:10 醫生：9:10 至 9:20 醫生：9:20 至 9:30

病例首次向患者展示 9:00 至 9:20

挑戰：

• 附加時間為 9:00 至 9:20（醫生時段之間可能有緩衝時間（醫生休息））
• 我們將有兩個來自數據庫的插槽 ID，而不是一個（哪個 SlotID 將與 Order 一起使用）
• 如何根據使用者的情況在執行時向使用者顯示我們將使用哪個時間通用模型並在稍後相應地更新價格
• 如果使用者預訂了第一個時間段，然後另一個使用者預訂了後續時間，則會出現間隙以及如何處理數據庫中 SQL Server 中的時間

問題：

1. 實現滿足所有可能場景的解決方案的最佳數據庫模式是什麼？
2. 從 SQL Server/ASP.NET POV 處理時間實體的最佳方法是什麼？

我建議使用一個Appointment表格來儲存每位醫生的目前預約。我們可以在此表上添加一些約束，將預約開始時間限制為十分鐘（例如 9.00、9.10、9.20），並添加一些其他常識檢查，例如EndTimeafterStartTime和醫生不能同時開始兩個預約. 假設您還希望醫生只在上午 9 點到下午 5 點之間工作，因為每個人都需要一些工作與生活的平衡。

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);

我們可以在這個表中插入一些數據來看看它是什麼樣子的。請注意，第三次插入將失敗，因為我們的約束阻止了它。醫生不能同時開始兩次預約。

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');

假設您有一個數字表。如果你沒有很多其他人已經描述瞭如何創建一個。如果所有其他方法都失敗了，這可能會為您創建一個，但這可能不是最好的方法。

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 

現在，如果我們想查看特定醫生的空閒位置，我們需要做的就是指定哪個醫生，以及我們正在尋找的位置有多長：

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;

這看起來有點尷尬，所以如果有人能改進這個日期邏輯，很樂意接受建議。

如果醫生想要休息，則將休息時間作為預約輸入，並且無法預訂。

請注意，表約束不強制執行非重疊約會。這是可能的，但它更複雜。如果這是我的系統，我會考慮一些系統（例如觸發器）來最終驗證約會在插入時不會與現有的約會重疊，但這取決於你。

引用自：https://dba.stackexchange.com/questions/137606