是否曾经有过使用数据库 1:1 关系有意义的时候? [英] Is there ever a time where using a database 1:1 relationship makes sense?

问题描述

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

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.

• 姓名:SSN?我会把它们放在同一张桌子上.
• PersonID:AddressID?再次，同一张桌子.

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

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

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

我是否遗漏了一些明显的东西?

Am I missing something obvious?

推荐答案

1:1 关系通常表示您出于某种原因对更大的实体进行了分区.通常是由于物理模式中的性能原因，但如果预计大量数据同时是未知的"(在这种情况下，您有一个 1:0或 1:1，但不能更多).

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).

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

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 whenever you have queries that need consistent subsets of a larger entity.

这篇关于是否曾经有过使用数据库 1:1 关系有意义的时候?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！