我什么时候应该用一对一的关系?

Sorry for that noob question but is there any real needs to use one-to-one relationship with tables in your database? You can implement all necessary fields inside one table. Even if data becomes very large you can enumerate column names that you need in SELECT statement instead of using SELECT *. When do you really need this separation?

对于noob这个问题，我感到很抱歉，但是您的数据库中是否真的需要使用与表的一对一关系呢?您可以在一个表中实现所有必要的字段。即使数据变得很大，您也可以在SELECT语句中枚举所需的列名，而不是使用SELECT *。什么时候你真的需要这种分离?

12 个解决方案

#1

1 to 0..1

• The "1 to 0..1" between super and sub-classes is used as a part of "all classes in separate tables" strategy for implementing inheritance.

  “0 . . 1在父类和子类之间使用1作为实现继承的“所有表中的类”策略的一部分。

• A "1 to 0..1" can be represented in a single table with "0..1" portion covered by NULL-able fields. However, if the relationship is mostly "1 to 0" with only a few "1 to 1" rows, splitting-off the "0..1" portion into a separate table might save some storage (and cache performance) benefits. Some databases are thriftier at storing NULLs than others, so a "cut-off point" where this strategy becomes viable can vary considerably.

  “1 - 0 . .“1”可以用“0”表示在一个表中。1“可空字段覆盖的部分。但是，如果关系主要是“1到0”，只有少数“1到1”行，则将“0”分割开。1“将部分放入单独的表中可以节省一些存储(和缓存性能)好处。有些数据库在存储NULLs方面比其他数据库更灵活，因此这种策略变得可行的“截止点”可能会有很大的不同。

1 to 1

• The real "1 to 1" vertically partitions the data, which may have implications for caching. Databases typically implement caches at the page level, not at the level of individual fields, so even if you select only a few fields from a row, typically the whole page that row belongs to will be cached. If a row is very wide and the selected fields relatively narrow, you'll end-up caching a lot of information you don't actually need. In a situation like that, it may be useful to vertically partition the data, so only the narrower, more frequently used portion or rows gets cached, so more of them can fit into the cache, making the cache effectively "larger".

  真正的“1到1”垂直划分数据，这可能对缓存有影响。数据库通常在页面级实现缓存，而不是在单个字段级实现缓存，因此即使您从一行中只选择几个字段，通常也会缓存该行所属的整个页面。如果一行非常宽，并且选择的字段相对较窄，那么您将结束缓存大量您实际上并不需要的信息。在这种情况下，垂直地划分数据可能是有用的，因此只有更窄的、更经常使用的部分或行才会被缓存，因此更多的数据可以装入缓存，从而有效地使缓存“更大”。

• Another use of vertical partitioning is to change the locking behavior: databases typically cannot lock at the level of individual fields, only the whole rows. By splitting the row, you are allowing a lock to take place on only one of its halfs.

  垂直分区的另一个用途是改变锁定行为:数据库通常不能锁定单个字段的级别，只能锁定整个行。通过分割行，您只允许在其中一个halfs上发生锁。

• Triggers are also typically table-specific. While you can theoretically have just one table and have the trigger ignore the "wrong half" of the row, some databases may impose additional limits on what a trigger can and cannot do that could make this impractical. For example, Oracle doesn't let you modify the mutating table - by having separate tables, only one of them may be mutating so you can still modify the other one from your trigger.

  触发器通常也是特定于表的。虽然理论上，您可以只有一个表，并让触发器忽略行中的“错误部分”，但是有些数据库可能会对触发器能够做什么和不能做什么施加额外的限制，从而使这一操作变得不切实际。例如，Oracle不允许您修改可变表——通过拥有单独的表，其中只有一个可能正在发生变化，因此您仍然可以从触发器中修改另一个表。

• Separate tables may allow more granular security.

  单独的表可能允许更细粒度的安全性。

These considerations are irrelevant in most cases, so in most cases you should consider merging the "1 to 1" tables into a single table.

这些考虑在大多数情况下是无关的，所以在大多数情况下，您应该考虑将“1到1”表合并到一个表中。

#2

If data in one table is related to, but does not 'belong' to the entity described by the other, then that's a candidate to keep it separate.

如果一个表中的数据与另一个表中描述的实体有关，但不属于该实体，那么就应该将其分开。

This could provide advantages in future, if the separate data needs to be related to some other entity, also.

如果单独的数据也需要与其他实体相关联，这将在未来提供优势。

#3

The most sensible time to use this would be if there were two separate concepts that would only ever relate in this way. For example, a Car can only have one current Driver, and the Driver can only drive one car at a time - so the relationship between the concepts of Car and Driver would be 1 to 1. I accept that this is contrived example to demonstrate the point.

如果有两个单独的概念只会以这种方式联系起来，那么使用它的最合理的时间就是。例如，一辆车只能有一辆当前的司机，而司机一次只能开一辆车，所以汽车和司机的概念之间的关系是1比1。我承认这是人为设计的例子来证明这一点。

Another reason is that you want to specialize a concept in different ways. If you have a Person table and want to add the concept of different types of Person, such as Employee, Customer, Shareholder - each one of these would need different sets of data. The data that is similar between them would be on the Person table, the specialist information would be on the specific tables for Customer, Shareholder, Employee.

另一个原因是您希望以不同的方式专门化一个概念。如果您有一个Person表，并希望添加不同类型的人员的概念，例如员工、客户、股东——每个人都需要不同的数据集。他们之间相似的数据将在Person表上，专业信息将在客户、股东、员工的特定表上。

Some database engines struggle to efficiently add a new column to a very large table (many rows) and I have seen extension-tables used to contain the new column, rather than the new column being added to the original table. This is one of the more suspect uses of additional tables.

一些数据库引擎难以有效地将一个新列添加到一个很大的表(许多行)中，并且我已经看到了用于包含新列的扩展表，而不是将新列添加到原始表中。这是其他表更可疑的用途之一。

You may also decide to divide the data for a single concept between two different tables for performance or readability issues, but this is a reasonably special case if you are starting from scratch - these issues will show themselves later.

您还可以决定将数据划分为两个不同的表，以实现性能或可读性问题，但这是一个相当特殊的情况，如果您从头开始——这些问题将稍后显示。

#4

If you place two one-to-one tables in one, its likely you'll have semantics issue. For example, if every device has one remote controller, it doesn't sound quite good to place the device and the remote controller with their bunch of characteristics in one table. You might even have to spend time figuring out if a certain attribute belongs to the device or the remote controller.

如果将两个一对一的表放在一个表中，很可能会出现语义问题。例如，如果每个设备都有一个远程控制器，那么将设备和具有这些特性的远程控制器放在一个表中听起来不是很好。您甚至可能需要花费时间来确定某个属性是否属于设备或远程控制器。

There might be cases, when half of your columns will stay empty for a long while, or will not ever be filled in. For example, a car could have one trailer with a bunch of characteristics, or might have none. So you'll have lots of unused attributes.

可能会有这样的情况，当你的专栏有一半会空着很长一段时间，或者永远不会被填满。例如，一辆车可以有一辆有很多特点的拖车，也可以没有。所以你会有很多没用的属性。

If your table has 20 attributes, and only 4 of them are used occasionally, it makes sense to break the table into 2 tables for performance issues.

如果您的表有20个属性，并且偶尔只使用其中的4个，那么出于性能问题，将表分解为2个表是有意义的。

In such cases it isn't good to have everything in one table. Besides, it isn't easy to deal with a table that has 45 columns!

在这种情况下，把所有东西都放在一个表中是不好的。此外，处理一个有45列的表并不容易!

#5

not very often.

不是很经常。

you may find some benefit if you need to implement some security - so some users can see some of the columns (table1) but not others (table2)..

如果您需要实现一些安全性，您可能会发现一些好处——这样一些用户可以看到一些列(表1)，但是其他列(表2)。

of course some databases (Oracle) allow you to do this kind of security in the same table, but some others may not.

当然，有些数据库(Oracle)允许您在同一个表中执行这种安全性，但有些可能不允许。

#6

You are referring to database normalization. One example that I can think of in an application that I maintain is Items. The application allows the user to sell many different types of items (i.e. InventoryItems, NonInventoryItems, ServiceItems, etc...). While I could store all of the fields required by every item in one Items table, it is much easier to maintain to have a base Item table that contains fields common to all items and then separate tables for each item type (i.e. Inventory, NonInventory, etc..) which contain fields specific to only that item type. Then, the item table would have a foreign key to the specific item type that it represents. The relationship between the specific item tables and the base item table would be one-to-one.

您指的是数据库规范化。我在维护的应用程序中可以想到的一个例子是项。应用程序允许用户销售许多不同类型的项目(例如，InventoryItems, NonInventoryItems, ServiceItems，等等)。虽然我可以存储的所有字段所需的每一项一项表格,更容易保持有一个基本项表,包含所有项目的公共字段,然后单独的表为每个项目类型(例如库存、NonInventory等. .)这只包含字段特定项目类型。然后，项目表将具有它表示的特定项目类型的外键。特定项表和基本项表之间的关系是一对一的。

Below, is an article on normalization.

下面是一篇关于规范化的文章。

http://support.microsoft.com/kb/283878

http://support.microsoft.com/kb/283878

#7

As with all design questions the answer is "it depends."

就像所有的设计问题一样，答案是“视情况而定”。

There are few considerations:

有几个注意事项:

• how large will the table get (both in terms of fields and rows)? It can be inconvenient to house your users' name, password with other less commonly used data both from a maintenance and programming perspective

  表将会有多大(包括字段和行)?从维护和编程的角度来看，使用其他不太常用的数据来存放用户的姓名、密码可能会很不方便

• fields in the combined table which have constraints could become cumbersome to manage over time. for example, if a trigger needs to fire for a specific field, that's going to happen for every update to the table regardless of whether that field was affected.

  合并表中具有约束的字段随着时间的推移可能变得难以管理。例如，如果一个触发器需要为一个特定的字段触发，那么无论该字段是否受到影响，它都将在对表的每个更新中发生。

• how certain are you that the relationship will be 1:1? As This question points out, things get can complicated quickly.

  你有多确定这段关系会是1:1?正如这个问题所指出的，事情会很快变得复杂。

#8

Another use case can be the following: you might import data from some source and update it daily, e.g. information about books. Then, you add data yourself about some books. Then it makes sense to put the imported data in another table than your own data.

另一个用例可以是:您可以从某些来源导入数据并每天更新它，例如有关书籍的信息。然后，你自己添加一些书的数据。然后，将导入的数据放在另一个表中而不是您自己的数据中是有意义的。

#9

My 2 cents.

我的2美分。

I work in a place where we all develop in a large application, and everything is a module. For example, we have a users table, and we have a module that adds facebook details for a user, another module that adds twitter details to a user. We could decide to unplug one of those modules and remove all its functionality from our application. In this case, every module adds their own table with 1:1 relationships to the global users table, like this:

我工作的地方，我们都在一个大的应用程序中开发，所有的东西都是一个模块。例如，我们有一个用户表，我们有一个为用户添加facebook细节的模块，另一个向用户添加twitter细节的模块。我们可以决定去掉其中一个模块，并从应用程序中删除它的所有功能。在这种情况下，每个模块都向全局用户表添加自己的具有1:1关系的表，如下所示:

create table users ( id int primary key, ...);
create table users_fbdata ( id int primary key, ..., constraint users foreighn key ...)
create table users_twdata ( id int primary key, ..., constraint users foreighn key ...)

#10

I normally encounter two general kinds of 1:1 relationship in practice:

我在实践中经常遇到两种一般的1:1关系:

1. IS-A relationships, also known as supertype/subtype relationships. This is when one kind of entity is actually a type of another entity (EntityA IS A EntityB). Examples:

   IS-A关系，也称为超类型/子类型关系。这是当一种实体实际上是另一种实体的类型时(EntityA是EntityB)。例子:

   • Person entity, with separate entities for Accountant, Engineer, Salesperson, within the same company.
   • 个人实体，在同一公司内有独立的会计、工程师、销售人员实体。
   • Item entity, with separate entities for Widget, RawMaterial, FinishedGood, etc.
   • 项目实体，具有小部件、原材料、成品等单独的实体。
   • Car entity, with separate entities for Truck, Sedan, etc.
   • 汽车实体，有单独的实体为卡车，轿车等。

   In all these situations, the supertype entity (e.g. Person, Item or Car) would have the attributes common to all subtypes, and the subtype entities would have attributes unique to each subtype. The primary key of the subtype would be the same as that of the supertype.

   在所有这些情况下，超类型实体(例如Person、Item或Car)将具有所有子类型的公共属性，而子类型实体将具有每个子类型的惟一属性。子类型的主键将与父类型的主键相同。

2. "Boss" relationships. This is when a person is the unique boss or manager or supervisor of an organizational unit (department, company, etc.). When there is only one boss allowed for an organizational unit, then there is a 1:1 relationship between the person entity that represents the boss and the organizational unit entity.

   “老板”的关系。这是指一个人是组织单位(部门、公司等)唯一的老板或经理或主管。当一个组织单元只允许一个boss时，代表boss的person实体与组织单元实体之间存在1:1的关系。

#11

In my time of programming i encountered this only in one situation. Which is when there is a 1-to-many and an 1-to-1 relationship between the same 2 entities ("Entity A" and "Entity B").

在我编程的时候，我只在一种情况下遇到过这种情况。即同一两个实体(“实体a”和“实体B”)之间存在一比多和一比一的关系。

When "Entity A" has multiple "Entity B" and "Entity B" has only 1 "Entity A" and "Entity A" has only 1 current "Entity B" and "Entity B" has only 1 "Entity A".

当“实体A”有多个“实体B”，“实体B”只有一个“实体A”，“实体A”只有一个当前的“实体B”，“实体B”只有一个“实体A”。

For example, a Car can only have one current Driver, and the Driver can only drive one car at a time - so the relationship between the concepts of Car and Driver would be 1 to 1. - I borrowed this example from @Steve Fenton's answer

例如，一辆车只能有一辆当前的司机，而司机一次只能开一辆车，所以汽车和司机的概念之间的关系是1比1。-我从@Steve Fenton的回答中借用了这个例子

Where a Driver can drive multiple Cars, just not at the same time. So the Car and Driver entities are 1-to-many or many-to-many. But if we need to know who the current driver is, then we also need the 1-to-1 relation.

一个司机可以开多辆车，只是不能同时开。所以汽车和司机实体是1对多或多对多。但如果我们需要知道当前的驱动程序是谁，那么我们还需要1比1的关系。

#12

First, I think it is a question of modelling and defining what consist a separate entity. Suppose you have customers with one and only one single address. Of course you could implement everything in a single table customer, but if, in the future you allow him to have 2 or more addresses, then you will need to refactor that (not a problem, but take a conscious decision).

首先，我认为这是一个建模和定义组成独立实体的问题。假设您的客户只有一个地址。当然，您可以在一个表客户中实现所有内容，但是如果将来您允许他拥有两个或多个地址，那么您将需要重构这个地址(不是问题，但要做出有意识的决定)。

I can also think of an interesting case not mentioned in other answers where splitting the table could be useful:

我还能想到一个有趣的情况，在其他答案中没有提到，分桌子可能有用:

Imagine, again, you have customers with a single address each, but this time it is optional to have an address. Of course you could implement that as a bunch of NULL-able columns such as ZIP,state,street. But suppose that given that you do have an address the state is not optional, but the ZIP is. How to model that in a single table? You could use a constraint on the customer table, but it is much easier to divide in another table and make the foreign_key NULLable. That way your model is much more explicit in saying that the entity address is optional, and that the ZIP is an optional attribute of that entity.

再一次，想象一下，您的客户都只有一个地址，但这一次，有一个地址是可选的。当然，您可以将其实现为一系列可空列，如ZIP、state和street。但是假设您有一个地址，这个状态不是可选的，但是ZIP是可选的。如何在一个表中建模?您可以在customer表上使用约束，但是在另一个表中划分并使foreign_key为NULLable要容易得多。这样，您的模型就更明确地表示实体地址是可选的，而ZIP是该实体的可选属性。