mysql-多个表之间的SQL多对多关系

我有一个尝试在SQL上创建的数据库,并且试图将这些关系连接在一起.有三个表：superhero,power和superheroPower.表超级英雄和力量是由表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;

解决方法:

是的,那里的一切看起来还不错.但…

一些注意事项：

对于性别列,我们将使用较短的数据类型；我看不到需要255个字符来表示. (强制执行的行的最大大小是有限制的.)如果只有很少的值,我们将考虑使用ENUM数据类型.

我们还可能在其中的几列上添加NOT NULL约束,例如英雄名,名字,姓氏.我们还可能添加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.

跟进

我上面的“注释”不是必须遵循的特定规则；这些只是我们使用的模式.

遵循这些模式并不能保证我们将拥有成功的软件,但确实可以帮助我们.

为了供您参考,我将展示这些表在我们商店中的“初切”效果,以说明另一种模式.这不是“正确的方法”,而只是我们作为团队所确定的“一种方法”.

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;