Re: History

On Saturday 01 February 2003 23:07, will trillich wrote:
> On Sat, Feb 01, 2003 at 06:47:05PM -0600, Alan Gutierrez wrote:
> > On Friday 31 January 2003 05:27, you wrote:
> > > Should I use inherrtance and have a different table for each
> > > table I want history for or one table for the lot?
> > > Table "public.history"
> > > Column | Type | Modifiers
> > > ---------+-----------------------------+-----------
> > > tab | text |
> > > field | text |
> > > action | text |
> > > before | text |
> > > after | text |
> > > occured | timestamp without time zone |
> > > key | text |
> > > who | text |
> >
> > One table for the lot. If you use inheritance, it is my
> > understanding that it will be the same thing, since all the
> > data will be stored in the base table. The only thing you'd
> > remove is the table name.
>
> aha. that clears up a question i had as well. lemme see if i
> understand--
>
> create table delta (
> id serial primary key,
> created date default current_date
> );
>
> create table loc (
> addr varchar(80),
> st varchar(4),
> city varchar(30),
> zip varchar(12),
> nation varchar(3) default 'USA'
> ) inherits ( delta );
>
> insert into loc(addr,city,zip) values
> ('329 Main','Middlegulch','24680');
>
> then when i
>
> select * from delta;
>
> i'll see the id (from loc) and the created date as well? hmm!
> and this way it's ONE sequence for all related tables. i bet
> that's a nice un-cluttering side-effect. plus, the child tables
> would all take up that much LESS space, right? whoa, serious
> paradigm shift in the works... cool!

Shift back. I am not advocating the use of PostgreSQL inheritance. When
I want to model inheritance I do so explicitly.

CREATE TABLE Person
(person_id INTEGER NOT NULL,
first_name VARCHAR(32),
last_name VARCHAR(32) NOT NULL,
PRIMARY KEY (person_id));

CREATE TABLE Worker
(worker_id int NOT NULL REFERENCES (Person),
date_hired DATE NOT NULL,
PRIMARY KEY (worker_id));

> but -- is there some way to tell which offspring table the delta
> record came from? now THAT would be useful.

There is no good way.

You moved the goal posts. I thought you wanted a history table to store
changes per row. How's that coming along?

--
Alan Gutierrez - ajglist(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows