mysql - SQL多表多对多关系

Question

我有一个试图在 SQL 上创建的数据库，我试图将这些关系连接在一起。共有三个表:superhero、power 和 superheroPower。表 superhero 和 power 是多对多关系，由表 superheroPower 表示。

下面的语法对于表之间的外键(以及其他所有内容)是否正确？此外，在设置方面对这些表还有其他建议吗？

CREATE TABLE superhero( id INT NOT NULL AUTO_INCREMENT, 
heroName VARCHAR(255) NOT NULL, 
firstName VARCHAR(255), 
lastName VARCHAR(255), 
firstAppearance DATE, 
gender VARCHAR(255), 
bio TEXT, 
universe VARCHAR(255), 
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE power( 
id INT NOT NULL AUTO_INCREMENT, 
name VARCHAR(255) NOT NULL, 
description TEXT NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE superheroPower( 
superheroID INT, 
powerID INT, 
PRIMARY KEY(superheroID, powerID), 
FOREIGN KEY(superheroID) REFERENCES superhero(id), 
FOREIGN KEY(powerID) REFERENCES power(id) 
) ENGINE=InnoDB;

Answer 1

是的，那里的一切看起来都很好。但是……

---

一些注意事项:

我们将为 gender 列使用更短的数据类型；我不认为我们需要 255 个字符来表达它。 (强制执行的行的最大大小有限制。)如果只有几个值，我们会考虑 ENUM 数据类型。

我们还可能在其中的几个列上添加 NOT NULL 约束，例如 heroname、firstname、lastname。我们还可能添加 DEFAULT ''。有时，出于某种原因我们确实需要允许 NULL 值，但我们会尽可能使用 NOT NULL。

我对 TEXT 列犹豫不决。使用 TEXT 数据类型没有任何问题，但我只是怀疑这些可能“隐藏”了一些可能更好地存储在其他列中的信息。

对于外键，我们会按照我们使用的模式为约束分配一个名称，并且还可能添加 ON UPDATE CASCADE ON DELETE CASCADE

CONSTRAINT FK_superheroPower_power FOREIGN KEY (powerID) 
  REFERENCES power(id) ON UPDATE CASCADE ON DELETE CASCADE

---

关于标识符(表名和列名)的说明

我们这样做的方式是，所有表名都是小写。 (我们有一个 MySQL 选项集，强制所有表名都小写。)我们这样做是为了避免不同操作系统/文件系统(其中一些区分大小写，一些不区分大小写)的不兼容问题。

此外，表名是单数。表格的名称命名了表格的一行 所代表的内容。我们也不包括 _table 作为名称的一部分。

MySQL 中的列名从不区分大小写，但我们也始终对列名使用小写。我们不使用“camelCase”我们的列名，我们使用下划线字符作为分隔符，例如power_id 与 powerID，hero_name 与 heroName。

---

跟进

我上面的“注意事项”并不是必须遵守的具体规则；这些只是我们使用的模式。

遵循这些模式并不能保证我们会拥有成功的软件，但它确实对我们有帮助。

为了您的引用，我将展示这些 table 作为我们商店的“第一次切割”时的样子，作为另一种模式的例证；这不是“正确的方式”，它只是我们作为一个团队确定的“方式”。

CREATE TABLE superhero
( id               INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'pk'
, hero_name        VARCHAR(255) NOT NULL                COMMENT ''
, first_name       VARCHAR(255) NOT NULL DEFAULT ''     COMMENT ''
, last_name        VARCHAR(255) NOT NULL DEFAULT ''     COMMENT ''
, first_appearance DATE                                 COMMENT 'date superhero first appeared'
, gender           ENUM('female','male','other')        COMMENT 'female,male or other'
, biography_text   TEXT                                 COMMENT ''
, universe         VARCHAR(255)                         COMMENT ''
, PRIMARY KEY(id)
, UNIQUE KEY superhero_UX1 (hero_name) 
) ENGINE=InnoDB;

CREATE TABLE power
( id               INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'pk'
, name             VARCHAR(255) NOT NULL                COMMENT ''  
, description_text TEXT NOT NULL                        COMMENT '' 
, PRIMARY KEY(id)
, UNIQUE KEY power_UX1 (name)
) ENGINE=InnoDB;

CREATE TABLE superheropower
( superhero_id   INT UNSIGNED NOT NULL         COMMENT 'pk, fk ref superhero'
, power_id       INT UNSIGNED NOT NULL         COMMENT 'pk, fk ref power'
, PRIMARY KEY(superhero_id, power_id)
, CONSTRAINT FK_superheropower_superhero 
     FOREIGN KEY(superhero_id) REFERENCES superhero(id)
     ON UPDATE CASCADE ON DELETE CASCADE
, CONSTRAINT FK_superheropower_power
     FOREIGN KEY (power_id) REFERENCES power(id) 
     ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;