Temporal database design, with a twist (live vs draft rows)

database-design

Not sure if/how I should be cross-posting this… But in case some follow DBA but not SO…

Is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

(Full details on the SO thread).

I'm looking into implementing object-versioning with the added twist of needing to have both live and draft objects, and could use the insights from someone experience in this, as I'm beginning to wonder if it's even possible without potentially horrific hacks.

I'll break it down to posts with tags for the sake of the example, but my use-case is a bit more general (involving slowly changing dimensions – http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Suppose you've a posts table, a tags table, and a post2tag table:

posts (
 id
)

tags (
 id
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id)
)

I'm in need of a couple of things:

Being able to show exactly how a post looked like at an arbitrary datetime, including for deleted rows.
Keep track of who is editing what, for a complete audit trail.
Needs a set of materialized views ("live" tables) for the sake of keeping referential integrity (i.e. logging should be transparent to the developers).
Needs to be appropriately fast for live and the latest draft rows.
Being able to have a draft post coexist with a live post.

I've been investigating various options. So far, the best I've come up with (without points #4/#5) looks a bit like the SCD type6-hybrid setup, but instead of having a current boolean there's a materialized view for the current row. For all intents and purposes, it looks like this:

posts (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

post_revs (
 id,
 rev pkey,
 public,
 created_at,
 created_by,
 deleted_at
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)


tag_revs (
 id,
 public,
 rev pkey,
 created_at,
 created_by,
 deleted_at
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by
)

post2tag_revs (
 post_id,
 tag_id,
 post_rev fkey post_revs(rev), -- the rev when the relation started
 tag_rev fkey tag_revs(rev), -- the rev when the relation started
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_rev, tag_rev)
)

I'm using pg_temporal to maintain indexes on period(created_at, deleted_at). And I keep the various tables in sync using triggers. Yada yada yada… I created the triggers that allow to cancel an edit to posts/tags in such a way that the draft gets stored into the revs without being published. It works great.

Except when I need to worry about draft-row related relations on post2tag. In that case, all hell breaks loose, and this hints to me that I've some kind of design problem in there. But I'm running out of ideas…

I've considered introducing data duplication (i.e. n post2tag rows introduced for each draft revision). This kind of works, but tends to be a lot slower than I'd like it to be.

I've considered introducing drafts tables for the "last draft", but this quickly tends to become very very ugly.

I've considered all sorts of flags…

So question: is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

Best Answer

Cross-posting the answer as well:

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)


tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)

Related Solutions

Mysql – How to implement Draft functionality, with attached files

For the drafts side, what we do (LedgerSMB. PostgreSQL) is use a bool "approved" column or some approval information (where NULL means draft, for example "approved_by" or "approved_at").

I don't know the quirks of MySQL check constraints too well, but in PostgreSQL you can condition check constraints on the value in a field, something like: CHECK (approved is not true or title is not null). This makes it impossible to approve something that has not had everything filled on. We don't do this generally because approval is supposed to be all or nothing, but there are a few areas where something like this is done.

Help with database design

You could always do something like this:

Create a users table to store all of your user info:

create table users
(
  id int, 
  user_name varchar(50)
);

insert into users values
(1, 'John'),
(2, 'Jill'),
(3, 'Bob'),
(4, 'Sam');

You state that you might need to apply this type of tracking for multiple applications. So you can create an application table to store the app name and any details.

create table applications 
(
  id int, 
  app_name varchar(50)
);

insert into applications values
(1, 'App 1'),
(2, 'App 2'),
(3, 'App 3');

Next create a categories table to store the info on each of the categories that you want to track by the users. This table will include a column that will contain the application id. This table will contain a foreign key relationship to the applications table. This allows you to have some flexibility in having the same category in multiple apps.

create table categories 
(
  id int,  
  cat_name varchar(100),
  a_id int 
);

insert into categories values
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 3', 2),
(4, 'Cat 4', 3);

Finally, create the users_categories table which will contain the stats that you want to track, including user, category and then the action_date (the date the event took place).

create table users_categories
(
  u_id int,
  c_id int,
  action_date datetime
);
insert into users_categories values
(1, 1, current_timestamp()),
(2, 3, '2012-01-15 10:15'),
(3, 4, '2012-08-24 12:48'),
(1, 2, '2012-06-08 08:50');

Then to query the data, you would use something similar to this:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join applications a
  on c.a_id = a.id

And the results would contain (See SQL Fiddle with Demo):

ID | USER_NAME | U_ID   | C_ID   | ACTION_DATE           | CAT_NAME | A_ID    | APP_NAME
------------------------------------------------------------------------------------------
1  | John      | 1      | 1      | '2012-09-28 06:47:53' | Cat 1    | 1       | App 1
1  | John      | 1      | 2      | '2012-06-08 08:50:00' | Cat 2    | 1       | App 1
2  | Jill      | 2      | 3      | '2012-01-15 10:15:00' | Cat 3    | 2       | App 2
3  | Bob       | 3      | 4      | '2012-08-24 12:48:00' | Cat 4    | 3       | App 3
4  | Sam       | (null) | (null) | (null)                | (null)   | (null)  | (null)

A few other suggestions, you could technically remove the a_id from the categories table and create a join table between categories and applications, similar to this:

create table categories_applications
(
  c_id int,
  a_id int
);
insert into categories_applications values
(1, 1),
(2, 3),
(3, 1),
(1, 2);

This would lead to one additional join in your queries, but the results would be the same:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join categories_applications ca
  on c.id = ca.c_id
left join applications a
  on ca.a_id = a.id

Best Answer

Related Solutions

Mysql – How to implement Draft functionality, with attached files

Help with database design

Related Question