Re: Versioning

On Apr 10, 2007, at 3:18 PM, Sean Davis wrote:

> 4)  Maintain a shadow table with only diffs from the original and
> metadata on
> when the changes took place

Thats what I do.

Table artist
    id serial primary key
    version_id serial
    version_date
    name
unique key artist_id_version on artist( id , version_id );

Table artist_archive
    archive_id serial primary key
    id int references artist(id);
    version_id
    version_date
    name
unique key artist_id_version on artist( id , version_id );

I just record the an archive id in the new table, and bump-up an
internal version id + version date on both.
any other metadata goes into its own transaction_details table.  i
opted for duplicating the version date into those tables because it
is often used and the speed/memory improvement from not joining
offset the disk space.

the only thing worth mentioning, is that this sort of archive is a
PITA to handle unless you enter a record in both tables as record 0.

ie, every new insert puts the full data in both tables.  its possible
to reconstruct information otherwise, but its a headache.




// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
|      FindMeOn.com - The cure for Multiple Web Personality Disorder
|      Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
|      RoadSound.com - Tools For Bands, Stuff For Fans
|      Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -