Adding a Version / History to a Database Table

Question

I am working on a project where I need to add a data version control form or a history function for each table. Basically, we need to track every insert or change in the database, so it's easy to roll back or view previous versions of the data in each table.

As my project manager suggests that this needs to be done is to add some new columns for each table. The main feature is a coloumn called "version". Every time an update occurs, nothing is updated, the old row remains, but a new row is added to the table with an added value for the "version".

To show the current data, we simply use a view that shows only the rows with the highest version number of each type.

Despite the fact that this works great when you move back and forth between different versions, I ran into the problem of this approach. For any relationship between tables, we need to define foreign keys, and foreign keys can only refer to unique fields in another table. Now, when we save several versions of the same row (with the same "Identifier", since this is basically the same piece of data as our application), we can no longer use another table "Identifier" as a foreign key for the table.

We use a unique primary key field for each row, but this is useless as an identifier, since multiple rows are basically different versions of the same thing. We could manually track the latest version of each type of record and update the corresponding relationship with the foreign key every time something changes, but it looks like a lot of work, and I'm not sure it will always work (for example, reverting to the previous version of the record may cause the foreign key to refer to an old and unusable version of another record in another table.)

I know that there are other ways to store the history of database updates (for example, using a separate history table for each table), but I take this approach in this project. Is there an even more obvious way of handling relationships between tables like this that I am missing?

Note. I am using MS SQL Server 2008 R2.

Answer 1

There is a good article about the version for MySQL: https://web.archive.org/web/20171216185737/http://blog.jasny.net/articles/versioning-mysql-data/

I think the basics can be easily applied to any other system.

Answer 2

You say you don’t want a “separate revision table” without voting for the FractalizeR solution, because it is. Well, here is a “one table solution” ... But please simplify / summarize your question in order to better answer and better use this page for all visitors. I think your problem is with version control in SQL tables.

The solution is for "ISO 2008 SQL", then I think it is also for Microsoft SQL-Server. I tested it on PostgreSQL 9.1.

In this problem, we can use SQL View to "emulate" the original table, and the "version of the table" as a new one, with a large number of attributes: * New moment attribute for sorting (organizing) changes and for registering time; * New cmd attribute for "traceability" (optional).

Suppose your original (and regular) table is t . For version control, you must add new attributes, but other programmers do not need to see these new attributes ... The solution is to rename table t to t_hist and offer other SQL programmers VIEW t (as a query through t_hist ).

t is a VIEW for displaying a regular table: only "current tuples". t_hist is a new table with "historical tuples."

Suppose t with attributes a, b. PS: on t_hist I added isTop for better performance on t .

  -- .... CREATE TABLE t_hist ( -- the old attributes for t: id integer NOT NULL, -- a primary key of t a varchar(10), -- any attribute b integer, -- any attribute -- new attributes for revision control: isTop BOOLEAN NOT NULL DEFAULT true, -- "last version" or "top" indicator cmd varchar(60) DEFAULT 'INSERT', -- for traceability moment timestamp NOT NULL DEFAULT now(), -- for sort revisions UNIQUE(id,moment) ); CREATE VIEW t AS SELECT id,a,b FROM t_hist WHERE isTop; -- same, but better performance, as -- SELECT id,a,b FROM t_hist GROUP BY id,a,b HAVING MAX(moment)=moment -- Verifies consistency in INSERT: CREATE FUNCTION t_hist_uniq_trig() RETURNS TRIGGER AS $$ DECLARE aux BOOLEAN; BEGIN SELECT true INTO aux FROM t_hist WHERE id=NEW.id AND moment>=NEW.moment; IF found THEN -- want removes from top? RAISE EXCEPTION 'TRYING TO INCLUDE (ID=%) PREVIOUS TO %', NEW.id, NEW.moment; END IF; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER uniq_trigs BEFORE INSERT ON t_hist FOR EACH ROW EXECUTE PROCEDURE t_hist_uniq_trig(); CREATE FUNCTION t_reset_top(integer) RETURNS BOOLEAN AS $BODY$ UPDATE t_hist SET isTop=false WHERE isTop=true AND id=$1 RETURNING true; -- null se nao encontrado $BODY$ LANGUAGE sql; -------- -- Implements INSER/UPDATE/DELETE over VIEW t, -- and controls unique id of t: CREATE OR REPLACE FUNCTION t_cmd_trig() RETURNS TRIGGER AS $$ DECLARE aux BOOLEAN; BEGIN aux:=true; IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN aux := t_reset_top(OLD.id); -- rets. true ou NULL ELSE SELECT true INTO aux FROM t_hist WHERE id=NEW.id AND isTop; END IF; IF (TG_OP='INSERT' AND aux IS NULL) OR (TG_OP='UPDATE' AND aux) THEN INSERT INTO t_hist (id,a,b,cmd) VALUES (NEW.id, NEW.a,NEW.b,TG_OP); ELSEIF TG_OP='DELETE' AND aux THEN -- if first delete UPDATE t_hist SET cmd=cmd||' AND DELETE AT '||now() ELSEIF TG_OP='INSERT' THEN -- fails by not-unique(id) RAISE EXCEPTION 'REGISTER ID=% EXIST', NEW.id; ELSEIF TG_OP='UPDATE' THEN -- .. redundance, a trigger not goes here RAISE EXCEPTION 'REGISTER ID=% NOT EXIST', NEW.id; END IF; RETURN NEW; -- discarded END $$ LANGUAGE plpgsql; CREATE TRIGGER ins_trigs INSTEAD OF INSERT OR UPDATE OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE t_cmd_trig(); -- Examples: INSERT INTO t(id,a,b) VALUES (1,'aaaaaa',3); -- ok INSERT INTO t(id,a,b) VALUES (1,'bbbbbb',3); -- error UPDATE t_hist SET a='teste' WHERE id=1; -- ok -- SELECT * from t; SELECT * from t_hist; INSERT INTO t(id,a,b) VALUES (2,'bbbbbb',22), -- ok (3,'bbbbbb',22), -- ok (4,'aaaaaa',2); -- ok DELETE FROM t WHERE id=3; -- SELECT * from t; SELECT * from t_hist; 

PS: I suggest not trying to adapt this solution for one table without presentation, your trigger will be very complicated; do not try to adapt for t_hist inherit t , where all content inserted into t_hist will be copied to t .