Some Advice needed with historical data

[Alex <alex@xxxxxxxxxxxxxxx>]

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




















---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx