mysql - 表 "Products"有预定义的产品，用户可以自定义价格。如何避免数据冗余？

Question

几天来我一直在思考这个问题，但我仍然找不到做我想做的事情的方法。

下面是我的数据库目前是如何设计的(这是我卡住的地方):

这就是我想要的:

• 一个用户可以创建多个价格表。用户可以给价格表起任何他想要的名字。有两种价目表类型:“实验室实现”或“ self 实现”。
• 如果用户选择“Lab Fulfillment”，他可以导入其中一个预定义实验室的全部或部分产品。 (我换句话说:很少有实验室带有预定义的产品列表)。用户将只能自定义价格。他无法将定制产品添加到此价格表。
• 如果用户选择“Self Fulfillment”，他可以添加自己的产品，并可以个性化每个字段(名称、成本、价格、dimension_h、dimension_l)。

我不知道如何链接它们之间的表格。如果我将预定义的Products放在Products表中，并在PriceSheets和Product之间设置多对多关系，当用户自定义它时，预定义Product的默认价格将被覆盖，这不是我想要的。

另外，我希望我的预定义产品的默认值在我的数据库中只存在一次。如果 100 个用户使用预定义的产品，我不希望默认成本在我的数据库中出现 100 次。

不要犹豫，要求精确度，我很难把这个问题说清楚，我认为它仍然不完全清楚。

预先感谢您的帮助

Answer 1

好的，数据库规范化 101。有很多方法可以做到这一点，我需要一天时间才能真正优化所有这些，这应该有所帮助:

User

Lab

Product
id        name           cost          dimension   .....
1         a
2         b
3         c
4         d

所以那三个表没问题。您所有的产品都将放在产品表中。这些表中的任何一个都没有外键。

PriceSheet
user_id   custom_price   product_id    type
1         1.99           1             lab-fulfillment
0         NULL           2             self-fulfillment
1         5.99           3             lab-fulfillment

因此用户可以拥有任意数量的价格表，并且他们只能调整产品的价格。如果您愿意，这实际上可以进一步规范化:

PriceSheet (composite key on id, user_id, FK user_id)
id   user_id
0    0
1    1
2    1

LabPriceSheet (you could add an id, might be better, or you could use a composite key, stricter)
PriceSheet_id       custom_price   lab_product_id
0                   1.99           0
2                   5.99           1

CustomPriceSheet
PriceSheet_id       custom_product_id
1                   0

酌情使用外键。现在，这使 MySQL 限制了 custom_price，而不是在 PHP 中(尽管您仍然需要处理以确保正确的 INSERT!)。

现在，要处理谁添加了产品:

CustomProduct
id    user_id    product_id    timestamp
0     3          2             ...      

LabProduct
id    lab_id     product_id    timestamp
0     0          1             ...  
1     0          3             ...  

所以让我们仔细检查一下:

This is what I want :

a User can create multiple PriceSheets. check A User can give a PriceSheet any name he wants. check There are two PriceSheets types : "Lab Fulfillment", or "Self Fulfillment". check

if the User chooses "Lab Fulfillment", he can import all or part of the Products of one of the predefined Labs. (I rephrase : there are few Labs that come with a predefined list of Products). The User will only be able to customize the price. He can't add custom products to this PriceSheet.

是的，因为他会创建一个只能添加 lab_product_id 的 LabPriceSheet。自定义价格也在那里，它会覆盖产品表中的默认价格。

if the User chooses "Self Fulfillment", he can add his own products, and can personalize each field (name, cost, price, dimension_h, dimension_l).

是的，他会添加一个产品(您需要检查是否存在类似的产品，否则返回产品表中现有产品的 ID)，然后这也将成为 CustomProduct 中的一个条目。

I don't know how to link the tables between them. If I put the predefined Products in the Products table and set a Many-to-Many relationship between PriceSheets and Product, the default price of a predefined Product will be overwritten when a User customizes it, which is not what I want.

是的，这不会发生 :) 从不(非常非常少)实现许多关系。

Also, I want the default values of my predefined Products to be only once in my database. If 100 users uses the predefined Products, I don't want the default cost to be in my database 100 times.

当然。

如果您需要 MySQL 代码，请告诉我，我认为您是好的!请记住使用 InnoDB 并正确配置您的 MySQL 配置!

编辑我想通过复制和粘贴来帮助您。我喜欢复制和粘贴东西。此外，广告中有一个多余的 user_id 列，我在之前的编辑中修复了该列。

SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL general_log = 'OFF';
SET FOREIGN_KEY_CHECKS=1;
SET GLOBAL character_set_server = utf8mb4;
SET NAMES utf8mb4;
CREATE DATABASE SO; USE SO;
ALTER DATABASE SO CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `User` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(555) NOT NULL,
  `password` VARBINARY(200) NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `role` INT(2) NOT NULL,
  `active` TINYINT(1) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Lab` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Product` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1000) NOT NULL,
  `password` VARBINARY(200) NOT NULL,
  `cost` DECIMAL(10, 2) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `height` DECIMAL(15, 5) NOT NULL,
  `length` DECIMAL(15, 5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `CustomProduct` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` BIGINT(20) UNSIGNED NOT NULL,
  `product` BIGINT(20) UNSIGNED NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user`) REFERENCES `User`(`id`),
  FOREIGN KEY (`product`) REFERENCES `Product`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `LabProduct` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lab` BIGINT(20) UNSIGNED NOT NULL,
  `product` BIGINT(20) UNSIGNED NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`lab`) REFERENCES `Lab`(`id`),
  FOREIGN KEY (`product`) REFERENCES `Product`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `PriceSheet` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1000) NOT NULL,
  `user` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`,`user`),
  FOREIGN KEY (`user`) REFERENCES `User`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `LabPriceSheet` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `price_sheet` BIGINT(20) UNSIGNED NOT NULL,
  `lab_product` BIGINT(20) UNSIGNED NOT NULL,
  `custom_price` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`price_sheet`) REFERENCES `PriceSheet`(`id`),
  FOREIGN KEY (`lab_product`) REFERENCES `LabProduct`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `CustomPriceSheet` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `price_sheet` BIGINT(20) UNSIGNED NOT NULL,
  `custom_product` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`price_sheet`) REFERENCES `PriceSheet`(`id`),
  FOREIGN KEY (`custom_product`) REFERENCES `CustomProduct`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;