Thread: Table Updates ..

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

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 BerkusAglio Database SolutionsSan Francisco

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.
>
>  
>