Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Rudi Starcevic <rudi@oasis.net.au>
Subject: Re: Table Updates ..
Date: Tue, 11 Mar 2003 19:57:23 -0800
References: <3E6EAE99.8030005@oasis.net.au>
In-Reply-To: <3E6EAE99.8030005@oasis.net.au>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303111957.23061.josh@agliodbs.com>


Rudi,

> If I have 3 tables :
>=20
> orders,
> products,
> orders_products ( join table for orders and products )
>=20
> If I insert an order with two products I have:
>=20
> 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 )
>=20
> So far so good. All that works well.
> But what if next week one the the products in the products table is=20
> updated and changed ?
> Then my order from last week is now different -- corrupted. It links to=
=20
> the same product_id but the product is changed.

If you're concerned with "freezing" the product characteristics at the time=
 of=20
the order, the answer is to add all or most of the columns in the products=
=20
table to orders_products.   Then, at the time of order finalization=20
(shipping? not sure) you copy those columns from products to order_products.

If you're into ORDMBS, you could even create orders_products as inheriting=
=20
products.  But this isnt' required.

This will work better than a monthly log.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco