我正在更改开源预订系统以满足雇主的需求。我希望做到这一点,以便编辑或删除服务不一定会修改或删除现有约会引用的服务(例如,编辑服务的价格会留下与更改之前进行的约会列出的相同价格) 。
我目前已经实现了它,存储服务的表使用 MariaDB 的系统版本控制进行版本控制,并且约会存储row_start
服务记录的记录以在稍后创建约会时获取它。约会当前使用服务 id 作为外键,我想将其更改为使用 id 和列的密钥对,row_end
以便约会引用在进行约会或上次编辑约会时存在的服务row_end
。这样,我可以删除或编辑服务表的“当前”版本中的服务记录,而不会影响该服务的现有预约。
基本上我有:
CREATE TABLE `services` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(256) DEFAULT NULL,
`duration` int(11) DEFAULT NULL,
`description` text DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci WITH SYSTEM VERSIONING;
INSERT INTO `services` VALUES
(1, 'Service', 30, 'This is a description', 20.00);
CREATE TABLE `appointments` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`start_datetime` datetime DEFAULT NULL,
`end_datetime` datetime DEFAULT NULL,
`id_services` int(11) DEFAULT NULL,
`service_timestamp` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `appointments` VALUES
(1, '2024-03-19 11:30:00', '2024-03-19 12:00:00', 1, '2038-01-18 21:14:07.999999');
ALTER TABLE appointments
ADD CONSTRAINT `appointments_services`
FOREIGN KEY (`id_services`, `service_timestamp`)
REFERENCES services(`id`, `row_end`)
ON DELETE CASCADE ON UPDATE CASCADE;
我想要的结果是:
UPDATE `services` SET `price`=30.00 WHERE `id` = 1
在约会中:
table : appointments
+-------+---------------------+---------------------+-------------+----------------------------+
| id | start_datetime | end_datetime | id_services | service_timestamp |
+-------+---------------------+---------------------+-------------+----------------------------+
| 1 | 2024-03-19 11:30:00 | 2024-03-19 12:00:00 | 1 | (When the update happened) |
+-------+---------------------+---------------------+-------------+----------------------------+
相反,我得到:
table : appointments
+-------+---------------------+---------------------+-------------+----------------------------+
| id | start_datetime | end_datetime | id_services | service_timestamp |
+-------+---------------------+---------------------+-------------+----------------------------+
| 1 | 2024-03-19 11:30:00 | 2024-03-19 12:00:00 | 1 | 2038-01-18 21:14:07.999999 |
+-------+---------------------+---------------------+-------------+----------------------------+
并尝试在更新破坏外键约束之前将 service_timestamp 更新为服务版本的 row_end 。
我想继续使用 MariaDB 的系统版本控制,因为我认为它远远超过了我自己创建的任何东西,但如果有另一种方法可以记录服务的更改并检索它们的某些版本,我愿意接受建议。
有没有一种方法可以创建一个外键约束来引用整个系统版本控制表而不仅仅是其当前版本,这样当更新或删除服务记录时,外键将引用更新或删除之前的记录?我对数据库不是很有经验,所以欢迎任何建议。
系统版本表是一个审计系统,过去的记录是不可变的。将这些更新为预订系统是错误的解决方案。
MariaDB 拥有的是无重叠的应用程序时间段,这是预订系统的完美模型。
系统版本表仍然可以使用,但作为特定时间预订情况的历史记录。如果这样做,重命名
start_datetime/end_datetime
为以避免与系统版本表发生名称冲突可能会很方便。booking_time_{start,end}
从这个 jira 问题报告和github 存储库中的这些代码行来看,历史行在 FK 约束中被明确忽略,所以我相信使用 MariaDB 的系统版本表不可能实现我想要的行为。
我仍在寻找解决方案,但看到这个问题的目的是看看我是否可以专门使用系统版本控制表来实现快照,我将将此作为答案并找到不同的解决方案,除非有人否则可以找到一种方法来绕过 FK 约束中的历史行检查。编辑:
我能够实现与系统版本控制表类似的行为,同时还能够通过创建单独的“历史”表并在服务表上放置触发器来引用约会中服务行的特定历史版本,如下所示:
这按照我需要的方式工作,但根本不使用系统版本控制。最终,我想将其设置为更新触发器仅存储更改的列以节省空间,并创建一个视图,通过累积到该点的所有更改来在某个时间点重建服务。