On Wed, May 13, 2009 at 7:11 PM, Ben Madin <[email protected]> wrote: > Thanks Paul, > > This certainly looks like a more efficient system for 'versioning'... are > you suggesting the history table also has a copy of the 'current' record?
Yes, that's what I'm suggesting. You do all your work against yourtable, but the triggers maintain a fully versioned copy in yourtable_history > Alternatively, is there likely to be much of a performance hit if I create a > view unioning the 'current' table and the 'history' table to be able to > achieve this for reporting purposes. There's no disadvantage to having your _history table just as current as your working table. (Well, I guess size.) I think Leo's approach avoid that, but probably has the performance penalty associated with putting things back together on the fly. P > cheers > > Ben > > > > > On 14/05/2009, at 5:43 AM, Paul Ramsey wrote: > >> 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 > > -- > > Ben Madin > REMOTE INFORMATION > > t : +61 8 9192 5455 > f : +61 8 9192 5535 > m : 0448 887 220 > Broome WA 6725 > > [email protected] > > > > Out here, it pays to > know... > > > _______________________________________________ > 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
