Changes of product price in database design
<p>I have some issues about my POS database design, supposed I have <code>products</code> table with column id, product_name, price, and stocks, and then table <code>transactions</code>, and <code>transaction_details</code> if someone bought some products I have one transaction record and some transaction detail record, for now I copy value of price from <code>product</code> table into <code>transaction_details</code>, 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 <code>product_prices</code>, each product have many prices, and the <code>transaction_details</code> 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 <code>products</code> table, is it needed to or I just fetch from purchasing transaction subtract <code>unit_price</code> from <code>transaction_details</code>. Thank you for your answers.</p>

<p><a href="https://i.stack.imgur.com/uAjHd.png" rel="noreferrer"><img src="https://i.stack.imgur.com/uAjHd.png" alt="database design image"></a></p>

<pre><code>-- -----------------------------------------------------
-- 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;
</code></pre>