何时在数据库表之间使用一对一关系？
一个数据库设计问题：你什么时候决定使用1对1关系表？
我看到这种情况的地方之一是，例如，当您有一个 User 和 UserProfile 表时，人们会将它们分开，而不是将所有列都放在 User 表中。
从技术上讲，您可以将所有列放在一个表中，因为它们的关系是一对一的。
我知道有人说，对于 UserProfile 表，随着时间的推移，您需要更改表以添加更多列，但我真的不认为这是拆分表的强有力的理由。
那么，如果我要设计一个 User 表和 UserProfile 表，我是否只在一张表中完成它更好？
A DB design question: when do you decide to use 1 to 1 relation tables?
One of the places I see this is, for example, when you have a User and UserProfile table, people split them instead of putting all columns just in a User table.
Technically, you can just put all the columns in one table since their relationship is 1-to-1.
I know someone said that for the UserProfile table, over time you need to alter table to add more columns, but I really don't think this is a strong reason to split the tables.
So, if I'm to design a User table and UserProfile table, is it better for me to just do it in one table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
我唯一一次使用一对一关系是当我希望它多态地属于多个对象时。
例如地址。 一个用户有一个地址，一个商家有一个地址，一家特色餐厅有一个地址。 所有实例都在同一个表中处理，并具有相同的管理代码。 可以将其视为重构数据模型，以便可以在其他地方重用它。
The only time I have used a 1 to 1 relationship is when I want it to polymorphically belong to multiple objects.
Like an address for instance. A user has one address, a business has one address, a featured restaurant has one address. All instances are handled in the same table and has the same code that governs it. Think of it like refactoring your datamodel so you can reuse it in other places.
考虑如何设计业务对象。 您是要拥有一个包含 50 个属性的 User 对象，还是要拥有一个包含一些详细属性的 User 对象，然后是一个包含配置文件其他数据的 Profile 对象？
当表中的数据相关但目的不同时，您应该使用 1 对 1。 （可能措辞可以更好）
而且它可以让事情更容易找到。 没有什么比查看有 75 列的表格更让我讨厌的了。
Think about how you would design the business objects. Are you going to have a User object with 50 properties on it, or are you going to have a User object with a few detail properties, and then a Profile object that contains other data for a profile?
You should use 1-to-1 when the data in the table is related, but isn't there for the same purpose. (probably could be worded better)
Also it can make things easier to find. Not many things I hate more than having to look through a table with 75 columns.
典型的原因是避免可为空的列。
列中包含 NULL 值会使编写清晰（可维护）的 SQL 变得更加困难。 @Ovid 已经写过相关文章此处，借鉴了 Chris Date 的作品。
The classic reason is to avoid nullable columns.
Having a NULL value in a column can make it harder to write clear (maintainable) SQL. @Ovid has written about this here, drawing on the work of Chris Date.
仅当UserProfile表中的字段不需要用户表中的所有记录数时。 例如，如果您有 3,000,000 个用户，但其中只有 3,000 个用户拥有 UserProfiles，则将它们拆分可能是有意义的（以避免一大堆空列。）
尽管现在数据库速度不断提高，存储成本也越来越便宜，但实际上因为这个原因分开它们并没有多大区别......
Only when the fields in the UserProfile table are not required for all the number of records in the user table. For example if you had 3,000,000 users but only 3,000 of those have UserProfiles, it may make sense to split them (to avoid a whole bunch of null columns.)
Although now a days with the increased databases speed and cheap costs of storage, it really doesn't make much of a difference to split them for this reason...
我最近看到一个表，其中有一个表，其中包含大部分数据，然后是另一个表，其中包含大量可选数据。
第二个表有三分之一的行，但列数是其三倍。
这是几年前完成的，以避免列中出现大量空值 - 即空白空间。
但是，如果您现在正在这样做，我会尽量不打扰。 与空旷的空间一起生活。 它给应用程序开发带来的麻烦根本不值得，而且空间比开发时间更便宜。
I've seen one recently where you had one table, with most of the data in, then another table with lots and lots of optional data.
The second table had a third of the rows, but three times as many columns.
This was done a few years ago avoid lots of nulls in columns - i.e. empty space.
However, if you are doing this now, I would tempted not to bother. Live with the empty space. The hassle it can cause to application development simply isn't worth it, and space is cheaper than development time.
这是今天在该线程中出现的另一个问题的直接复制和粘贴，但在这里感觉也很有用。 有吗使用数据库 1:1 关系是否有意义？
我使用它们主要有几个原因。 一是数据变化率发生显着变化。 我的一些表可能有审计跟踪，我可以跟踪以前版本的记录，如果我只关心跟踪 10 列中的 5 列的以前版本，那么将这 5 列拆分到带有审计跟踪机制的单独表上会更有效。 另外，我可能有只写的记录（例如会计应用程序）。 您不能更改美元金额或它们所属的帐户，如果您犯了错误，那么您需要制作相应的记录以注销不正确的记录，然后创建更正分录。 我对表有约束，强制执行它们不能更新或删除的事实，但我可能有几个该对象的可延展属性，这些属性保存在单独的表中，不受修改限制。 我另一次这样做是在医疗记录应用程序中。 有些与访问相关的数据一旦签核后就无法更改，而与访问相关的其他数据可以在签核后更改。 在这种情况下，我将拆分数据并在锁定表上放置一个触发器，拒绝在签署时对锁定表进行更新，但允许对医生未签署的数据进行更新。
另一位发帖者评论说 1:1 没有标准化，在某些情况下我不同意这一点，尤其是子类型。 假设我有一个员工表，主键是他们的 SSN（这是一个例子，让我们不要争论这对于另一个线程来说是否是一个好键）。 员工可以是不同的类型，例如临时员工或永久员工，如果他们是永久员工，则需要填写更多字段，例如办公室电话号码，只有当类型=“永久”时，该字段才应不为空。 在第三范式数据库中，列应该仅取决于键，即员工，但它实际上取决于员工和类型，因此 1:1 关系是完全正常的，在这种情况下也是可取的。 如果我有 10 个正常填充的列，但仅针对某些类型有 20 个附加列，它还可以防止表过于稀疏。
This is a direct copy and paste from another question that popped up today in this thread, but it feels useful here as well. Is there ever a time where using a database 1:1 relationship makes sense?
I use them primarily for a few reasons. One is significant changes in rate of data change. Some of my tables may have audit trails where I track previous versions of records, if I only care to track previous versions of 5 out of 10 columns splitting those 5 columns onto a separate table with an audit trail mechanism on it is more efficient. Also, I may have records (say for an accounting app) that are write only. You can not change the dollar amounts, or the account they were for, if you made a mistake then you need to make a corresponding record to write adjust off the incorrect record, then create a correction entry. I have constraints on the table enforcing the fact that they cannot be updated or deleted, but I may have a couple of attributes for that object that are malleable, those are kept in a separate table without the restriction on modification. Another time I do this is in medical record applications. There is data related to a visit that cannot be changed once it is signed off on, and other data related to a visit that can be changed after signoff. In that case I will split the data and put a trigger on the locked table rejecting updates to the locked table when signed off, but allowing updates to the data the doctor is not signing off on.
Another poster commented on 1:1 not being normalized, I would disagree with that in some situations, especially subtyping. Say I have an employee table and the primary key is their SSN (it's an example, let's save the debate on whether this is a good key or not for another thread). The employees can be of different types, say temporary or permanent and if they are permanent they have more fields to be filled out, like office phone number, which should only be not null if the type = 'Permanent'. In a 3rd normal form database the column should depend only on the key, meaning the employee, but it actually depends on employee and type, so a 1:1 relationship is perfectly normal, and desirable in this case. It also prevents overly sparse tables, if I have 10 columns that are normally filled, but 20 additional columns only for certain types.
这个问题已经得到很好的解决，但我只是添加一个简短的注释来澄清一些对我来说并不明显且尚未明确说明的内容。 1对1关系并不意味着表A中的每条记录在表B中都有1条对应的记录。相反，它意味着对于表A中的每条记录在表B中将有0或1条对应的记录。
Shane D.和其他人描述了利用这一事实的场景。
This has been well addressed but I'll just add a quick note to clarify something that wasn't obvious to me and hasn't been explicitely stated. A 1 to 1 relationship does not mean that for each record in table A there is 1 corresponding record in table B. Instead, it means that for each record in table A there will be 0 or 1 corresponding records in table B.
Shane D. and others describe scenarios that take advantage of this fact.
我认为 Shane D 有一个非常合理的理由。 即使我也遇到过同样的情况，一个大约有 40 列的表，这些列的数据是通过 csv 上传的，仅用于报告目的，一组列用于处理这些经常更新的文件。
因此，如果我们维护一张表作为解决方案。 我们对该表进行频繁更新，并且仅更新 50 列中的 5 列。
我觉得每次更新都会扰乱行分配，并且很有可能出现行链接，因此为了避免行链接，我采用了基于 DML 活动分离数据的方法。
让我知道是否有更好的解决方案
I think Shane D has a reason which is quite valid. As even I came across the same situation for a table having around 40 columns, data for these columns is uploaded through csvs and used only for reporting purpose and a set of columns to process those files,which are frequently updataing.
So If we maintain one table as a solution. We perform frequent updates on that table and will be updating only 5 columns of 50.
I feel every update disturbs the row allocation and there is highly possibility of row-chaining, so to avoid row-chaining, i followed the approach of separating data based on DML-activity.
Let me know if any better solution