設計複雜的交叉表數據完整性檢查

我正在嘗試開始開發一個應用程序，雖然我有一些程式碼，但我的數據庫模型也已經到了。在接下來的部分中，我將描述我的應用程序應該做什麼，然後是我對我的數據庫模型的期望，最後我將向你展示我迄今為止解決我的問題的嘗試。希望你的經驗能派上用場。

這是一個簡單的應用程序，用於為特定提供商的服務創建約會。例如，該應用程序將使您能夠在 10:00 創建一個由您最喜歡的美髮師 John Doe 完成的理髮預約。

現在問題來了。即每個服務提供商，在這種情況下，我們的 John Doe 先生，可能在不同的時間在不同的地點，如果根據他的工作計劃，在我們的一個子公司中與 John Doe 預約可能是不正確的。計劃在完全不同的地方。

這些考慮使我發現我的數據庫中有以下基本實體：

• 服務（代表您可以作為服務購買的東西，例如理髮）
• 提供者（我們的 John Doe）
• 地點（子公司所在地）
• 預約（服務預約的實際代表）

對於這些實體，以下約束對我來說代表“有效數據”（如果不應該在數據庫中完成，請糾正我）：

• 提供者只能在特定時間位於單個位置
• 提供者只能在特定時間預約一次
• 單一的約會完全由單一的提供者提供（當我展示我目前已經制定的解決方案時，你會看到這個約束的相關性）
• 單一地點完全提供單一約會
• 只有當提供者計劃此時在該位置時，我們才能在特定位置與特定提供者預約。

我已經設法實現了大部分約束，但有些約束似乎總是不能用簡單的工具來滿足。我決定為了簡化事情，避免使用時間間隔，只允許在數據庫中輸入特定的時間條目。即預訂和位置的時間間隔由時間戳的集合表示，時間戳必須始終是 5 分鐘的倍數並且必須有 0 秒。這是我到目前為止的模型：

create table service (
   id serial primary key,
   name varchar(255) not null,
   unique(name)
);
create table provider (
   id serial primary key,
   name varchar(128) not null,
);
create table location (
   id serial primary key,
   name varchar(255) not null,
   unique(name)
);
-- This table specifies the exact times at which a provider is at a specific location
create table provider_location_time(
   id serial primary key,
   provider_id integer not null,
   location_id integer not null,
   time_block timestamp not null check (cast(extract(minute from time_block) as integer) % 5 = 0 and cast(extract(second from time_block) as integer) = 0),
   foreign key (provider_id) references provider(id),
   foreign key (location_id) references location(id),
   unique(provider_id, time_block) -- this constraint ensures that provider is only at single location at specific time
);

create table appointment(
   id serial primary key,
   service_id integer not null
);

-- Specifies the times which are reserved for single appointment
create table appointment_time(
   id serial primary key,
   appointment_id integer not null,
   provider_location_time_id integer not null, -- usage of this foreign key ensures that the appointment can only be made at locations and times where a provider is available as described in fifth constraint listed above
   foreign key (appointment_id) references appointment(id),
   foreign key (provider_location_time_id) references provider_location_time(id),
   unique(provider_location_time_id) -- ensures that single time slot available for provider at location is booked only for single appointment. Avoids double booking of providers by accident
);

現在給定上面的模型，我不能確保第三個約束，即：

單一預約完全由單一提供者提供

由於無法確保上述約束，我無法確保約會始終在單個特定位置完成，因為我可以將約會附加到任意提供者-位置-時間組合。

這可以通過更好的設計來解決還是觸發器是我唯一的選擇？

在數據庫中使用聲明性引用完整性對所有應用程序的規則進行建模是不切實際的。從 1) 數據完整性、2) 業務規則的角度考慮您的約束。努力在您的數據庫中強制執行數據完整性規則，並期望其他業務規則需要在程式碼中檢查。

單一預約完全由單一提供者提供

為什麼？這個規則在某種程度上是業務領域的基礎嗎？如果您將此規則融入您的數據模型並且客戶希望將多個提供商安排在一次約會中怎麼辦？允許這樣做的數據模型不是更有用嗎？

至於您的問題的詳細資訊，我認為該appointment表在這裡沒有用。我會用appointment_time桌子折疊它

create table appointment(
   id serial primary key,
   service_id integer not null
   provider_location_time_id integer not null, -- usage of this foreign key ensures that the appointment can only be made at locations and times where a provider is available as described in fifth constraint listed above
   foreign key (service_id) references service(id),
   foreign key (provider_location_time_id) references provider_location_time(id),
   unique(provider_location_time_id) -- ensures that single time slot available for provider at location is booked only for single appointment. Avoids double booking of providers by accident
);

這樣一個appointment人只有一個provider_location_time_id，所以只有一個provider。

這是我想出的解決方案，它尊重我的所有約束並且是為 Postgres 編寫的：

create table provider (
   id serial primary key,
   name varchar(128) not null
);
create table service (
   id serial primary key,
   name varchar(255) not null,
   unique(name)
);
create table location (
   id serial primary key,
   name varchar(255) not null,
   unique(name)
);

create table appointment(
   id serial primary key,
   service_id integer not null
);

create table provider_location_time(
   id serial primary key,
   provider_id integer not null,
   location_id integer not null,
   time_block timestamp not null check (cast(extract(minute from time_block) as integer) % 5 = 0 and cast(extract(second from time_block) as integer) = 0),
   appointment_id integer,
   foreign key (provider_id) references provider(id),
   foreign key (location_id) references location(id),
   foreign key (appointment_id) references appointment(id),
   constraint PROVIDER_AT_SINGLE_LOCATION_AT_A_TIME unique(provider_id, time_block), -- Provider can be at single location at given time
   constraint APPOINTMENT_BOOKS_TIME_ONCE unique(appointment_id, time_block), -- appointment books a single time block once
   constraint APPOINTMENT_PROVIDED_BY_SINGLE_PROVIDER EXCLUDE USING gist (provider_id WITH <>, appointment_id WITH =),
   constraint APPOINTMENT_PROVIDED_AT_SINGLE_LOCATION using gist (appointment_id with =, location_id with <>)
);

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