morgon:

Your method is fine, but when I need this capability, I find it best to split the history into a separate table. The way I prefer is to replicate the table structure and add a timestamp column to it1 (along with other information you might want to capture).

I do it this way because I tend to think of the primary table as "the current state of things", and in my applications, I find that the current state is more often queried than the history. If your applications deal with history as often as current state, then splitting the data into a separate table may not be a good idea. I prefer the split, though, because it's far too easy to have queries give misleading results when you have to remember to explicitly filter out history. In the cases where I *do* want the history, I'll join the tables.

I do the versioning with triggers so the database can't get itself into an invalid state. (I also apply as many constraints as I can to the database to keep it internally consistent. It's much easier to modify the database structure to accomodate new requests than it is to remove crap from a database once it gets in there.) To prevent the vendor lock-in, keep your triggers trivial, so they can be translated simply. (In fact, if you keep the structure the same, you can automate the translation.) So you might have something like the following (I'm using MS SQL instead of Oracle, as I haven't used Oracle since 42 BC):

create table foobar (
   foo int primary key,
   bar varchar(5),
)

create table foobar_hist (
   foo int,
   bar varchar(5),
   change  char(1),   -- Type of change
   changed datetime,  -- when change occurred
   constraint PK_foobar_hist primary key (foo, changed)
)

/* MS-SQL triggers provide two virtual tables:
 *   INSERTED - holds records to add to the table, and 
 *   DELETED - holds records to delete from the table
 * An update is treated as an insert and delete at the
 * same time, so you have access to the before *and*
 * after values of the columns.
 */
create trigger foobar_ins on foobar for insert as
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'I', getdate()
   from INSERTED

create trigger foobar_upd on foobar for update as
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'U', getdate()
   from INSERTED

create trigger foobar_del on foobar for delete as
   -- Note: Final record is duplicated in history table the
   -- last I or U record and this D record have the same
   -- values other than the change/changed columns.
   insert foobar_hist (foo, bar, change, changed)
   select foo, bar, 'D', getdate()
   from DELETED
[download]

As you can see, I try not to put any rocket science in those triggers.

Advantages are:

1. Your primary table and indexes stay as small as possible
2. Your queries for current information remain simple

Disadvantages are:

1. More database objects to maintain

I hope you find this useful...

...roboticus

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.