From: Lena Moretti, finance analyst
Deliverable: a price_history table with July's changes applied, a current_prices view, and two checks
"Our margin reports keep using today's price for last year's sales. I need a proper price history: one row per price with the dates it applied, July's changes recorded without losing the old prices, and a view of current prices for the website team."
July 1, 2025 changes: product 1 to $14.50; then products 2, 5 and 13 to $44.00, $21.00 and $46.50.
How this works: each step starts from the database your previous step left.
This project builds a type 2 history table and maintains it with DML. Each price change is two statements that must stay together: close the current version, then insert the new one. Close first; if you insert first, the closing UPDATE catches the new row too.
The key (product_id, valid_from) stops two versions starting on the same day, and the CHECK keeps prices positive. The last steps use the table the way reports will: prices as of a date, and a check that each product's history makes sense.