[postgis-users] Versioning

[Ben Madin]

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?

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.

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 <jp.alcantara at geo-st.com>:
>> 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 <jp.alcantara at geo-st.com>:
>>>> 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
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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

ben at remoteinformation.com.au



							Out here, it pays to know...