mysql - 数据库设计中产品价格的变化

Question

我的 POS 数据库设计有一些问题，假设我有包含列 id、product_name、价格和库存的 products 表，然后是 transactions 表，以及 transaction_details 如果有人买了一些产品我有一个交易记录和一些交易明细记录，现在我将价格值从product表复制到transaction_details，所以如果产品价格发生变化，它们不会影响交易历史/报告，但我考虑将单独的价格放入另一个表中，比方说 product_prices，每个产品都有很多价格，而 transaction_details 与 product_prices 相关，而不是直接产品本身。我的方法是好是坏对应于数据本身的数据完整性、性能或效率。我在 products 表中有库存，是否需要，或者我只是从采购交易中获取，从 transaction_details 中减去 unit_price。感谢您的回答。

-- -----------------------------------------------------
-- 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

我知道您似乎通过将价格保留在交易表中来对价格进行非规范化，而且这种非规范化感觉“不好”，因为我们喜欢遵循最佳实践。但是，在这里考虑您的问题的更好方法是，在这种情况下，价格未被非规范化。

产品表(或产品历史表)中的价格在语义上与交易表中的价格不同。

一种是厂商建议零售价(即“应有”价格)，一种是已付(“实际”)价格。是的，大部分时间这些都是相同的，但这是巧合。

你应该在交易表中保留实际支付的价格，无论你是否在历史表中保留价格。这样做的原因是交易表记录了实际发生的事情。在某种程度上，它是一种一次写入的日志。如果你能证明实际支付的价格不能在以后根据你的代码的工作方式重述，审计员会更喜欢它。在传统的会计系统中，甚至更正都是使用逆向交易而不是编辑来应用的。

另一件需要考虑的事情是价格可能有异常(exception)。例如，如果您决定使用优惠券进行促销怎么办？或者，如果您为“开箱”商品提供 20% 的折扣？这种一次性价格很难在价格历史表中追踪。

由于这些原因，将实际支付的价格保留在交易表中是一个有效的设计决策，而不仅仅是性能或代码简化的权宜之计。