Thread: Historical Data Question

Historical Data Question

From
Lza
Date:
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.


Re: Historical Data Question

From
Greg Smith
Date:
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

Re: Historical Data Question

From
"A. Kretschmer"
Date:
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

Re: Historical Data Question

From
Tomasz Ostrowski
Date:
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

Re: Historical Data Question

From
Francisco Reyes
Date:
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.

Re: Historical Data Question

From
"A. Kretschmer"
Date:
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

Re: Historical Data Question

From
Jeff Davis
Date:
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


Re: Historical Data Question

From
Rich Shepard
Date:
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