Here is a slightly combined version of what Paul says that uses table inheritance to try to achieve the best of both worlds. I think Ben is doing something like that where he keeps the whole history and current version in one table.
1) Create a table called mystuff_all(should have the date fields in it) 2) Create a table called mystuff which inherits from mystuff_all 3) Create a table called mystuff_history which inherits from mystuff_all Use Paul's trigger adding triggers against mystuff to add records into mystuff_history when things change in mystuff -- the full record basically. Those apps that have version awareness can query from mystuff_all and others just use mystuff. Leo -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul Ramsey Sent: Wednesday, May 13, 2009 5:43 PM To: [email protected]; PostGIS Users Discussion Subject: Re: [postgis-users] Versioning 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
