mysql - 创建具有两个不同外键的表

Question

我必须创建一个与两个不同表有关系的表。考虑下面的例子。

假设我有以下三个表:

1) Person -> person_id, residence_type (V or T)
2) Village -> village_id, village_name
3) Town -> town_id, town_name

现在我想做的是在人员表中为该人的村庄或城镇创建一个外键引用。我想了两个办法。

一种是创建一列residence_name，然后从村庄或城镇表中输入id，然后根据residence_type查询它是V还是T。但是我肯定不能将它同时创建为两个不同表的外键引用时间。

另一种选择是在 person 表中创建两列 - v_id 和 t_id，并根据居住类型填充一列。再次，我可以相应地查询它，但肯定不能将其创建为外键引用，因为它不能为 NULL，但在这种情况下，一列将始终为 NULL。

这两种方法哪一种更好，或者有没有更好的方法可以让我创建外键引用？

Answer 1

对我来说，这个设计看起来有点“不确定”。我的假设是，对于一个人，您想知道他的住所是什么，即最终的村庄或城镇名称？

如果是这种情况，我会删除“村庄”和“城镇”表，并将其替换为“住宅”查找表，该表包含村庄和城镇，并且有一列指向“住宅类型”表。 ResidenceType 表看起来像这样(这是 SQL Server 语法):

CREATE TABLE dbo.ResidenceType
(
  ResidenceTypeID smallint NOT NULL,
  ResidenceTypeDefinition nvarchar(25),
  CONSTRAINT [pk_ResidenceType] PRIMARY KEY (ResidenceTypeID),
);

然后您将像这样填充该表:

INSERT INTO dbo.ResidenceType(ResidenceTypeID, ResidenceTypeDefinition)
VALUES (1, 'Village'), (2, 'Town');

现在您可以创建一个 Residence 表，该表将保存城镇和村庄名称 - 并且它将有一个针对 ResidenceType 表的外键:

CREATE TABLE dbo.Residence
(
  ResidenceID int NOT NULL,
  ResidenceTypeID smallint NOT NULL,
  ResidenceName nvarchar(25),
  CONSTRAINT [pk_Residence] PRIMARY KEY (ResidenceID),
  CONSTRAINT [fk_ResidenceTypeID] FOREIGN KEY (ResidenceTypeID)
  REFERENCES dbo.ResidenceType(ResidenceTypeID),
);

添加一些数据:

INSERT INTO dbo.Residence(ResidenceID, ResidenceTypeID, ResidenceName)
VALUES (1, 1, 'Village 1'),
       (2, 1, 'Village 2'),
       (3, 2, 'Town 1'),
       etc...

最后你的 Person 表看起来像这样:

CREATE TABLE dbo.Person
(
  PersonID bigint NOT NULL,
  ResidenceID int NOT NULL,
  CONSTRAINT [pk_Person] PRIMARY KEY (PersonID),
  CONSTRAINT [fk_Person_ResidenceID] FOREIGN KEY (ResidenceID)
  REFERENCES dbo.Residence(ResidenceID),
);

这就是我建议你这样做的方式。您现在在标准化等方面遵循最佳实践。