Re: Versioning Schema SQL ideas needed

On 1/26/15 4:38 PM, Tim Smith wrote:
> create table templates(
>     template_id int not null primary key,
>     template_groupid int not null,
>     template_version int not null
>     template_text text not null);
>
> My thinking on the above is :
> - template_id is a unique ID for that version
> - template_groupid identifies the set the template belongs to
> - version is the version

I suggest something different that (IMHO) is clearer:

CREATE TABLE template_group(
   template_group_id SERIAL PRIMARY KEY
   , template_group_name text NOT NULL UNIQUE
   ...
);

CREATE TABLE template_history(
   template_history_id SERIAL
   , template_group_id int REFERENCES template_group
   , template_previous_hid int REFERENCES template_history
   , ...
);
CREATE UNIQUE INDEX template_history__u_group_null_previous
   ON template_history( template_group_id )
   WHERE template_previous_hid IS NULL
;
CREATE UNIQUE INDEX template_history__u_group__previous
   ON template_history( template_group_id, template_previous_hid )
   WHERE template_previous_hid IS NOT NULL
;
And then a BEFORE INSERT OR UPDATE trigger that correctly sets
template_previous_hid to whatever the previous history id for that group is.

The reason prefer this way of doing history is it's very hard to screw
up. The previous field always points to the prior record and the two
UNIQUE indexes enforce that it has to be unique. You will want a
function that get you the most recent history ID for a specific group_id
by walking down the chain (which you can do with a CTE).

Note that you can switch previous_hid to next_hid if you want. I
personally don't like that because it means you have to UPDATE the
previous record. I would rather make it so you can't actually update a
history record (since you shouldn't be able to rewrite history unless
you live in a George Orwell world...). The one upside to using next
instead of previous is it's trivial to find the most current record. But
if you're worried about the performance of that, I would just have the
trigger that sets previous_hid also update a template_current table that
is just template_group_id, template_current_history_id.

BTW, when I've actually done this for real I just used 'hid' everywhere
instead of 'history_id'.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com