为什么在数据库设计中使用1对1关系？ [英] Why use a 1-to-1 relationship in database design?

问题描述

我很难想出在数据库设计中何时使用1对1的关系，或者如果有必要的话。

I am having a hard time trying to figure out when to use a 1-to-1 relationship in db design or if it is ever necessary.

如果你可以只选择您在查询中需要的列是否有点将表分解为1对1关系。我想更新一个大表对性能的影响比较小的表，我敢肯定，这取决于表对某些操作（读/写）的使用有多重。

If you can select only the columns you need in a query is there ever a point to break up a table into 1-to-1 relationships. I guess updating a large table has more impact on performance than a smaller table and I'm sure it depends on how heavily the table is used for certain operations (read/ writes)

因此，在设计数据库模式时，如何考虑1对1关系？你使用什么标准来确定你是否需要一个，以及没有使用它的好处是什么？

So when designing a database schema how do you factor in 1-to-1 relationships? What criteria do you use to determine if you need one, and what are the benefits over not using one?

谢谢！

推荐答案

从逻辑的角度来看，1：1关系应该总是合并到一个表中。

From the logical standpoint, a 1:1 relationship should always be merged into a single table.

另一方面，此类垂直分区可能存在物理注意事项>或行拆分，尤其是如果您知道您将更频繁地访问某些列或以不同的模式访问其他列，例如：

On the other hand, there may be physical considerations for such "vertical partitioning" or "row splitting", especially if you know you'll access some columns more frequently or in different pattern than the others, for example:

• 您可能需要群集或分区 1：1关系的两个端点表格不同。


• 如果您的DBMS允许，您可能希望将它们放在不同的物理磁盘上（例如在SSD上的性能更为关键，而在便宜的HDD上则更高）。


• 您已经测量了对缓存的影响，并且希望确保热列保存在缓存中，没有冷列污染它。


• 您需要一个比整行窄的并发行为（例如锁定）。这是高度DBMS特定的。


• 您需要不同列的不同安全性，但是您的DBMS不支持列级权限。


• 触发器通常是表特定的。虽然理论上可以只有一个表，并且触发器忽略该行的错误的一半，但是一些数据库可能会对触发器可以和不能执行的操作施加额外的限制。例如，Oracle不允许您从行级触发器修改所谓的mutating表 - 通过具有单独的表，只有其中一个可能是突变，所以您仍然可以修改触发器中的另一个（但有其他方法来解决此问题。）

• You might want to cluster or partition the two "endpoint" tables of a 1:1 relationship differently.
• If your DBMS allows it, you might want to put them on different physical disks (e.g. more performance-critical on an SSD and the other on a cheap HDD).
• You have measured the effect on caching and you want to make sure the "hot" columns are kept in cache, without "cold" columns "polluting" it.
• You need a concurrency behavior (such as locking) that is "narrower" than the whole row. This is highly DBMS-specific.
• You need different security on different columns, but your DBMS does not support column-level permissions.
• Triggers are 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. For example, Oracle doesn't let you modify the so called "mutating" table from a row-level trigger - by having separate tables, only one of them may be mutating so you can still modify the other from your trigger (but there are other ways to work-around that).

数据库在处理数据方面非常出色，因此我不会为了更新性能而拆分表，除非基于代表性数据量的基准，并得出结论，性能差异实际上存在并且足够大（例如，以抵消对JOINing的增加的需求）。

Databases are very good at manipulating the data, so I wouldn't split the table just for the update performance, unless you have performed the actual benchmarks on representative amounts of data and concluded the performance difference is actually there and significant enough (e.g. to offset the increased need for JOINing).

另一方面，如果你正在谈论1：0或1（而不是真正的1：1），这是一个完全不同的问题，应该得到不同的答案...

On the other hand, if you are talking about "1:0 or 1" (and not a true 1:1), this is a different question entirely, deserving a different answer...

这篇关于为什么在数据库设计中使用1对1关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！