We are coding a MIS for customers. The price of a particular product changes often, customers need to maintain price and date period during which the price is effective. There is a table named
CREATE TABLE `PRODUCT_PRICE` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`product_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'product id',
`price` bigint(20) NOT NULL DEFAULT 0 COMMENT 'price value in cent',
`start_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when this price takes effect',
`del_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'mark if logically deleted',
`status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 new, 1 wait for audit, 2 accepted, 3 rejected',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='product price table'
Your first solution is almost it.
Try to look at the problem from birds view, abstract from db model. Each time price changes system creates new instance of product price item. It is a change from user perspective, for system it is a new entity. Seems like you already figured it out.
What is more important - let me warn you from doing this: "the old one would be deleted". I believe the assumption behind is that product price start date can be in the past. It is very dangerous and it would cause terrible complications later. Change of the price in the past must be a rare exception, not a normal situation.
Of course is still happens because people do mistakes. For this critical (and rare!) situation you need to give one or two senior people (or admin/support) ability to change the price in the past without any approvals. Probably directly in the DB. There is just no time for it, company is loosing money every second. Usually, the same person will later take care of all the consequences of the wrong pricing on customers and bills. Even in this situation you should not edit row in the product_price, mark the old one as invalid and create a new row starting in the past.