Then you should be able to get by with a pretty simple system. For each table you want to retain history for, add a second table, table_history that adds two columns (date_added, date_deleted). The put some insert/update/delete triggers on your main tables. - on insert of row, simultaneously insert a copy into the _history table with an appropriate date_added - on delete of row update the date_deleted field in the equivalent _history table row - on update of row, flag the current copy of the row as deleted, and add a new copy of the row with the current date as the added date
Now a snapshot of "historical state" as of date_query is as simple as querying the history table for "date_added < date_query and (date_deleted > date_query or date_deleted is null)" The thrifty among us will say "hey, I can just have one table with date_added and date_deleted columns" and "current state is just all records with "date_deleted is null". However, your table will grow over time, and eventually things will get slower than you would like. Having a "working table" that is just current also removes the need for client applications to understand the "history concept", they can just work on the table as normal and history is maintained for them transparently. P. 2009/5/13 Juan Pedro Pérez Alcántara <[email protected]>: > Sure, you right. I'm refering versioning in it's first meaning, that is, > history tracking. > > Thanks, > > Juan Pedro Pérez Alcántara > > On Wed, 2009-05-13 at 12:33 -0700, Paul Ramsey wrote: >> Juan Pedro, >> >> What do you mean by "versioning"? >> >> History tracking? (Every state of the data is one version, but there >> is only one branch of the data.) >> Branching and merging? (People can take a copy of the data (a version) >> and work on it, then merge it back into the main database.) >> >> Versioning is an overloaded word. Describe your actual use case. >> >> P. >> >> 2009/5/13 Juan Pedro Pérez Alcántara <[email protected]>: >> > Hello all, >> > >> > soon we will face a project which will need a really strong > versioning >> > mechanism, including geometry. I'm sure anybody here have already >> > addressed this sort of problem. I wonder if you can drop a line or > two >> > about this issue, for I consider it hard to implement. Sure the >> > solutions will vary wildly depending on the scenario, the data, the >> > versioning schedule, etc., but I'll be glad to hear about your >> > experiences in that field. >> > >> > Greetings, >> > >> > Juan Pedro Pérez Alcántara >> > >> > >> > _______________________________________________ >> > postgis-users mailing list >> > [email protected] >> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> > >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
