为什么在数据库设计中使用一对一关系? [英] 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.

如果您只能在查询中选择您需要的列，那么将表分解为一对一关系是否有任何意义.我想更新一个大表比一个小表对性能的影响更大，我确定这取决于该表用于某些操作(读/写)的程度

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)

那么在设计数据库模式时，您如何考虑一对一关系?您使用什么标准来确定是否需要一个?不使用一个的好处是什么?

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 不允许您修改所谓的变异"来自行级触发器的表 - 通过拥有单独的表，其中只有一个可能会发生变异，因此您仍然可以从触发器中修改另一个(但有 其他方法来解决这个问题).

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

另见:什么时候应该使用一对一的关系?

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