When to use 1-to-1 relationships between database

问题:

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?

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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

回答5:

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.

回答6:

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.

回答7:

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.

回答8:

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