共享可用性规则模式设计

Question

我正在设计一个模式，其中包括可用性规则的概念。这些规则是链接实体可用的一天的时间段。这可以是企业的营业时间、工作人员的工作时间或特定服务的可用时间。此架构需要允许同一天的多个时间段。规则不是在实体之间共享的，企业有一组规则，其他每个实体都是相同的。

我第一次经历这个过程，给了我这个模式，但是availabilityGroup实际上并不包含任何数据，这让我觉得可以用不同的方式来完成。

CREATE TABLE `business` (
  `id` integer PRIMARY KEY,
  `name` varchar(255),
  `availabilityId` integer,
  CONSTRAINT `FK_business_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `staff` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_staff_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);


CREATE TABLE `service` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_service_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `availabilityGroup` (
  `id` integer PRIMARY KEY
);

CREATE TABLE `availabilityRule` (
  `id` integer PRIMARY KEY,
  `availabilityGroupId` integer,
  `day` string,
  `startTime` time,
  `endTime` time,
    CONSTRAINT `FK_availability_rule_to_availability_group` FOREIGN KEY (`availabilityGroupId`) REFERENCES `availabilityGroup` (`id`)
);

另一种可能的解决方案可能是使用这样的方法来使用单个表继承，这是我们使用的ORM太https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html#single-table-inheritance所支持的

CREATE TABLE `singleAvailabilityRulesTable` (
  `id` integer PRIMARY KEY,
  `discriminator` enum('business','staff','service'),
  `entityId` integer,
  `day` string,
  `startTime` time,
  `endTime` time
);

有什么想法吗？

Answer 1

尝试将所有内容塞到一个通用表中是不值得的。最好是像这样构建每个实体：

CREATE TABLE business
(
  business_id  INT           NOT NULL
 ,name         VARCHAR(255)  NOT NULL
 ,CONSTRAINT PK_business PRIMARY KEY (business_id)
 ,CONSTRAINT AK_business UNIQUE (name)
)
;

CREATE TABLE business_hours_of_operation
(
  business_id  INT              NOT NULL
 ,day_of_week  TINYINT          NOT NULL
 ,start_hour   DECIMAL(4,2)     NOT NULL /* Expressed as fractional - i.e. 12:30 = 12.5 */
 ,hours_open   DECIMAL(5,2)     NOT NULL
 ,CONSTRAINT FK_Hours_Of_Operation_For_Business FOREIGN KEY (business_id) REFERENCES business (business_id)
 ,CONSTRAINT PK_Business_Hours_Of_Operation PRIMARY KEY (business_id, day_of_week, start_hour)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Day_Of_Week_Is_Valid CHECK(day_of_week >= 1 AND day_of_week <= 7)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Start_Hour_Is_Valid CHECK(start_hour >= 0.00 AND start_hour < 24.00)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Hours_Open_Is_Valid CHECK(hours_open > 0.00 AND hours_open <= 168.00)
)
;

每隔一段时间显示所有内容：

SELECT
  business_id
 ,day_of_week AS start_day_of_week
 ,hour AS start_hour
 ,1 + MOD(day_of_week - 1 + FLOOR((hour + hours_open)/24),7) AS end_day_of_week
 ,MOD(hour + hours_open,24) AS end_hour
FROM
  business_hours_of_operation
;

您需要使用存储过程和/或触发器来锁定内容，以确保间隔不重叠和任何其他业务规则。

见Fiddle 这里。