Some Advice needed with historical data

From: Alex <alex(at)meerkatsoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Some Advice needed with historical data
Date: 2005-04-14 17:00:14
Message-ID: 425EA19E.9010909@meerkatsoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I need to implement a solution where I need to save changes of a record
and was wondering whats the best way to implement it.

I have 2 tables, A Subscription which holds the original information and
table B Changes which should log the changes. (something like this)

Table A Subscript
-----------------
item_id (key)
customer_id
product_id
attrib_1
attrib_2
....
attrib_5
start_date

Table B Changes
---------------
item_id (fkey)
attrib_1
attrib_2
....
attrib_5
change_date (timestamp)

Here are a few conditions that i must meet.
- Table B can log 0,1 or more change per item_id
- I need to create a view that gives me the Original
record of Table A and the current current values of Table B.

I can think of 2 solutions:

A) I create a stored procedure to return the data when selecting item_id
B) I add a new key to table A) B) (pointer) which points to the latest
update in table B), if key is Null, then no change has been logged yet.

Is there an onther way to implement that? A) seems to be easier but how
about the performance? Can A) be done with a simple view too ?

Any suggestion is appreciated

Thanks
Alex

Browse pgsql-general by date

  From Date Subject
Next Message Cristian Prieto 2005-04-14 19:06:48 Help with process list
Previous Message Kevin MacClay 2005-04-14 15:46:22 Re: pgpool with PostgreSQL 7.4