From: Rudi Starcevic <rudi@oasis.net.au>
To: pgsql-sql@postgresql.org
Subject: Table Updates ..
Date: Wed, 12 Mar 2003 13:50:49 +1000
Message-ID: <3E6EAE99.8030005@oasis.net.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rudi@oasis.net.au
Subject: Re: Table Updates ..
In-Reply-To: <3E6EAE99.8030005@oasis.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox