1

I'm altering an open-source booking system to match my employer's needs. I'd like to make it so that editing or deleting a service does not necessarily modify or delete the service that is referenced by existing appointments (e.g. editing the price of a service leaves the same price listed for appointments that were made before the change).

I've currently implemented it such that the table storing the service is versioned using MariaDB's system versioning, and appointments store the row_start of the service record to fetch it as it was at appointment creation later. The appointments currently use the service id as the foreign key, and I would like to change that to a key pair using the id and the row_end columns such that the appointment references the service as it existed when the appointment was made or last edited using row_end instead. That way I can delete or edit service records in the "present" version of the services table without impacting existing appointments for that service.

Basically I have:

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;

And I would like the result of:

UPDATE `services` SET `price`=30.00 WHERE `id` = 1

In appointments to be:

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

Instead I get:

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 |
+-------+---------------------+---------------------+-------------+----------------------------+

And attempting to update service_timestamp to the row_end of the version of the service before the update breaks the foreign key constraint.

I would like to keep using MariaDB's system versioning as I think it far surpasses anything I could create myself, but if there is another way I could record changes to the services and retrieve certain versions of them, I am open to suggestion.

Is there a way I can make a foreign key constraint that references the entire system-versioned table rather than just its current version, such that when a service record is updated or deleted, the foreign key references the record before it was updated or deleted? I'm not super experienced with databases so any advice is welcome.

7
  • Provide complete reproduceable example according to Tips for asking a good Structured Query Language (SQL) question, #5.
    – Akina
    Commented Mar 20 at 5:03
  • @Akina Thanks for the advice, I tried to edit my question to better follow the tips listed there, so hopefully its easier to follow and understand! Commented Mar 20 at 18:46
  • .. REFERENCES services(`id`, `row_end`) .. ??? I do not see the column row_end in services.
    – Akina
    Commented Mar 20 at 19:06
  • The column row_end is added by MariaDB when using System-Versioning. link But I wonder if defining them explicitly will have different results, let me try. Commented Mar 20 at 19:10
  • Explicitly defining row_start and row_end columns and sadly does not change the behavior. Commented Mar 20 at 19:22

2 Answers 2

0

System version tables is an audit system where past records are immutable. Is the wrong solution to update these as a booking system.

What MariaDB does have is Application Time Periods without Overlaps which is the perfect model for a booking system.

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,
  PERIOD FOR p(start_datetime,send_datetime),
  UNIQUE (id_services, p WITHOUT OVERLAPS)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

System version tables can still be used, but as an historical record of what booking looked like at a particular time. It might be handy to rename start_datetime/end_datetime to booking_time_{start,end} to avoid name conflicts with system version tables if you do.

3
  • The issue I am facing is that I would like to retain information about the service when it is edited or deleted so that existing appointments won't be affected. The appointments otherwise already function as desired, giving them a unique start and end would cause issues as appointments can be at the same datetime depending on the service (services can have multiple seats and different providers). However, I think I can use this system to emulate system-versioned tables without actually using system-versioning, which would be perfect! I just need to figure out the triggers. Commented Mar 21 at 18:33
  • The uniqueness is combined with id_services, so putting id_service, id_seat in the same table and unique criteria will work, disallowing overlaps only when the other unique fields are common.
    – danblack
    Commented Mar 21 at 21:39
  • This would work, but the behavior for appointments is already handled by the existing software I'm extending. My goal is to snapshot the old version of a row in services when it is updated or deleted such that appointments refer to the service as it was at the appointment's creation. Commented Mar 21 at 22:31
0

From this jira issue report and these lines of code in the github repo it looks like historical rows are explicitly ignored in FK constraints, so I believe the behavior I desire is impossible using MariaDB's system-versioned tables.

I'm still looking for a solution but seeing as the intention of this question was to see if I could implement snapshotting using the system-versioned tables specifically, I'm going to leave this as the answer and find a different solution, unless someone else can find a way around the historic row check in the FK constraint.

EDIT:

I was able to achieve similar behavior to the system-versioned table while also being able to reference a specific historic version of the service row in the appointment by creating a separate "history" table, and putting triggers on the service table like so:

CREATE TABLE `services_history` (
  `id` int(11) NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `rs` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
  `re` timestamp(6) NOT NULL DEFAULT from_unixtime(2147483647.999999),
   PRIMARY KEY (`id`,`re`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

DELIMITER $$
CREATE TRIGGER `services_delete` BEFORE DELETE ON `services` 
FOR EACH ROW 
UPDATE services_history
    SET re = CURRENT_TIMESTAMP
    WHERE id = OLD.id AND re = DEFAULT(re)
$$
CREATE TRIGGER `services_insert` AFTER INSERT ON `services` 
FOR EACH ROW 
INSERT INTO services_history(`id`, `name`, `duration`, `description`, `price`)
VALUES (NEW.id, NEW.name, NEW.duration, NEW.description, NEW.price)
$$
CREATE TRIGGER `services_update` BEFORE UPDATE ON `services` 
FOR EACH ROW BEGIN
UPDATE services_history
    SET re = CURRENT_TIMESTAMP
    WHERE id = NEW.id AND re = DEFAULT(re);

INSERT INTO services_history(`id`, `name`, `duration`, `description`, `price`)
VALUES (NEW.id, NEW.name, NEW.duration, NEW.description, NEW.price);
END
$$
DELIMITER ;

This works the way I need it to, but doesn't use system versioning at all. Eventually, I would like to set it up such that the update trigger only stores the columns that changed to save on space, and create a view that rebuilds the service at a point in time by accumulating all the changes up to that point.

2
  • The jira issue report was fixed a long time ago, and the code section referenced is making sure an inserted FK value isn't considering a historical reference as a current constraint.
    – danblack
    Commented Mar 21 at 21:42
  • Yes, the jira issue is fixed, which resulted in FK values not considering historical references as a current constraint, but that is the behavior I want. I want to consider historical references in the constraint. I want the old "broken" behavior but the fix seems to be unconditional. Commented Mar 21 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.