SQL 是否曾经有过使用数据库 1:1 关系有意义的时候？

Is there ever a time where using a database 1:1 relationship makes sense?

提问by Pulsehead

I was thinking the other day on normalization, and it occurred to me, I cannot think of a time where there should be a 1:1 relationship in a database.

前几天我正在考虑规范化，我突然想到，我想不出在数据库中应该有 1:1 关系的时候。

Name:SSN? I'd have them in the same table PersonID:AddressID? Again, same table.

姓名：社会保障号？我将它们放在同一张表中 PersonID:AddressID? 再次，同一张桌子。

I can come up with a zillion examples of 1:many or many:many (with appropriate intermediate tables), but never a 1:1.

我可以想出无数个 1:many 或 many:many 的例子（带有适当的中间表），但永远不会是 1:1。

Am I missing something obvious?

我错过了一些明显的东西吗？

采纳答案by Godeke

A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be "unknown" at the same time (in which case you have a 1:0 or 1:1, but no more).

1:1 关系通常表示您出于某种原因对更大的实体进行了分区。通常是由于物理模式中的性能原因，但如果预计大量数据同时是“未知的”（在这种情况下，您有一个 1:0或 1:1，但仅此而已）。

As an example of a logical partition: you have data about an employee, but there is a larger set of data that needs to be collected, if and only if they select to have health coverage. I would keep the demographic data regarding health coverage in a different table to both give easier security partitioning and to avoid hauling that data around in queries unrelated to insurance.

作为逻辑分区的示例：您有关于员工的数据，但需要收集更大的数据集，当且仅当他们选择有健康保险时。我会将有关健康保险的人口统计数据保存在不同的表中，以便更轻松地进行安全分区，并避免在与保险无关的查询中搬运这些数据。

An example of a physical partition would be the same data being hosted on multiple servers. I may keep the health coverage demographic data in another state (where the HR office is, for example) and the primary database may only link to it via a linked server... avoiding replicating sensitive data to other locations, yet making it available for (assuming here rare) queries that need it.

物理分区的一个示例是托管在多个服务器上的相同数据。我可能会将健康保险人口统计数据保存在另一个州（例如人力资源办公室所在的州），并且主数据库可能只能通过链接服务器链接到它……避免将敏感数据复制到其他位置，但使其可用于（假设这里很少见）需要它的查询。

Physical partitioning can be useful wheneveryou have queries that need consistent subsets of a larger entity.

每当您的查询需要较大实体的一致子集时，物理分区就非常有用。

回答by kemiller2002

One reason is database efficiency. Having a 1:1 relationship allows you to split up the fields which will be affected during a row/table lock. If table A has a ton of updates and table b has a ton of reads (or has a ton of updates from another application), then table A's locking won't affect what's going on in table B.

原因之一是数据库效率。拥有 1:1 关系允许您拆分将在行/表锁定期间受到影响的字段。如果表 A 有大量更新，而表 b 有大量读取（或有大量来自另一个应用程序的更新），那么表 A 的锁定不会影响表 B 中发生的事情。

Others bring up a good point. Security can also be a good reason depending on how applications etc. are hitting the system. I would tend to take a different approach, but it can be an easy way of restricting access to certain data. It's really easy to just deny access to a certain table in a pinch.

其他人提出了一个好观点。根据应用程序等如何影响系统，安全性也可以是一个很好的理由。我倾向于采用不同的方法，但它可以是限制对某些数据的访问的一种简单方法。在紧要关头拒绝访问某个表真的很容易。

My blog entry about it.

我的博客条目。

回答by chaos

Sparseness. The data relationship may be technically 1:1, but corresponding rows don't have to exist for every row. So if you have twenty million rows and there's some set of values that only exists for 0.5% of them, the space savings are vast if you push those columns out into a table that can be sparsely populated.

稀疏。数据关系在技术上可能是 1:1，但不必每行都存在对应的行。因此，如果您有 2000 万行，并且某些值集仅占其中的 0.5%，那么如果将这些列推送到一个可以稀疏填充的表中，则可以节省大量空间。

回答by Tripartio

Most of the highly-ranked answers give very useful database tuning and optimization reasons for 1:1 relationships, but I want to focus on nothing but "in the wild" examples where 1:1 relationships naturally occur.

大多数排名靠前的答案都为 1:1 关系提供了非常有用的数据库调整和优化原因，但我只想关注自然发生 1:1 关系的“野外”示例。

Please note one important characteristic of the database implementation of most of these examples: no historical information is retained about the 1:1 relationship. That is, these relationships are 1:1 at any given point in time. If the database designer wants to record changes in the relationship participants over time, then the relationships become 1:M or M:M; they lose their 1:1 nature. With that understood, here goes:

请注意其中大多数示例的数据库实现的一个重要特征：没有保留关于 1:1 关系的历史信息。也就是说，这些关系在任何给定时间点都是 1:1。如果数据库设计者想记录关系参与者随时间的变化，那么关系就变成 1:M 或 M:M；他们失去了 1:1 的本性。明白了这一点，这里是：

• "Is-A" or supertype/subtype or inheritance/classification relationships: This category is when one entity is a specific type of another entity. For example, there could be an Employee entity with attributes that apply to all employees, and then different entities to indicate specific types of employee with attributes unique to that employee type, e.g. Doctor, Accountant, Pilot, etc. This design avoids multiple nulls since many employees would not have the specialized attributes of a specific subtype. Other examples in this category could be Product as supertype, and ManufacturingProduct and MaintenanceSupply as subtypes; Animal as supertype and Dog and Cat as subtypes; etc. Note that whenever you try to map an object-oriented inheritance hierarchy into a relational database (such as in an object-relational model), this is the kind of relationship that represents such scenarios.

• "Boss" relationships, such as manager, chairperson, president, etc., where an organizational unit can have only one boss, and one person can be boss of only one organizational unit. If those rules apply, then you have a 1:1 relationship, such as one manager of a department, one CEO of a company, etc. "Boss" relationships don't only apply to people. The same kind of relationship occurs if there is only one store as the headquarters of a company, or if only one city is the capital of a country, for example.

• Some kinds of scarce resource allocation, e.g. one employee can be assigned only one company car at a time (e.g. one truck per trucker, one taxi per cab driver, etc.). A colleague gave me this example recently.

• Marriage(at least in legal jurisdictions where polygamy is illegal): one person can be married to only one other person at a time. I got this example from a textbook that used this as an example of a 1:1 unary relationship when a company records marriages between its employees.

• Matching reservations: when a unique reservation is made and then fulfilled as two separate entities. For example, a car rental system might record a reservation in one entity, and then an actual rental in a separate entity. Although such a situation could alternatively be designed as one entity, it might make sense to separate the entities since not all reservations are fulfilled, and not all rentals require reservations, and both situations are very common.

• “Is-A”或超类型/子类型或继承/分类关系：此类别是指一个实体是另一个实体的特定类型。例如，可能有一个 Employee 实体，其属性适用于所有员工，然后不同的实体表示具有该员工类型独有属性的特定类型的员工，例如医生、会计师、飞行员等。这种设计避免了多个空值，因为许多员工不具备特定子类型的专业属性。此类别中的其他示例可能是 Product 作为超类型，而 ManufacturingProduct 和 MaintenanceSupply 作为子类型；动物作为超类型，狗和猫作为亚类型；等等。请注意，每当您尝试将面向对象的继承层次结构映射到关系数据库（例如在对象-关系模型中）时，这就是表示此类场景的关系类型。

• “老板”关系，如经理、主席、总裁等，其中一个组织单位只能有一个老板，一个人只能是一个组织单位的老板。如果这些规则适用，那么你就有了 1:1 的关系，例如一个部门的经理，一个公司的 CEO 等等。“老板”关系不仅仅适用于人。例如，如果只有一家商店作为公司的总部，或者只有一个城市是一个国家的首都，也会出现同样的关系。

• 某些稀缺资源分配，例如，一名员工一次只能分配一辆公司汽车（例如，每个卡车司机一辆卡车，每个出租车司机一辆出租车等）。最近一位同事给了我这个例子。

• 婚姻（至少在一夫多妻是非法的法律管辖范围内）：一个人一次只能与另一个人结婚。我从一本教科书中得到了这个例子，当公司记录其员工之间的婚姻时，该教科书将其用作 1:1 一元关系的示例。

• 匹配预留：当进行唯一预留然后作为两个单独的实体执行时。例如，汽车租赁系统可能会在一个实体中记录预订，然后在单独的实体中记录实际租赁。虽然这种情况也可以设计为一个实体，但将实体分开可能是有意义的，因为并非所有预订都已完成，也并非所有租金都需要预订，而且这两种情况都很常见。

I repeat the caveat I made earlier that most of these are 1:1 relationships only if no historical information is recorded. So, if an employee changes their role in an organization, or a manager takes responsibility of a different department, or an employee is reassigned a vehicle, or someone is widowed and remarries, then the relationship participants can change. If the database does not store any previous history about these 1:1 relationships, then they remain legitimate 1:1 relationships. But if the database records historical information (such as adding start and end dates for each relationship), then they pretty much all turn into M:M relationships.

我重复我之前所做的警告，即只有在没有记录历史信息的情况下，其中大部分都是 1:1 的关系。因此，如果员工改变他们在组织中的角色，或者经理负责不同的部门，或者员工被重新分配车辆，或者有人丧偶并再婚，那么关系参与者可能会改变。如果数据库不存储有关这些 1:1 关系的任何先前历史记录，则它们仍然是合法的 1:1 关系。但是如果数据库记录了历史信息（例如为每个关系添加开始和结束日期），那么它们几乎都变成了 M:M 关系。

There are two notable exceptions to the historical note: First, some relationships change so rarely that historical information would normally not be stored. For example, most IS-A relationships (e.g. product type) are immutable; that is, they can never change. Thus, the historical record point is moot; these would always be implemented as natural 1:1 relationships. Second, the reservation-rental relationship store dates separately, since the reservation and the rental are independent events, each with their own dates. Since the entities have their own dates, rather than the 1:1 relationship itself having a start date, these would remain as 1:1 relationships even though historical information is stored.

历史注释有两个明显的例外：首先，某些关系很少发生变化，以至于通常不会存储历史信息。例如，大多数 IS-A 关系（例如产品类型）是不可变的；也就是说，它们永远不会改变。因此，历史记录点是没有意义的；这些将始终作为自然的 1:1 关系来实现。其次，预订-租赁关系存储日期，因为预订和租赁是独立的事件，每个事件都有自己的日期。由于实体具有自己的日期，而不是 1:1 关系本身具有开始日期，因此即使存储了历史信息，这些实体也将保持为 1:1 关系。

回答by Walter Mitty

Your question can be interpreted in several ways, because of the way you worded it. The responses show this.

由于您的措辞方式，您的问题可以有多种解释。回应表明了这一点。

There can definitely be 1:1 relationships between data items in the real world. No question about it. The "is a" relationship is generally one to one. A car is a vehicle. One car is one vehicle. One vehicle might be one car. Some vehicles are trucks, in which case one vehicle is not a car. Several answers address this interpretation.

现实世界中的数据项之间肯定存在 1:1 的关系。毫无疑问。“是一个”关系通常是一对一的。汽车是一种交通工具。一辆车就是一辆车。一辆车可能是一辆车。有些车辆是卡车，在这种情况下，一辆车不是汽车。有几个答案解决了这种解释。

But I think what you really are asking is... when 1:1 relationships exist, should tables ever be split? In other words, should you ever have two tables that contain exactly the same keys? In practice, most of us analyze only primary keys, and not other candidate keys, but that question is slightly diferent.

但我认为您真正要问的是...当存在 1:1 关系时，是否应该拆分表？换句话说，您是否应该有两个包含完全相同键的表？在实践中，我们大多数人只分析主键，而不分析其他候选键，但这个问题略有不同。

Normalization rules for 1NF, 2NF, and 3NF never require decomposing (splitting) a table into two tables with the same primary key. I haven't worked out whether putting a schema in BCNF, 4NF, or 5NF can ever result in two tables with the same keys. Off the top of my head, I'm going to guess that the answer is no.

1NF、2NF 和 3NF 的规范化规则从不需要将一个表分解（拆分）为具有相同主键的两个表。我还没有弄清楚将模式放在 BCNF、4NF 或 5NF 中是否会导致两个具有相同键的表。在我的头顶上，我猜答案是否定的。

There is a level of normalization called 6NF. The normalization rule for 6NF can definitely result in two tables with the same primary key. 6NF has the advantage over 5NF that NULLS can be completely avoided. This is important to some, but not all, database designers. I've never bothered to put a schema into 6NF.

有一种称为 6NF 的标准化级别。6NF 的规范化规则肯定会导致两个表具有相同的主键。6NF 比 5NF 的优势在于可以完全避免 NULLS。这对某些（但不是全部）数据库设计人员很重要。我从来没有费心将模式放入 6NF。

In 6NF missing data can be represent by an omitted row, instead of a row with a NULL in some column.

在 6NF 中，缺失的数据可以用省略的行来表示，而不是在某些列中带有 NULL 的行。

There are reasons other than normalization for splitting tables. Sometimes split tables result in better performance. With some database engines, you can get the same performance benefits by partitioning the table instead of actually splitting it. This can have the advantage of keeping the logical design easy to understand, while giving the database engine the tools needed to speed things up.

除了规范化之外，拆分表还有其他原因。有时拆分表会导致更好的性能。使用某些数据库引擎，您可以通过对表进行分区而不是实际拆分来获得相同的性能优势。这可以使逻辑设计易于理解，同时为数据库引擎提供加快速度所需的工具。

回答by Shane Delmore

I use them primarily for a few reasons. One is significant difference in rate of data change. Some of my tables may have audit trails where I track previous versions of records, if I only care to track previous versions of 5 out of 10 columns splitting those 5 columns onto a separate table with an audit trail mechanism on it is more efficient. Also, I may have records (say for an accounting app) that are write only. You can not change the dollar amounts, or the account they were for, if you made a mistake then you need to make a corresponding record to write adjust off the incorrect record, then create a correction entry. I have constraints on the table enforcing the fact that they cannot be updated or deleted, but I may have a couple of attributes for that object that are malleable, those are kept in a separate table without the restriction on modification. Another time I do this is in medical record applications. There is data related to a visit that cannot be changed once it is signed off on, and other data related to a visit that can be changed after signoff. In that case I will split the data and put a trigger on the locked table rejecting updates to the locked table when signed off, but allowing updates to the data the doctor is not signing off on.

我使用它们主要有几个原因。一是数据变化率的显着差异。我的一些表可能有审计跟踪，我可以在其中跟踪以前版本的记录，如果我只关心跟踪 10 列中的 5 列的以前版本，将这 5 列拆分到一个单独的表中，并带有审计跟踪机制，效率会更高。另外，我可能有只写的记录（比如会计应用程序）。您不能更改美元金额或它们的帐户，如果您犯了错误，则需要制作相应的记录以注销不正确的记录，然后创建更正条目。我对表有限制，强制执行它们不能更新或删除的事实，但我可能有该对象的几个可延展的属性，这些保存在一个单独的表中，没有修改的限制。另一次我这样做是在医疗记录应用程序中。有些与访问相关的数据一旦签署就无法更改，而与访问相关的其他数据则可以在签署后更改。在这种情况下，我将拆分数据并在锁定表上放置一个触发器，在注销时拒绝对锁定表的更新，但允许对医生未注销的数据进行更新。

Another poster commented on 1:1 not being normalized, I would disagree with that in some situations, especially subtyping. Say I have an employee table and the primary key is their SSN (it's an example, let's save the debate on whether this is a good key or not for another thread). The employees can be of different types, say temporary or permanent and if they are permanent they have more fields to be filled out, like office phone number, which should only be not null if the type = 'Permanent'. In a 3rd normal form database the column should depend only on the key, meaning the employee, but it actually depends on employee and type, so a 1:1 relationship is perfectly normal, and desirable in this case. It also prevents overly sparse tables, if I have 10 columns that are normally filled, but 20 additional columns only for certain types.

另一位发帖人评论说 1:1 没有被标准化，在某些情况下，我不同意这一点，尤其是子类型。假设我有一个员工表，主键是他们的 SSN（这是一个例子，让我们不再争论这是否是另一个线程的好键）。员工可以是不同类型的，比如临时的或永久的，如果他们是永久的，他们有更多的字段需要填写，比如办公室电话号码，如果 type = 'Permanent'，它应该不为空。在第三范式数据库中，列应该只取决于键，即员工，但它实际上取决于员工和类型，因此 1:1 关系是完全正常的，在这种情况下是可取的。它还可以防止表格过于稀疏，如果我有 10 个通常填充的列，

回答by Erik Funkenbusch

The most common scenario I can think of is when you have BLOB's. Let's say you want to store large images in a database (typically, not the best way to store them, but sometimes the constraints make it more convenient). You would typically want the blob to be in a separate table to improve lookups of the non-blob data.

我能想到的最常见的情况是当你有 BLOB 的时候。假设您想将大图像存储在数据库中（通常，这不是存储它们的最佳方式，但有时限制会使其更方便）。您通常希望 blob 位于单独的表中，以改进对非 blob 数据的查找。

回答by Quassnoi

In terms of pure science, yes, they are useless.

就纯科学而言，是的，它们毫无用处。

In real databases it's sometimes useful to keep a rarely used field in a separate table: to speed up queries using this and only this field; to avoid locks, etc.

在真实的数据库中，将很少使用的字段保存在单独的表中有时很有用：为了加快使用此字段且仅使用此字段的查询；避免锁等。

回答by eleven81

Rather than using views to restrict access to fields, it sometimes makes sense to keep restricted fields in a separate table to which only certain users have access.

与使用视图来限制对字段的访问不同，有时将受限制的字段保存在只有某些用户有权访问的单独表中是有意义的。

回答by HLGEM

1-1 relationships are also necessary if you have too much information. There is a record size limitation on each record in the table. Sometimes tables are split in two (with the most commonly queried information in the main table) just so that the record size will not be too large. Databases are also more efficient in querying if the tables are narrow.

如果您有太多信息，1-1 关系也是必要的。表中的每条记录都有记录大小限制。有时表被分成两部分（最常查询的信息在主表中）只是为了记录大小不会太大。如果表很窄，数据库在查询方面也更有效。