使用两个不同的外键创建表

I have to create a table with relationships to two different tables. Consider the example below.

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

Lets say I have following three tables:

让我们说我有以下三个表：

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

Now what I want to do is create a foreign key reference in person table for the person's village or town. I thought of two ways.

现在我想要做的是在人员村或城镇的人员表中创建一个外键引用。我想到了两种方式。

One is to create one column of residence_name and just input id from village or town table and then query it based on residence_type whether its V or T. But surely I cannot create it as a foreign key reference for two different tables at same time.

一种是创建一个residence_name列，只输入来自村庄或城镇表的id，然后根据residence_type查询它是否为V或T.但是我当然不能将它作为两个不同表的外键引用同时创建。

Another option is to create two columns - v_id and t_id in person table and fill one based on residence_type. Again I can query it accordingly but surely cannot create it as foreign key reference as it cannot be NULL but in this case one column will always be NULL.

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

Which of the two approaches is better or is there any better approach which will allow me to create a foreign key reference?

这两种方法中的哪一种更好，还是有更好的方法可以让我创建一个外键引用？

1 个解决方案

#1

To me it looks like the design is somewhat "iffy". My assumption is that for a person you'd want to know what his residence is, i.e. ultimately name of village or town?

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

If that is the case, I'd remove the Village and Town tables and replace that with a Residence look up table which contains both Villages and towns, and which has a column to a ResidenceType table. The ResidenceType table would look something like so (this is SQL Server syntax):

如果是这种情况，我会删除乡村和城镇的桌子，并将其替换为包含乡村和城镇的住宅查找表，其中包含一个到ResidenceType表的列。 ResidenceType表看起来像这样（这是SQL Server语法）：

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

You would then populate that table like so:

然后，您将填充该表，如下所示：

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

Now you can create a Residence table, whoch would hold both town and village names - and it would have a foreign key against the ResidenceType table:

现在你可以创建一个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),
);

Add some data:

添加一些数据：

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

Finally your Person table would look something like:

最后你的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),
);

That's the way I would suggest you'd do it. You are now following best practices when it comes to normalization etc.

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

#1