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;

Answer 1

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.