DB with Revision History

README.md

DB with Revision History

First, you have only one table to handle everything, in order to keep the model design and the data integrity at only one point.

This is the basic idea, you can extend the design with the created_by & updated_by columns if you need.

The following implementation is for MySQL, but the idea can be implemented at other kind of SQL databases too.

Cons

• Unique key constrain is not possible, but you can create a Trigger to handle it.
• Update many records at same time (UPDATE ...) is not possible if you want to save the history.
• Delete many records at same time (DELETE ...) is not possible if you want to save the history.

Links

• https://gist.github.com/reduardo7/4ed3dd52a5d8a61a54f20b42b82afa15
• https://stackoverflow.com/a/70596143/717267.

examples.sql

-- Select
SELECT * FROM `vMyTable`;

-- Select with deleted records
SELECT * FROM `vMyTableAll`;

-- Insert / Add / New
INSERT INTO myTable (`title`) VALUES ('Test 1');

-- Update / Edit
INSERT INTO myTable (`id`, `version`, `title`, `description`)
SELECT
    `id`
  , `version` + 1 as `version` -- New version
  , `title`
  , 'New description' AS `description`
  FROM `vMyTable`
  WHERE id = 1;

-- Soft Delete
INSERT INTO myTable (`id`, `version`, `title`, `description`, `deleted_at`)
SELECT
    `id`
  , `version` + 1 as `version` -- New version
  , `title`
  , `description`
  , NOW() AS `deleted_at`
  FROM `vMyTable`
  WHERE id = 1;

-- Restore Soft Deleted record
INSERT INTO myTable (`id`, `version`, `title`, `description`, `deleted_at`)
SELECT
    `id`
  , `version` + 1 as `version` -- New version
  , `title`
  , `description`
  , null AS `deleted_at`
  FROM `vMyTableAll` -- Get with deleted
  WHERE id = 1;

-- Delete record & history
DELETE FROM `myTable` WHERE id = 1;

-- Record history
SELECT *
FROM `myTable`
WHERE id = 1
ORDER BY `version` DESC;

init.sql

DROP TABLE IF EXISTS `myTable`;

CREATE TABLE `myTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `version` int(11) NOT NULL DEFAULT 0 COMMENT 'Version',
  `title` varchar(32) NOT NULL COMMENT 'Title',
  `description` varchar(1024) DEFAULT NULL COMMENT 'Description',
  `deleted_at` datetime DEFAULT NULL COMMENT 'Record deleted at',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record created at'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `myTable`
  ADD PRIMARY KEY (`id`, `version`) USING BTREE,
  ADD KEY `i_title` (`title`);

-- Get current version

CREATE OR REPLACE VIEW vMyTableCurrentVersion AS
SELECT
    `id`
  , MAX(`version`) AS `version`
  , MIN(`created_at`) AS `created_at`
FROM `myTable`
GROUP BY `id`;

-- Get all records (include deleted records)

CREATE OR REPLACE VIEW vMyTableAll AS
SELECT
    T.id
  , T.version

  , T.title
  , T.description

  , T.deleted_at
  , _T.created_at
  , T.created_at AS `updated_at`
FROM
  `myTable` AS T
  INNER JOIN vMyTableCurrentVersion AS _T ON
    T.id = _T.id
    AND T.version = _T.version;

-- Get records

CREATE OR REPLACE VIEW vMyTable AS
SELECT *
FROM `vMyTableAll`
WHERE `deleted_at` IS NULL;

-- Triggers & Validations

DROP PROCEDURE IF EXISTS myTable_uk_title;
DROP TRIGGER IF EXISTS myTable_insert_uk_title;
DROP TRIGGER IF EXISTS myTable_update_uk_title;

DELIMITER //

CREATE PROCEDURE myTable_uk_title(id INT, title VARCHAR(32)) BEGIN
  IF (
    SELECT COUNT(*)
    FROM vMyTable AS T
    WHERE
      T.id <> id
      AND T.title = title
  ) > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Duplicated "title"', MYSQL_ERRNO = 1000;
  END IF;
END //

CREATE TRIGGER myTable_insert_uk_title BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
  CALL myTable_uk_title(NEW.id, NEW.title);
END //

CREATE TRIGGER myTable_update_uk_title BEFORE UPDATE ON myTable
FOR EACH ROW
BEGIN
  CALL myTable_uk_title(NEW.id, NEW.title);
END //

DELIMITER ;