MySQL - 一张表中的多个外键

Question

我正在为游戏制作数据库...问题是我对数据库了解不多。这是我遇到问题的表格:

CREATE TABLE items(
name VARCHAR(30) PRIMARY KEY,
type VARCHAR(20) NOT NULL,
atk INT,
def INT,
arm INT,
price_buy SMALLINT UNSIGNED,
price_sell SMALLINT UNSIGNED);

CREATE TABLE equipment (
id_eq INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
head_s VARCHAR(30),
body_s VARCHAR(30),
right_s VARCHAR(30),
left_s VARCHAR(30),
feet_s VARCHAR(30),
slot1 VARCHAR(30),
slot2 VARCHAR(30),
slot3 VARCHAR(30),
slot4 VARCHAR(30),
slot5 VARCHAR(30),
slot6 VARCHAR(30),
slot7 VARCHAR(30),
slot8 VARCHAR(30),
slot9 VARCHAR(30),
slot10 VARCHAR(30).
FOREIGN KEY (head_s) REFERENCES items(name),
FOREIGN KEY (body_s) REFERENCES items(name),
FOREIGN KEY (right_s) REFERENCES items(name),
FOREIGN KEY (left_s) REFERENCES items(name),
FOREIGN KEY (feet_s) REFERENCES items(name),
FOREIGN KEY (slot1) REFERENCES items(name),
FOREIGN KEY (slot2) REFERENCES items(name),
FOREIGN KEY (slot3) REFERENCES items(name),
FOREIGN KEY (slot4) REFERENCES items(name),
FOREIGN KEY (slot5) REFERENCES items(name),
FOREIGN KEY (slot6) REFERENCES items(name),
FOREIGN KEY (slot7) REFERENCES items(name),
FOREIGN KEY (slot8) REFERENCES items(name),
FOREIGN KEY (slot9) REFERENCES items(name),
FOREIGN KEY (slot10) REFERENCES items(name));

我对这个解决方案有疑问，有人能告诉我这是正确的方法吗？

Answer 1

一般来说，这不是正确的方法，除非您正好有 7 个标记为 1 到 7 的插槽，并且您要将元素放入其中的部分或全部插槽中。

更典型的方式是联结表:

create table EquipmentItems (
    EquipmentItemId int primary key auto_increment,
    EquipmentId int,
    ItemId int,
    constraint fk_equipmentitems_equipment foreign key (EquipementId) references Equipement(id_eq),
    constraint fk_equipmentitems_item foreign key (ItemId) references items(ItemId)
);

如果您仔细查看，您会注意到我向 Items 添加了一个合成 key 。对于外键引用来说这是一个好主意，因为整数通常比字符串更紧凑。