Archiving price changes in SQL

Archiving price changes is probably common function often implemented in web applications. I would like to share solution that satisfied requirements in application we developed.

Let's have module for creating offers from products. Every product has its own price and offers are generated directly from database. So there is not any pdf document file archived for example.

We need to be sure that product prices in offer we created year before (for example), will correspond with prices at date when the offer was created even if we will generate same offer again later. Price changes will be rocorded in separated table.

Basic table for products with some data:

CREATE TABLE `products` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `price` decimal(9,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from products;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | Plate |  4.95 |
|  2 | Bowl  |  3.00 |
|  3 | Vase  |  7.25 |
+----+-------+-------+

Table storing price changes:

CREATE TABLE `products_price_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` mediumint(8) unsigned NOT NULL,
  `price` decimal(9,2) unsigned DEFAULT '0.00',
  `effective_to` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pph_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from products_price_history;
+----+------------+-------+---------------------+
| id | product_id | price | effective_to        |
+----+------------+-------+---------------------+
|  1 |          1 |  4.30 | 2018-12-10 00:00:00 |
|  2 |          1 |  6.00 | 2018-12-20 00:00:00 |
|  3 |          3 |  7.00 | 2018-12-20 00:00:00 |
|  4 |          2 |  3.50 | 2019-01-20 00:00:00 |
+----+------------+-------+---------------------+

On table products we will create trigger capturing price changes on column price. So every price change will be stored in table products_price_history.

DROP TRIGGER IF EXISTS `tg_products_after_update`;

DELIMITER $$
CREATE TRIGGER `tg_products_after_update` AFTER UPDATE ON `products` FOR EACH ROW
BEGIN

IF (OLD.price != NEW.price) THEN
IF(OLD.price > 0) THEN
 INSERT INTO products_price_history (product_id, price)
      VALUES (OLD.id, OLD.price);
END IF;
END IF;

END$$
DELIMITER ;

When we need product price according to a specific date (date when offer was created), we will join table products_price_history. If record does not exists in history for current product than the current price will be returned.

SELECT 
    `title`,
    IFNULL(products_price_history.price, products.price) AS `price`
FROM
    `products` 
  LEFT JOIN `products_price_history` ON products_price_history.id = (
       SELECT 
            `id`
         FROM
            `products_price_history`
        WHERE
            `products_price_history`.`product_id` = `products`.`id`
             AND DATE(`effective_to`) >= '2018-12-18' -- date when offer was created
     ORDER BY `effective_to` ASC LIMIT 1
   )
ORDER BY `title` ASC;

And the result reflects the product prices coresponding to date when offer was created.

+-------+-------+
| title | price |
+-------+-------+
| Bowl  |  3.50 |
| Plate |  6.00 |
| Vase  |  7.00 |
+-------+-------+