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