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.

Answer 2

We learned it hard way... What You might think about is WHERE would you need price except product detail itself and transation detail (or basically history). If you have some statistics about product pricing OR you have product which price may warry a lot depending on time (good example will be currency trades) then price history is required. But if it's not the case, you probably should go for separated product and transaction detail. Otherwise you're slowing whole system instead of 1 duplicate (in most cases) value per transaction. I hope it makes sense...

Update #1

But stock requires different approach with same thing to keep in mind: Always compute and load least amount of data possible. If you want co calculate some values, create Cron script for that and store result in db, becaues loading from database is in most cases faster than calculating stuff like stock from history in users request.

Also keep in mind that product can have different colors/sizes/other attributes and you definitely dont want to put same T-Shirt in 3 colors and 5 sizes as 3*5=15 different products. SO you need to store combination and attrbutes somewhere and have productId on every combination (or maybe as part of optimization you will store more data in combinatons than in prodct table in order to achieve select from one table instead of joins, but that's another question).

Now, every color/size have different stock, so there's your stock value per combination of attributes. Only question I have in my own structure is whether I want to store stock in product table at all (I can easily normalize structure by assigning "default" combination if product doesnt have varations), or should I store stock for combinations directly in combination table and make cron which compute total stock of all combinations and update stock in product. Again, it all depends on your data usage, in my case, tbh I cant think of any place I would need something similar to product stock on shop with clothes (and make no mistakes, there's plenty of different similar products).

Update #2

And don't forget about future code expansion. I've never seen a single project which didn't have new features implemented later or even after public release. So not only should you have optimized data, but also be prepared for new modules and be flexible enough to keep things fast. Bad example is our current CMS which had products, then somebody added cache table which contains only important data for product detail but later somebody else found out that there're much more vars needed for product so they returned back products table. But nw it already wasnt enough because cache contained important cached data which product didnt have, so they join things altogether and now instead of optimized cache loading, we have triple join with slow LEFT JOIN implemented in it...