Thread: Versioning

I can think of several ways of "versioning" objects (modeled as rows of a
table).

1)  parent-child based model, where each edit of a row results in a child row
2)  date-based model, where each row is stored with a date and no updates are
performed, only inserts
3)  Maintain a shadow table with "old" versions of a row
4)  Maintain a shadow table with only diffs from the original and metadata on
when the changes took place
5)  Other?

Has anyone suggestions on what might work "best"?  The "rows" here are going
to be simple text fields with a little metadata.

Thanks,
Sean

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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -