当我应该使用一对一的关系？ [英] When I should use one to one relationship?

问题描述

对于这个noob问题，但是有什么真正的需要使用一对一的关系，你的数据库中的表？您可以在一个表中实现所有必要的字段。即使数据变得非常大，您也可以在 SELECT 语句中枚举您需要的列名称，而不是使用 SELECT * 。你真的需要这种分离吗？

解决方案

1到0..1

• 超类和子类之间的1到0..1用作实现继承。




• 1到0..1在单个表中表示为由可NULL字段覆盖的0..1部分。但是，如果关系大多是1到0，只有少数1到1行，则将0..1部分拆分为单独的表可能会节省一些存储空间和缓存性能）好处。一些数据库比其他数据库更容易存储NULL，因此这个策略变得可行的截止点可能有很大的不同。

1到1

• 实际的1到1垂直分割数据，这可能对缓存有影响。数据库通常在页面级别（而不是单个字段级别）实现高速缓存，因此即使从行中只选择了几个字段，通常该行所属的整个页面也将被缓存。如果一个行非常宽，并且所选择的字段相对较窄，那么你最终会缓存很多你实际上不需要的信息。在这样的情况下，垂直分割数据可能是有用的，因此只有较窄的，更频繁使用的部分或行被高速缓存，因此更多的可以适合于高速缓存，使得高速缓存




• 垂直分区的另一个用途是改变锁定行为：数据库通常不能锁定在单个字段的级别， 。




• 触发器通常也是针对特定表格的。虽然理论上可以只有一个表，并且触发器忽略该行的错误的一半，但是一些数据库可能对触发器可以和不能做的额外限制可能使这不切实际。例如，Oracle不允许修改变异表 - 通过使用单独的表，只有其中一个变异，所以您仍然可以修改触发器中的另一个。




• 这些注意事项在大多数情况下都是不相关的，因此，这些注意事项可能会导致更严谨的安全性。

<在大多数情况下，您应该考虑将1：1表合并为单个表。

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?

解决方案

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.

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

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

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

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

这篇关于当我应该使用一对一的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！