hard · ~12 min
Billing needs historical order totals calculated using the catalog price that was actually in force when each order was placed.
price_history records every price update for a product along with the timestamp effective_from when that price took effect.
price_history
effective_from
orders
unit_price
product_id
order_time
order_total = ROUND(unit_price * quantity, 2)
order_id
order_total
Input contract: order_id is unique and non-null in orders; (product_id, effective_from) is unique and non-null in price_history; all columns are non-null. Every order has at least one price entry effective at or before its order_time, and products can receive later price updates after earlier orders.
(product_id, effective_from)