我什么时候应该使用一对一的关系? [英] When I should use one to one relationship?
问题描述
抱歉问那个菜鸟问题，但是否真的需要使用与数据库中的表的一对一关系?您可以在一张表中实现所有必要的字段.即使数据变得非常大，您也可以在 SELECT 语句中枚举您需要的列名，而不是使用 SELECT *.你什么时候真的需要这种分离?
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
1 到 0..1"超类和子类之间的所有类在单独的表中"被用作单独表中的所有类"的一部分.实现继承的策略.
1 到 0..1"可以在单个表中用0..1"表示由可为 NULL 的字段覆盖的部分.但是，如果关系主要1 到 0"只有几个1对1"行，拆分0..1"；部分放入单独的表中可能会节省一些存储(和缓存性能)优势.一些数据库在存储 NULL 方面比其他数据库更节俭，因此截止点"需要注意.这种策略在何处变得可行可能会有很大差异.
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对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.
触发器通常也是特定于表的.虽然理论上你可以只有一张桌子并且触发器忽略错误的一半"在这一行中，一些数据库可能会对触发器可以做什么和不能做什么施加额外的限制，这可能会使这变得不切实际.例如，Oracle 不允许您修改变异表 - 通过拥有单独的表，其中只有一个可能发生变异，因此您仍然可以通过触发器修改另一个表.
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.
这些注意事项在大多数情况下是无关紧要的，因此在大多数情况下，您应该考虑合并1 对 1"表合并为一个表.
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屋！
