在SQL数据库中，何时一对一的关系应该在同一个表中，并在不同的表中？ [英] In a SQL database, when should a one-to-one relationship be in the same table and when in separate tables?

问题描述

任何人都可以提供一些SQL数据库中的一些示例，在同一个表上保存一对一的关系是更好的选择，而将它们放在单独的表上更合理？

当你有几个实体都必须能够作为另一个实体的外键，并且几个实体有两个共同的属性和独特的属性，并且你想要一个独特的属性的NOT NULL约束（或者更重要的是不希望一堆NULL值的唯一属性不适用于其他实体）。即使你没有独特的/通用的属性，也不关心NULL值，如果你希望在每个子表格和超类型表格上单独使用外部约束，你可能仍然希望这样做。这个策略被称为超类型/亚型建模。

让我给你一个例子。

people

• id（PK）


• 姓名
>

教师

• id（PK，and FK to people.id）
  
• years_teaching NOT NULL


• 无NOT NULL

学生

• id（PK和FK给people.id）
  
• 年级不为空


• 无论是否为空

正如您所看到的，教师和学生可以拥有单个公用表的一些属性，并可以分别拥有自己的NOT NULL唯一属性。此外，您可以将人员，教师和学生加入到其他表格中，并保持参照完整性。

另一个应用程序可能一些属性在一个和另一个，但是，我从来没有这样做过。

Can anyone provide some examples of when in a SQL database it's a better choice to keep one-to-one relationships on the same table, and when instead it makes more sense to have them on separate tables?

解决方案

When you have several entities which all must be able to act as a foreign key to another entity, and the "several entities" have both common properties and unique properties, and you want a NOT NULL constraint on the unique properties (or less important don't want a bunch of NULL values for the unique properties not applicable to the other entity). Even if you didn't have the unique/common properties and didn't care about the NULL values, you might still wish to do so if you wanted individual foreign constraints on each subtpye table as well as the supertype table. This strategy is called supertype/subtype modelling.

Let me give you an example.

peoples

• id (PK)
• name
• age

teachers

• id (PK, and FK to people.id)
• years_teaching NOT NULL
• whatever NOT NULL

students

• id (PK, and FK to people.id)
• grade NOT NULL
• whatever NOT NULL

As you see, teachers and students can have a single common table for some of the properties and can each have their own NOT NULL unique properties. Furthermore, you can JOIN people, teachers, and students to other tables and keep referential integrity.

Another application "might" be if you had separate databases for the each record with some of the properties in one and some in the other, however, I have never done this.

这篇关于在SQL数据库中，何时一对一的关系应该在同一个表中，并在不同的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！