Thread: Historical Data Question
Can anyone help me with this problem? I have a table in my database that holds information on policies and this table is linked to a number of other tables. I need to be able to keep a history of all changes to a policy over time. The other tables that are linked to policy also need to store historical data. When I run a query on the policy table for a certain period, I also need to be able to pull the correct related rows (i.e. the information that would have been in the table at that time) from the tables linked to it. Does anyone have any suggestions on how to store historical information in databases? Any good resources (books, etc..) that cover this information? Thanks for your time.
On Thu, 14 Jun 2007, Lza wrote: > When I run a query on the policy table for a certain period, I also need > to be able to pull the correct related rows (i.e. the information that > would have been in the table at that time) from the tables linked to it. Check out "Developing Time-Oriented Database Applications in SQL" by Richard Snodgras; it's an entire book devoted to this and related topics. It's out of print and hard to get, but you can download a free PDF copy from the author at http://www.cs.arizona.edu/people/rts/publications.html Much of the text shows how to simulate types and operations that now are built-in to PostgreSQL, like the interval type, so it's not quite as intimidating a read as it seems at first; there's a lot of code for older databases that you can completely ignore. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
am Thu, dem 14.06.2007, um 10:57:43 -0700 mailte Lza folgendes: > Can anyone help me with this problem? > > I have a table in my database that holds information on policies and > this table is linked to a number of other tables. I need to be able to > keep a history of all changes to a policy over time. The other tables > that are linked to policy also need to store historical data. When I > run a query on the policy table for a certain period, I also need to > be able to pull the correct related rows (i.e. the information that > would have been in the table at that time) from the tables linked to > it. > > Does anyone have any suggestions on how to store historical > information in databases? Any good resources (books, etc..) that cover > this information? Maybe this one: http://www.rueping.info/doc/Andreas%20R&ping%20--%202D%20History.pdf Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, 14 Jun 2007, Lza wrote:
> Does anyone have any suggestions on how to store historical
> information in databases?
-- I have tables:
create table history_columns (
column_id smallint primary key,
column_name varchar(63) not null,
table_name varchar(63) not null,
unique (column_name, table_name)
);
create table history (
column_id smallint not null references history_columns,
id int not null,
time_of_change timestamp with time zone not null,
primary key (column_id,id,time_of_change),
user_id smallint not null references users,
value varchar(10000)
);
--------------------------------------------------------
-- Utility function:
create or replace function column_id(column_name varchar(63), table_name varchar(63))
returns smallint
language sql stable strict
as $column_id$
select column_id from history_columns where column_name=$1 and table_name=$2;
$column_id$;
--------------------------------------------------------
-- Every data table is like this:
create table table1 (
table1_id int primary_key,
column1 varchar,
-- ... repeat for every column
id_zmieniajacego_table1 smallint not null references users,
time_of_change_table1 timestamp with time zone not null
)
--------------------------------------------------------
-- An on every table there's a trigger:
create or replace function process_history_table1() returns trigger as
$$
declare
changed boolean;
begin
if (tg_op = 'DELETE') then
insert into history values (
column_id('table1_id','table1'), OLD.table1_id,
current_timestamp,
session_user_id(),
OLD.table1_id );
if (char_length(OLD.column1)>0) then insert into history values (
column_id('column1','table1'), OLD.id_table1,
OLD.time_of_change_table1,OLD.id_zmieniajacego_table1,OLD.column1::text
-- ... repeat for every column
return OLD;
elsif (tg_op = 'UPDATE') then
changed = false;
if (OLD.column1<>NEW.column1) then insert into history values (
column_id('column1','table1'), OLD.id_table1,
OLD.time_of_change_table1,OLD.id_zmieniajacego_table1,OLD.column1::text
-- ... repeat for every column
if (changed) then
NEW.id_zmieniajacego_table1=session_user_id();
NEW.time_of_change_table1=current_timestamp;
return NEW;
else
return null;
end if;
end if;
end;
$$ language plpgsql volatile;
create trigger process_history_table1
before update or delete on table1
for each row execute procedure process_history_table1();
--------------------------------------------------------
When I need to show a table values for $some_id at $some_date in
the past I'll just get actual values and process history table back
in time
select column_name, value from history
where
table_name='table1'
and id=$some_id
and time_of_change>=$some_date
order by time_of_change desc
changing values in relevant columns.
I can show a list of who, when made a change and what has changed
using history table.
I can easily delete/archive history table records older than some
date when I don't need it anymore.
It can be made secure making process_history_* tables "security
definer" and allowing changes to history table only to its owner.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
Lza writes: > I have a table in my database that holds information on policies and > this table is linked to a number of other tables. I need to be able to > keep a history of all changes to a policy over time. The other tables There is a postgresql project which I just saw last night. Don't recall the name, but it was announced in the monthly announcement email. It allows you to save all the changes made to a file and to revert back to a given date. I believe it was in the annoucement email for june.
am Fri, dem 15.06.2007, um 8:21:45 -0400 mailte Francisco Reyes folgendes: > Lza writes: > > >I have a table in my database that holds information on policies and > >this table is linked to a number of other tables. I need to be able to > >keep a history of all changes to a policy over time. The other tables > > There is a postgresql project which I just saw last night. > Don't recall the name, but it was announced in the monthly announcement > email. > > It allows you to save all the changes made to a file and to revert back to > a given date. > I believe it was in the annoucement email for june. Maybe tablelog. http://pgfoundry.org/projects/tablelog/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, 2007-06-14 at 10:57 -0700, Lza wrote:
> Can anyone help me with this problem?
>
> I have a table in my database that holds information on policies and
> this table is linked to a number of other tables. I need to be able to
> keep a history of all changes to a policy over time. The other tables
> that are linked to policy also need to store historical data. When I
> run a query on the policy table for a certain period, I also need to
> be able to pull the correct related rows (i.e. the information that
> would have been in the table at that time) from the tables linked to
> it.
>
> Does anyone have any suggestions on how to store historical
> information in databases? Any good resources (books, etc..) that cover
> this information?
>
I highly recommend _Temporal Data and the Relational Model_ by C.J.
Date, Hugh Darwen, and Nikos Lorentzos.
C.J. Date is my favorite database author, and he is highly respected.
Regards,
Jeff Davis
On Tue, 19 Jun 2007, Jeff Davis wrote:
>> I have a table in my database that holds information on policies and this
>> table is linked to a number of other tables. I need to be able to keep a
>> history of all changes to a policy over time. The other tables that are
>> linked to policy also need to store historical data. When I run a query
>> on the policy table for a certain period, I also need to be able to pull
>> the correct related rows (i.e. the information that would have been in
>> the table at that time) from the tables linked to it.
> I highly recommend _Temporal Data and the Relational Model_ by C.J.
> Date, Hugh Darwen, and Nikos Lorentzos.
Here's another excellent book: "Developing Time-Oriented Databse
Applications in SQL" by Richard T. Snodgrass. If you go to his web page at
the Univ. of Arizona's site, and follow the publications link, you'll arrive
at
<http://www.cs.arizona.edu/~rts/publications.html>
where you can download a pdf of the book for free.
This was recommended to me by Joe Celko and helped me to solve a similar
problem in one of our projects.
We use a Permit_History table, which has as its primary key a pointer to
the permit_nbr (primary key field) in the Permits table. That's equivalent
to the Policies table referenced above.
A separate histories table can track all changes to a policy so that you
can extract the policy at any given date. Both Joe Celko's "SQL for
Smarties, 3rd Ed." and Rick F. van der Lans' "Introduction to SQL, 4th Ed."
are also excellent sources of useful insight into temporal math.
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863