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添加了一个合成键。对于外键引用，这是个好主意，因为整数通常比字符串更紧凑。