pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Updates ..
3+ messages / 2 participants
[nested] [flat]
* Table Updates ..
@ 2003-03-12 03:50 Rudi Starcevic <rudi@oasis.net.au>
2003-03-12 03:57 ` Re: Table Updates .. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rudi Starcevic @ 2003-03-12 03:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Updates ..
2003-03-12 03:50 Table Updates .. Rudi Starcevic <rudi@oasis.net.au>
@ 2003-03-12 03:57 ` Josh Berkus <josh@agliodbs.com>
2003-03-12 04:07 ` Re: Table Updates .. Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-03-12 03:57 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
Rudi,
> 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.
If you're concerned with "freezing" the product characteristics at the time of
the order, the answer is to add all or most of the columns in the products
table to orders_products. Then, at the time of order finalization
(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
products. But this isnt' required.
This will work better than a monthly log.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Updates ..
2003-03-12 03:50 Table Updates .. Rudi Starcevic <rudi@oasis.net.au>
2003-03-12 03:57 ` Re: Table Updates .. Josh Berkus <josh@agliodbs.com>
@ 2003-03-12 04:07 ` Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 0 replies; 3+ messages in thread
From: Rudi Starcevic @ 2003-03-12 04:07 UTC (permalink / raw)
To: pgsql-sql
Josh,
Thanks for that.
You're suggestion is how I currently have it.
However some other's had doubted my method and suggested the logging
table(s).
Keeping is simple is much better.
Thanks again.
Regards
Rudi.
Josh Berkus wrote:
>Rudi,
>
>
>
>>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.
>>
>>
>
>If you're concerned with "freezing" the product characteristics at the time of
>the order, the answer is to add all or most of the columns in the products
>table to orders_products. Then, at the time of order finalization
>(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
>products. But this isnt' required.
>
>This will work better than a monthly log.
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-12 04:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 03:50 Table Updates .. Rudi Starcevic <rudi@oasis.net.au>
2003-03-12 03:57 ` Josh Berkus <josh@agliodbs.com>
2003-03-12 04:07 ` Rudi Starcevic <rudi@oasis.net.au>
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