CODEWITHSUNDEEP
databasedatabase-designrelational-databaserdbms
Dipendra Gurung
Dipendra Gurung

Reputation: 5870

Database design issue: History consistency

I don't know what is the term it used to define this problem, let me explain here with a simple example. I have a products and orders table, with orders having one to many relationship with ordered_products table.

See the tables:-

products(id, sku, name, summary, price);

orders(id, code, customer_name, customer_address);
ordered_products(id, order_id, product_id, quantity);

Once the orders are generated, the ordered products are listed in the ordered_products table. The application then generates an invoice at that instance taking the price reference from the products table. Problem occurs! when the product that has been ordered get edited. For example if name or price is edited on the products table, the orders is affected creating a mismatch in actual orders. What's the solution here? Do I need to copy the values in the ordered_items rather than using a foreign key reference here?

Upvotes: 0

Views: 37

Answers (1)

Ossi
Ossi

Reputation: 63

An order is 'normally' placed at an agreed price, so changing of the product price after the order is placed does mean that you have to store the ordered_price in the ordere_products table.

Upvotes: 1

Related Questions