最好添加一个鉴别器列,它可以是派生表中的计算列。在其他 DBMS 中,您可以在派生表中的计算列上放置外键,而在 MySQL 中,您需要检查约束。
CREATE TABLE Assistant (
id INT NOT NULL PRIMARY KEY,
type TINYINT NOT NULL CHECK (type IN (1, 2)),
firstname TEXT NOT NULL,
UNIQUE (id, type)
);
CREATE TABLE TerminalAssistant (
id INT NOT NULL PRIMARY KEY,
type TINYINT NOT NULL CHECK (type = 1),
FOREIGN KEY (id, type) REFERENCES Assistant (id, type)
);
现在您的连接表可以引用这些表。
CREATE TABLE Terminal_TerminalAssistant (
terminal_id INT NOT NULL,
assistant_id INT NOT NULL,
PRIMARY KEY (terminal_id, assistant_id),
FOREIGN KEY (terminal_id) REFERENCES Terminal (id),
FOREIGN KEY (assistant_id) REFERENCES TerminalAssistant (id)
);
在 的情况下Customer,由于您只能拥有一个,因此您也可以使用派生表作为实际的连接表。
CREATE TABLE CustomerAssistant (
assistant_id INT NOT NULL PRIMARY KEY,
assistant_type TINYINT NOT NULL CHECK (type = 2),
customer_id INT NOT NULL,
FOREIGN KEY (id, type) REFERENCES Assistant (assistant_id, assistant_type),
FOREIGN KEY (customer_id) REFERENCES Customer (id),
);
这是多态关联的经典案例。
您现有的设计是一种选择,尽管当有很多不同类型时它会变得混乱,并且在标准化方面感觉不那么干净。
相反,您需要单独的表
CustomerAssistant
和TerminalAssistant
与 1:1 对应Assistant
。这与连接表是分开的。最好添加一个鉴别器列,它可以是派生表中的计算列。在其他 DBMS 中,您可以在派生表中的计算列上放置外键,而在 MySQL 中,您需要检查约束。
现在您的连接表可以引用这些表。
在 的情况下
Customer
,由于您只能拥有一个,因此您也可以使用派生表作为实际的连接表。或者,您可以进行与上述相同的设计,除了添加一个
UNIQUE
约束assistant_id
以确保每个助手仅出现一次。如果您更改该要求,您只需删除约束即可,无需进行任何进一步的更改。现在 an
Assistant
只能是一种类型或另一种类型,而不能同时是两种类型。如果要更改类型,首先必须删除所有关联的行,否则外键约束将失败。数据库<>小提琴