Changes of product price in database design

Question

I have some issues about my POS database design, supposed I have products table with column id, product_name, price, and stocks, and then table transactions, and transaction_details if someone bought some products I have one transaction record and some transaction detail record, for now I copy value of price from product table into transaction_details, so if the product price is changed, they can't affect the transaction history/report, but I consider separate prices into another table, let's say product_prices, each product have many prices, and the transaction_details related with product_prices instead direct product itself. Is my approach better or worse correspond data integrity, performance or efficiency about data itself. and I have stock in products table, is it needed to or I just fetch from purchasing transaction subtract unit_price from transaction_details. Thank you for your answers.

-- -----------------------------------------------------
-- Table `mydb`.`transaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`transaction` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  `total` DOUBLE UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(1000) NULL,
  `price` DOUBLE UNSIGNED NOT NULL,
  `stocks` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`transaction_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`transaction_details` (
  `transaction_id` INT NOT NULL,
  `products_id` INT NOT NULL,
  `discount` DOUBLE NOT NULL,
  `unit_price` DOUBLE NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`, `products_id`),
  INDEX `fk_transaction_details_products1_idx` (`products_id` ASC),
  CONSTRAINT `fk_transaction_details_transaction`
    FOREIGN KEY (`transaction_id`)
    REFERENCES `mydb`.`transaction` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_details_products1`
    FOREIGN KEY (`products_id`)
    REFERENCES `mydb`.`products` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`purchasing`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`purchasing` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `products_id` INT NOT NULL,
  `date` DATETIME NOT NULL,
  `purchasing_price` DOUBLE NOT NULL,
  `quantity` INT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `products_id`),
  INDEX `fk_purchasing_products1_idx` (`products_id` ASC),
  CONSTRAINT `fk_purchasing_products1`
    FOREIGN KEY (`products_id`)
    REFERENCES `mydb`.`products` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

If you want to be able to ask questions like "What was the price on <date in the past>" then your design seems reasonable. Otherwise it's probably unnecessarily complex. — Barmar
hmm so it's okay if I copy the price from product table into transaction_details, thanks to ensure me, yes I consider future requirement about tracking product sales data, such the stats of highest selling with particular price in particular period. — Angga Ari Wijaya
I recommend an order detail table that captures the price for the transaction at the time. If you publish an actual schema, I can talk against it (meaning pro's and cons) — Drew
@Drew I have update may question with include image of simple ERD — Angga Ari Wijaya

Joel Brown Joel Brown · Accepted Answer · 2016-08-21T12:46:52

I know that it looks like you are denormalizing price by keeping it on your transaction table, and that denormalizing feels "bad" because we like to follow best practices. However, a better way to think about your problem here is that the price is not being denormalized in this case.

The price on your product table (or in a product history table) is semantically different from the price in your transaction table.

One is the MSRP (i.e. the "ought to be" price) and one is the paid ("actual") price. Yes, these will be the same most of the time, but that is coincidental.

You should keep the price actually paid in the transaction table, whether or not you keep the price in a history table. The reason for this is that the transaction table is a record of what actually happened. In a way it's a kind of a write-once log. Auditors will like it better if you can show that prices actually paid can't be restated later based on how your code works. In a traditional accounting system even corrections are applied using reversing transactions rather than edits.

Another thing to consider is that prices can have exceptions. What if you decide to have a promotion with coupons, for example? Or if you provide a 20% discount for "open box" items? These kind of one-off prices are difficult to track in a price history table.

For these reasons keeping the price actually paid in the transaction table is a valid design decision, not just an expediency for performance or code simplicity.

Changes of product price in database design

2 Answers