Robin Wang Robin Wang - 1 month ago 7
MySQL Question

DB table design for maintaining dynamic price of a product in MIS

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

PRODUCT_PRICE
to maintain the price whose DDL is simply shown as below.


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'


Note that there is no
end_date
in DDL because it is exactly the day before
start_date
of the next record or infinite if there is no next record.

In the system, every time when some people create or edit the price of some product in system, information will be sent to admin to review and audit. The change will not take effect unless the admin accept it. A
PRODUCT_PRICE
that passed the review and audit can be edited again.

The problem is that in our old design, the certain record of
PRODUCT_PRICE
will be immediately changed, with
status
field flipped into 0 to wait for review and audit. But that is not what customers want.

The new requirement is that people can still view the price records that passed review after someone edits it unless the new
price
or
start_date
is accepted by admin.

How should we refactor the old table design to implement the new requirements that the change of a certain record will not immediately take effect unless the change is accepted by admin.

After discussion, we figured out 2 solutions.


  • Every time when someone changes a
    PRODUCT_PRICE
    record in system, a row that contains the new information will be created, and sent for audit. After it is accepted, the old one would be deleted. Hence, a new column
    reference_id
    should be added to the table to mark a certain old record for change.

  • Create a new table
    PRODUCT_PRICE_TEMP
    to store all new price records waiting for audit. After accepted, update the certain old record in
    PRODUCT_PRICE
    . This solution also need a column
    reference_id
    to refer a certain row in
    PRODUCT_PRICE
    , but we don't need to delete records in
    PRODUCT_PRICE
    for changing value(just update).



Is there a better design to our new requirement?

Answer

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.

Comments