Use of Subversion to version "Database" Data

[Ken Clark <ke...@snet.net>]

I am looking for a little architectural advice...

We have an upcoming project that is a largely a standard rdbms-type IT 
project -- that is there are a number of screens where users can enter 
data and we need to persist it somewhere.  However, it also contains a 
requirement to support versioning of data -- that is, several screens 
worth of data form a block that needs to be revisioned as a single item, 
and the user can load older versions of the data for review at any time, 
as well as, of course, the "head" data.  There are a lot of complex 
interrelationships between these screens as well as to other 
non-versioned data.

I have considered a couple of solutions:

1) Brute force -- whenever the user asks for a revision, we simply copy 
everything in all related tables to a new set of rows, under a different 
high level key.  This is the simplest to understand, and would not 
require any special persistence code.

2) A more subversion-like approach, where every table that contains 
revisioned data has a couple of columns, one to keep the revision number 
and one to flag whether the data was deleted.  Every user transaction 
will always insert new rows, using a always-increasing sequential 
revision id.  Only data actually changed would be inserted.  To retrieve 
old revisions then, all tables would be searched for the record with the 
highest rev # less than a specified revision and with deleted flag not 
"Y".  To retrieve head data it would just be the highest rev # (not 
deleted).  I can explain this in more detail if needed.  This seems a 
more elegant solution, and would have an added benefit of keeping a 
detailed history of user transactions.  However, the complexity is much 
higher, and would require a custom persistence solution.

I am also wondering, though, if anyone has any advice on how to harness 
the subversion engine itself to handle versioning.  I would prefer to 
keep the data in a RDBMS where it can be easily searched via ad hoc 
means.  We could persist the data to XML files and use subversion to 
version those -- that is do-able but then how would we search across 
various high level objects -- for instance for data matching one 
specific low-level field?

Thanks in advance,
ken clark

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org