Обсуждение: 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. > > >