Message-ID: <3E6EAE99.8030005@oasis.net.au>
Date: Wed, 12 Mar 2003 13:50:49 +1000
From: Rudi Starcevic <rudi@oasis.net.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Table Updates ..
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

If I have 3 tables :

orders,
products,
orders_products ( join table for orders and products )

If I insert an order with two products I have:

2 rows in the products table, ( the two products )
1 row in the orders table and ( one order )
2 rows in the orders_products table. ( two products for this order )

So far so good. All that works well.
But what if next week one the the products in the products table is 
updated and changed ?
Then my order from last week is now different -- corrupted. It links to 
the same product_id but the product is changed.

What I think may be the correct thing to do is right a function and 
trigger to log changes to the products table -- products_log ( a fourth 
table )
This means :
a) when a user is looking at current products the products table is used.
b) when an order is processed the orders_products table does not uses a 
foreign key from the products table but a
foreign key from the products_log table.

This means that even if the products table products change all processed 
orders still link to the product description * as it was at the time of 
order *.
I hope that makes sense. It does to me.

Does that make sense to you and do you approve of this method ?

Thanks for your time.
Kind Regards
Rudi Starcevic