Abstract: Make a database which can be store historic modification is often considered a “secondary” activity, but bad design leads to databases that are difficult to optimize and often not very understandable. In this article we illustrate a simple method that respects the dictates of relational theory & is easy to understand. As a plus we will show it on SQLite, a small but powerful database system.
Authors: Ernesto Ghetti (idea), Giovanni Giorgi (Exhibition and demo trigger)
Let’s suppose we have a table called “CUSTOMER” and we want to log any changes made to its fields, for example on a daily basis.
It is usually tempting to add a couple of START & END DATE columns, only to realize that querying this table to find the current data becomes a nightmare, especially if there are other related tables. Another approach is to use a CUSTOMER_HISTORY table, but duplicating all the tables and leading to a cumbersome system of updating a group of related tables.
Lets start with a E-R model like this:
We are going to create a CUSTOMER table with a logical primary key NDG and a physical primary key XD. Then we define a relationship with the same table using XDS (iDentifer hiStory) as a foreign key. Let’s add some data (like customer name) and a couple of dates to register when the record is inserted / updated, as well as the author (editor) of the change in question:
create table CUSTOMER(
xd number not null,
NDG number not null,
xds number,
d_start timestamp,
d_end timestamp,
editor varchar2(40),
name varchar2(80),
Primary key (xd),
CONSTRAINT FK_HISTORY_CUSTOMER
FOREIGN KEY (XDS)
REFERENCES CUSTOMER (XD)
);
-- Forbids different CUSTOMERS with same NDG:
create unique index CUSTOMER_UQ_NDG ON CUSTOMER( case when xd=xds then NDG END );
-- View on active data
create view VCUSTOMER as
select * from CUSTOMER where xd=xds;
Then let’s define a couple of rules:
- The current record is the one with XD = XDS.
It could have END_DATE = null - A historicized record always has XD> XDS and must have defined the END_DATE
- Also, we want to avoid different customers have the same logical primary key, called NDG (line 16 above).
As you see is possible to define logical constraints (3) without much hassle: we used “indexes on expression” available from 2015 on SQLite
Creating a new record is easy:
-- Insert record example (unique id are fixed for simplicity)
INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,ndg,name,editor) values(1 ,1,date('now'),null,100,'Zeno Parisi','BOT');
select * from customer order by XD desc;
+----+-----+-----+------------+-------+--------+-------------+
| xd | NDG | xds | d_start | d_end | editor | name |
+----+-----+-----+------------+-------+--------+-------------+
| 1 | 100 | 1 | 2021-03-31 | | BOT | Zeno Parisi |
+----+-----+-----+------------+-------+--------+-------------+
Now the tricky part: how we update a record?
We use a “shrimp” strategy. First of all we create a copy of the current record, and we let point it to the new one with XD pointer.
-- Copy old record XD > XDS
-- On SQLITE you got a rowid special unique id column for every row: we use it here to make
-- the copy
INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,editor,name,ndg)
select rowid+1 /*trick to get a new one */, XDS,d_start,date('now'),editor,name,ndg
from CUSTOMER where xd=xds and NDG=100;
-- Update XD=XDS
update CUSTOMER set name='Zeno Paris', editor='GG', d_start=date('now') where xd=xds and ndg=100;
Then we update the current record with the new value we want to insert (line 7 above).
The result is below:
-- After update select * from customer order by XD desc; +----+-----+-----+------------+------------+--------+-------------+ | xd | NDG | xds | d_start | d_end | editor | name | +----+-----+-----+------------+------------+--------+-------------+ | 2 | 100 | 1 | 2021-03-31 | 2021-03-31 | BOT | Zeno Parisi | | 1 | 100 | 1 | 2021-03-31 | | GG | Zeno Paris | +----+-----+-----+------------+------------+--------+-------------+
External relation support
This strategy supports external relations too, as you can see in the owned_service table model.
Trigger support
-- New record (see below)
INSERT INTO customer(XD,XDS,d_start,d_end,ndg,name,editor)
select max(rowid)+1,max(rowid)+1, date('now'),null,200,'Scott Tiger','BOT'
from customer;
/* Trigger support for trasparent updates on vcustomer
*
*/
create trigger auto_history_customer_update
instead of update on vcustomer
FOR EACH ROW
begin
INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,editor,name,ndg)
select max(rowid)+1 /*trick to get a new one */, XDS,d_start,date('now'),editor,name,ndg
from CUSTOMER where xd=xds and NDG =OLD.ndg;
update CUSTOMER set name=NEW.name, editor=NEW.editor, d_start=date('now') where xd=xds and xd=OLD.XD;
end;
/* This trigger only manage rowids */
create trigger auto_history_customer_insert
instead of insert on vcustomer
FOR EACH ROW
begin
INSERT INTO CUSTOMER(XD,XDS, d_start, d_end, editor, name, ndg)
select max(rowid)+1,max(rowid)+1, NEW.d_start, NEW.d_end,NEW.editor, NEW.name, NEW.NDG
from customer;
end;
-- Trigger support example
update vcustomer set editor='Changed via trigger' where NDG=200;
-- select * from customer order by xd desc;
select * from vcustomer;
+----+-----+-----+------------+-------+---------------------+-------------+
| xd | NDG | xds | d_start | d_end | editor | name |
+----+-----+-----+------------+-------+---------------------+-------------+
| 1 | 100 | 1 | 2021-03-31 | | GG | Zeno Paris |
| 3 | 200 | 3 | 2021-03-31 | | Changed via trigger | Scott Tiger |
+----+-----+-----+------------+-------+---------------------+-------------+
.echo off
Real table data:
+----+-----+-----+------------+------------+---------------------+-------------+
| xd | NDG | xds | d_start | d_end | editor | name |
+----+-----+-----+------------+------------+---------------------+-------------+
| 1 | 100 | 1 | 2021-03-31 | | GG | Zeno Paris |
| 2 | 100 | 1 | 2021-03-31 | 2021-03-31 | BOT | Zeno Parisi |
| 3 | 200 | 3 | 2021-03-31 | | Changed via trigger | Scott Tiger |
| 4 | 200 | 3 | 2021-03-31 | 2021-03-31 | BOT | Scott Tiger |
+----+-----+-----+------------+------------+---------------------+-------------+