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