电子商务网站的数据库架构设计

Question

我正在使用MySQL制作一个电子商务网站。请告诉我设计中可能存在的缺陷或其他问题。产品在完成和尺寸上有变化。

• 产品A可以有1个或多个变体（1.AA哑光，AB木纹，AC油漆）。
• 每个变体可以有0个或多个尺寸（AA可以有30x40和40x60）。
• 每个产品都有其独特的属性（产品A可以有厚度，产品B可以有等级）。
• 产品的SKU和价格取决于其所有不同属性。

对于独特属性，我本可以使用EAV，但实际上选择了这种方式：

• 品牌、类别和系列是它们所表示的。
• base_relation_table 是上述 3 张表之间的关系，包括所有可能的组合。
• 产品 包含所有产品（产品 A、产品 B）和对应的 base_relation_table 的参考 ID，指示其所属的组合。它还引用了包含外观信息（石材、木纹等）的 Pattern。
• product_option_relation 是唯一属性（产品 A ID、厚度 ID）的关系表。
• option_table 仅包含名称（厚度、类型、印刷技术等）。
• option_value 包含所有选项表的值（0.7、0.9、专业级、初学者等）。
• product_variant 包含所有产品的变体（AA、AB、AC、BA、BB 等）。
• 颜色、表面处理 与产品变体（AA 磨砂红、AB 亮白等）呈 1:n 关系。
• 尺寸 与 product_variant 呈 n:m 关系。
• variant_values 是所有属性及其变体和唯一属性的组合。

架构：

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema Catelogue
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema Catelogue
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Catelogue` DEFAULT CHARACTER SET utf8 ;
USE `Catelogue` ;

-- -----------------------------------------------------
-- Table `Catelogue`.`brands`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`brands` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`brands` (
  `ID` VARCHAR(45) NOT NULL,
  `b_name` VARCHAR(45) NULL,
  `thumbnails` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`categorys`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`categorys` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`categorys` (
  `ID` VARCHAR(45) NOT NULL,
  `c_name` VARCHAR(45) NULL,
  `thumbnails` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`collections`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`collections` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`collections` (
  `ID` VARCHAR(45) NOT NULL,
  `co_name` VARCHAR(45) NULL,
  `thumbnails` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`base_Relation_table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`base_Relation_table` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`base_Relation_table` (
  `ID` INT NOT NULL,
  `Brands_ID` VARCHAR(45) NOT NULL,
  `Categorys_ID` VARCHAR(45) NOT NULL,
  `Collections_ID` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`),
  INDEX `fk_base_Relation_table_Brands_idx` (`Brands_ID` ASC) VISIBLE,
  INDEX `fk_base_Relation_table_Categorys1_idx` (`Categorys_ID` ASC) VISIBLE,
  INDEX `fk_base_Relation_table_Collections1_idx` (`Collections_ID` ASC) VISIBLE,
  CONSTRAINT `fk_base_Relation_table_Brands`
    FOREIGN KEY (`Brands_ID`)
    REFERENCES `Catelogue`.`brands` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_base_Relation_table_Categorys1`
    FOREIGN KEY (`Categorys_ID`)
    REFERENCES `Catelogue`.`categorys` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_base_Relation_table_Collections1`
    FOREIGN KEY (`Collections_ID`)
    REFERENCES `Catelogue`.`collections` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`pattern`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`pattern` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`pattern` (
  `ID` VARCHAR(45) NOT NULL,
  `option_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `values_UNIQUE` (`option_name` ASC) VISIBLE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`product`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`product` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`product` (
  `ID` INT NOT NULL,
  `p_name` VARCHAR(45) NULL,
  `Description` VARCHAR(45) NULL,
  `base_Relation_table_ID` INT NOT NULL,
  `pattern_ID` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`),
  INDEX `fk_Product_base_Relation_table1_idx` (`base_Relation_table_ID` ASC) VISIBLE,
  UNIQUE INDEX `name_UNIQUE` (`p_name` ASC) VISIBLE,
  INDEX `fk_Product_pattern1_idx` (`pattern_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Product_base_Relation_table1`
    FOREIGN KEY (`base_Relation_table_ID`)
    REFERENCES `Catelogue`.`base_Relation_table` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_pattern1`
    FOREIGN KEY (`pattern_ID`)
    REFERENCES `Catelogue`.`pattern` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`colors`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`colors` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`colors` (
  `ID` INT NOT NULL,
  `color_name` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`option_table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`option_table` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`option_table` (
  `ID` INT NOT NULL,
  `option_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `values_UNIQUE` (`option_name` ASC) VISIBLE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`option_values`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`option_values` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`option_values` (
  `ID` INT NOT NULL,
  `Option_ID` INT NOT NULL,
  `value_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`, `Option_ID`),
  UNIQUE INDEX `values_UNIQUE` (`value_name` ASC) VISIBLE,
  CONSTRAINT `fk_Option_values_Options1`
    FOREIGN KEY (`Option_ID`)
    REFERENCES `Catelogue`.`option_table` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`finish`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`finish` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`finish` (
  `ID` INT NOT NULL,
  `finish_name` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`product_variant`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`product_variant` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`product_variant` (
  `Variant_ID` INT NOT NULL,
  `Product_ID` INT NOT NULL,
  `Finish_ID` INT NOT NULL,
  `Colors_ID` INT NOT NULL,
  `metadata` VARCHAR(45) NULL,
  `thumbnail` VARCHAR(45) NOT NULL DEFAULT '\" \"',
  INDEX `fk_ProductDetails_Finish1_idx` (`Finish_ID` ASC) VISIBLE,
  INDEX `fk_ProductDetails_Colors1_idx` (`Colors_ID` ASC) VISIBLE,
  INDEX `fk_Product_Variant_Product1_idx` (`Product_ID` ASC) VISIBLE,
  PRIMARY KEY (`Variant_ID`, `Product_ID`),
  CONSTRAINT `fk_ProductDetails_Finish1`
    FOREIGN KEY (`Finish_ID`)
    REFERENCES `Catelogue`.`finish` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ProductDetails_Colors1`
    FOREIGN KEY (`Colors_ID`)
    REFERENCES `Catelogue`.`colors` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Variant_Product1`
    FOREIGN KEY (`Product_ID`)
    REFERENCES `Catelogue`.`product` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`product_option_relation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`product_option_relation` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`product_option_relation` (
  `Product_ID` INT NOT NULL,
  `Option_ID` INT NOT NULL,
  INDEX `fk_Product_Option_Product1_idx` (`Product_ID` ASC) VISIBLE,
  INDEX `fk_Product_Option_Options1_idx` (`Option_ID` ASC) VISIBLE,
  PRIMARY KEY (`Product_ID`, `Option_ID`),
  CONSTRAINT `fk_Product_Option_Product1`
    FOREIGN KEY (`Product_ID`)
    REFERENCES `Catelogue`.`product` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Option_Options1`
    FOREIGN KEY (`Option_ID`)
    REFERENCES `Catelogue`.`option_table` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`dimensions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`dimensions` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`dimensions` (
  `ID` INT NOT NULL,
  `dimensions_value` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`dimensions_has_product_variant`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`dimensions_has_product_variant` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`dimensions_has_product_variant` (
  `Dimensions_ID` INT NOT NULL,
  `Product_ID` INT NOT NULL,
  `Variant_ID` INT NOT NULL,
  PRIMARY KEY (`Dimensions_ID`, `Product_ID`, `Variant_ID`),
  INDEX `fk_Dimensions_has_Product_Variant_Product_Variant1_idx` (`Product_ID` ASC, `Variant_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Dimensions_has_Product_Variant_Dimensions1`
    FOREIGN KEY (`Dimensions_ID`)
    REFERENCES `Catelogue`.`dimensions` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Dimensions_has_Product_Variant_Product_Variant1`
    FOREIGN KEY (`Product_ID` , `Variant_ID`)
    REFERENCES `Catelogue`.`product_variant` (`Product_ID` , `Variant_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Catelogue`.`variant_value`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Catelogue`.`variant_value` ;

CREATE TABLE IF NOT EXISTS `Catelogue`.`variant_value` (
  `Product_ID` INT NOT NULL,
  `Option_ID` INT NOT NULL,
  `Value_ID` INT NOT NULL,
  `Dimensions_ID` INT NOT NULL,
  `Variant_ID` INT NOT NULL,
  `price` VARCHAR(45) NOT NULL,
  `SKU` VARCHAR(45) NULL,
  PRIMARY KEY (`Product_ID`, `Option_ID`, `Value_ID`, `Dimensions_ID`, `Variant_ID`),
  INDEX `fk3_option_values_idx` (`Value_ID` ASC, `Option_ID` ASC) VISIBLE,
  INDEX `fk2_product_options_idx` (`Product_ID` ASC, `Option_ID` ASC) VISIBLE,
  INDEX `fk_variant_value_dimensions_has_product_variant1_idx` (`Dimensions_ID` ASC, `Variant_ID` ASC, `Product_ID` ASC) VISIBLE,
  CONSTRAINT `fk2_product_options`
    FOREIGN KEY (`Product_ID` , `Option_ID`)
    REFERENCES `Catelogue`.`product_option_relation` (`Product_ID` , `Option_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk3_option_values`
    FOREIGN KEY (`Value_ID` , `Option_ID`)
    REFERENCES `Catelogue`.`option_values` (`ID` , `Option_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_variant_value_dimensions_has_product_variant1`
    FOREIGN KEY (`Dimensions_ID` , `Variant_ID` , `Product_ID`)
    REFERENCES `Catelogue`.`dimensions_has_product_variant` (`Dimensions_ID` , `Variant_ID` , `Product_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

我有什么遗漏吗？它会扩展吗？它看起来好吗？

- Seeon

---

乍一看，这看起来还不错。但是如果有缺点的话——只有在开始创建报告时才会开始注意到它们。如果您开始感觉报告查询变得过于复杂而难以构建——那么也许您面临的是数据模型中的缺陷。 - IVO GELOV

Answer 1

它是“过度规范化”的。例如，看看colors。您正在用4字节整数替换3字节的“red”。没有空间节省。(好吧，“magenta”需要超过4个字节，但不多。)

规范化的另一个目的是使更容易在整个数据集中更改“red”的拼写。我认为那不会发生。

所以...有专门用于“常见”、“重要”或“常搜索的”属性的列。

对于其他属性，请将它们放入JSON字符串中，并将其命名为other_attributes等列。

虽然您的模式比常见的EAV更复杂，但这里讨论了为什么EAV很差以及如何解决它：http://mysql.rjweb.org/doc.php/eav 至于dimensions，请考虑您将如何处理值或值列表。我怀疑您除了在屏幕上显示该列表之外，不会对"30x40,40x60"做任何事情。它本质上是无法搜索的。因此，唯一实际的方法是将该字符串放入我建议的JSON中。

- Rick James

---

对于颜色，我有很多其他名称的选择，这些名称是由诸如“浅黄”、“浅绿”等单词组合而成的。至于“common table part”，我不太明白你的意思是什么？至于尺寸，我计划根据不同的尺寸对我的产品进行分类。我一定会尝试你建议的使用JSON字符串来处理“other_attributes”。 - Seeon

@Seeon - “深天蓝色”。在 product_variant 中，使用 color VARCHAR(..) 替代 color_id；然后删除 colors 表。接着，可以直接使用 SELECT ... ORDER BY color 而无需进行 JOIN 操作。 - Rick James

明白了。但是我在想，由于我有超过3千个产品，这种存储方式是否正确？感谢您的回复，我非常感激。 - Seeon

@Seeon - 如果你有1亿个产品，那么存储问题值得考虑。但只有3K的话，我不会担心多出来10KB的存储空间。 - Rick James

Answer 2

我不知道你的数据库有多大的变化，我认为维度表需要扩展。你至少需要添加几个更多的表，比如基于维度的完成、颜色的价格计算表。你还没有添加客户、购物车、支付、运输表和税表。 如果你已经添加了，请告诉我。

- Gopal Panda