我正在开发一个基于 SQL 数据库的预订系统(对特定数据库没有限制)。
架构:
工作流程:
现在,我正在使用一个特殊的“实际预订”视图,该视图基于自定义分组和筛选逻辑来识别实际放置的预订。
架构:
![enter image description here](https://istack.dev59.com/f8P7Y.webp)
![enter image description here](https://istack.dev59.com/Yaea7.webp)
现在,我正在使用一个特殊的“实际预订”视图,该视图基于自定义分组和筛选逻辑来识别实际放置的预订。
CREATE VIEW `reserv-io`.`actual_reserved_resources` AS
SELECT
`r`.`id`,
`a`.`resource_id`,
`a`.`type_id`,
`a`.`status_id`,
`a`.`reservation_start`,
`a`.`reservation_end`
FROM `reserv-io`.`actions` `a`
JOIN (SELECT
`r`.`id` `id`,
MAX(`a`.`id`) `action_id`
FROM `reserv-io`.`reservations` `r`
JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
WHERE (`r`.`id` NOT IN
(SELECT `r`.`id` `id`
FROM `reserv-io`.`reservations` `r`
JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
WHERE (`a`.`status_id` IN
(SELECT `id`
FROM `reserv-io`.`reservation_statuses`
WHERE `name` = 'Canceled')))
AND `a`.`status_id` IN
(SELECT `id`
FROM `reserv-io`.`reservation_statuses`
WHERE `name` = 'Approved' OR `name` = 'Accepted'))
GROUP BY `r`.`id`) `b`
ON `a`.`id` = `b`.`action_id`
JOIN `reserv-io`.`reservations` `r` ON `r`.`id` = `a`.`reservation_id`;
通过以下步骤确定是否存在与其他预订重叠的情况:
CREATE PROCEDURE HAS_OVERLAPPING_RESERVATION_WITH_TYPE_SELF_CHECK(
IN RESERVATION BIGINT,
IN RESOURCE INT,
IN RESERVATION_TYPE INT,
IN STARTS_AT DATETIME,
IN ENDS_AT DATETIME,
OUT RESULT BIT)
BEGIN
SELECT CASE WHEN EXISTS(
SELECT *
FROM actual_reserved_resources r
WHERE r.resource_id = RESOURCE
AND r.type_id = RESERVATION_TYPE
AND r.reservation_start < ENDS_AT
AND r.reservation_end > STARTS_AT
AND r.id <> RESERVATION)
THEN TRUE
ELSE FALSE END
INTO RESULT;
END$$
我已经在“actions”表上建立了良好的索引,但我不确定我的方法是将所有与预订相关的操作放在单个表中是否真的可扩展。我听说过一种方法,即将所有待处理和实际批准的预订存储到单独的表中,并手动将它们同步到事务中。
我的问题是,从您的角度来看,哪种方法在数据库性能、可扩展性和良好的数据设计方面更好?