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