36

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?

Nakilon
  • 34,866
  • 14
  • 107
  • 142
chobo
  • 31,561
  • 38
  • 123
  • 191
  • 4
    http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense pretty much sums it up. – Willem van Rumpt Mar 13 '12 at 16:33
  • I know this is an old question, but I have many examples of legitimate 1:1 relationships, for most of which they are the ONLY correct solution: http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense/28313151#28313151 – Tripartio Jan 18 '16 at 19:41

6 Answers6

60

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:

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

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

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

See also: When I should use one to one relationship?

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • From what I have gathered in the answers it seems that 1:1 should be used mainly for performance optimization, or security. I like the idea of using a supertype / subtype, but from my understanding that is considered a 1:0 or 1. – chobo Mar 14 '12 at 18:48
  • I think the first sentence needs some explanation/reasoning. Why wouldn't an address be an actual separate entity logically just because we're simplifying it to "1 user 1 address" in our model? – user3738870 Aug 26 '22 at 11:12
  • @user3738870 I simply meant that 1:1 entity rows behave as a single table row. You cannot have one entity row without the other, just as you cannot have one "half" of the table row without the other. – Branko Dimitrijevic Aug 27 '22 at 04:57
  • I see, I think "should" is too strong in that sentence, "could" would be better. – user3738870 Aug 27 '22 at 09:40
10

Separation of duties and abstraction of database tables.

If I have a user and I design the system for each user to have an address, but then I change the system, all I have to do is add a new record to the Address table instead of adding a brand new table and migrating the data.

EDIT

Currently right now if you wanted to have a person record and each person had exactly one address record, then you could have a 1-to-1 relationship between a Person table and an Address table or you could just have a Person table that also had the columns for the address.

In the future maybe you made the decision to allow a person to have multiple addresses. You would not have to change your database structure in the 1-to-1 relationship scenario, you only have to change how you handle the data coming back to you. However, in the single table structure you would have to create a new table and migrate the address data to the new table in order to create a best practice 1-to-many relationship database structure.

bdparrish
  • 3,216
  • 3
  • 37
  • 58
  • If you "add a record" to `address` table without adding a record to `user` table, this is no longer a 1:1 relationship. – Branko Dimitrijevic Mar 13 '12 at 16:34
  • @BrankoDimitrijevic, the original explanation obviously did not get my point across clearly. I only meant if you data strategy changed in the future for each person to have multiple addresses, then all you would have to do is add a new record to the Address table and now you have a 1-to-Many relationship between Person and Address. – bdparrish Mar 13 '12 at 17:39
  • @bdparrish Yes, the point about evolution is valid. If a current 1:1 relationship is expected to become 1:N (or even just "1:0 or 1") in the future, then "pre-splitting" the table may be justified. +1 from me on flashing this point out. – Branko Dimitrijevic Mar 13 '12 at 17:51
5

Well, on paper, normalized form looks to be the best. In real world usually it is a trade-off. Most large systems that I know do trade-offs and not trying to be fully normalized.

I'll try to give an example. If you are in a banking application, with 10 millions passbook account, and the usual transactions will be just a query of the latest balance of certain account. You have table A that stores just those information (account number, account balance, and account holder name).

Your account also have another 40 attributes, such as the customer address, tax number, id for mapping to other systems which is in table B.

A and B have one to one mapping.

In order to be able to retrieve the account balance fast, you may want to employ different index strategy (such as hash index) for the small table that has the account balance and account holder name.

The table that contains the other 40 attributes may reside in different table space or storage, employ different type of indexing, for example because you want to sort them by name, account number, branch id, etc. Your system can tolerate slow retrieval of these 40 attributes, while you need fast retrieval of your account balance query by account number.

Having all the 43 attributes in one table seems to be natural, and probably 'naturally slow' and unacceptable for just retrieving single account balance.

Daniel Baktiar
  • 1,692
  • 11
  • 22
  • 1
    This is where I get confused. In your example you have a table with 43 attributes and you extract 3 attributes into a new table for faster querying. Couldn't you write a query that just selects the three columns from the large table? Is there a performance difference if you query the table with 3 attributes vs the one wit 43, but only select three of the fields? – chobo Mar 13 '12 at 17:04
  • Oh just re-read. It's mainly for putting them on different storage devices – chobo Mar 13 '12 at 17:06
  • Likewise where you're using BLOBs and CLOBs. You often isolate them in their own related table to avoid overhead on columns that don't involve them. – wmorrison365 Mar 13 '12 at 17:15
  • 1
    @wmorrison365 - Assuming I have a table with blob images and I don't select them when querying that table, is there still gonna be a lot of overhead from the blob column. Or are you referring to updating and inserting on a table that has that datatype? – chobo Mar 13 '12 at 17:57
2

Often people are talking about a 1:0..1 relationship and call it a 1:1. In reality, a typical RDBMS cannot support a literal 1:1 relationship in any case.

As such, I think it's only fair to address sub-classing here, even though it technically necessitates a 1:0..1 relationship, and not the literal concept of a 1:1.

A 1:0..1 is quite useful when you have fields that would be exactly the same among several entities/tables. For example, contact information fields such as address, phone number, email, etc. that might be common for both employees and clients could be broken out into an entity made purely for contact information.

A contact table would hold common information, like address and phone number(s).

So an employee table holds employee specific information such as employee number, hire date and so on. It would also have a foreign key reference to the contact table for the employee's contact info.

A client table would hold client information, such as an email address, their employer name, and perhaps some demographic data such as gender and/or marital status. The client would also have a foreign key reference to the contact table for their contact info.

In doing this, every employee would have a contact, but not every contact would have an employee. The same concept would apply to clients.

James Marks
  • 341
  • 2
  • 6
2

It makes sense to use 1-1 relationships to model an entity in the real world. That way, when more entities are added to your "world", they only also have to relate to the data that they pertain to (and no more).

That's the key really, your data (each table) should contain only enough data to describe the real-world thing it represents and no more. There should be no redundant fields as all make sense in terms of that "thing". It means that less data is repeated across the system (with the update issues that would bring!) and that you can retrieve individual data independently (not have to split/ parse strings for example).

To work out how to do this, you should research "Database Normalisation" (or Normalization), "Normal Form" and "first, second and third normal form". This describes how to break down your data. A version with an example is always helpful. Perhaps try this tutorial.

wmorrison365
  • 5,995
  • 2
  • 27
  • 40
  • The real 1:1 relationship _is_ "a single object in the real world". You are probably talking about "1 to 0 or 1". – Branko Dimitrijevic Mar 13 '12 at 16:57
  • I was just reading about the supertype subtype pattern. That seems like a pretty good use for a 1-to-1 table – chobo Mar 13 '12 at 17:01
  • @chobo This is not 1:1, unless there is exactly one subtype. – Branko Dimitrijevic Mar 13 '12 at 17:07
  • I just meant if you have an entity A and an entity B, you could group them in one table. But, then entity C comes along that only pertains to the data items (columns) that would have been present in entity B then it has no choice but to be related to the whole grouped table (even though it has no interest in the entity A columns). – wmorrison365 Mar 13 '12 at 17:14
  • @wmorrison365 I'm not sure what you mean by "pertains", but if the relationship between C and B is true 1:1 **and** the relationship between B and A is true 1:1, then the relationship between C and A must also be 1:1. So, you can't have _either_ one of them without having _all_ of them. This is pretty much what "being a single object" means. – Branko Dimitrijevic Mar 13 '12 at 17:30
  • @BrankoDimitrijevic, what if relationship between A and B is 1:1 and the relationship between B and C is 1:Many? Now A HAS to have a 1:Many relationship with C. – bdparrish Mar 13 '12 at 17:35
  • @bdparrish Exactly. Whether A and B are merged or not, they are both in the 1:N relationship to C. On the physical level however, you'll typically enforce that through just one FOREIGN KEY (e.g. from C to B). – Branko Dimitrijevic Mar 13 '12 at 17:46
  • LOL, I'm completely lost :) So the example isn't a supertype / subtype, or a supertype / subtype isn't a 1:1? – chobo Mar 13 '12 at 18:00
  • @chobo Both. We were not talking about supertype/subtype and supertype/subtype is not generally 1:1. A discriminating category hierarchy is 1:1 only if there is exactly one subtype. If there is more than one subtype, then when you "instantiate an object", you'll insert a row in the parent table and _one_ of the child tables. Since you did not insert in _all_ child tables, this is "1 to 0 or 1" and not exactly "1 to 1". A non-discriminating category hierarchy _may_ theoretically be 1:1 (if you _always_ insert to parent and all child tables), but this pretty much defeats its purpose... – Branko Dimitrijevic Mar 13 '12 at 18:54
  • This is a confusing answer. Real world representation is done in an Entity-Relationship diagram (ER), not the relational model. The OP question is about the mapping from the ER diagram to the relational model and the latter often has tables which do not map to world entities (eg a M-N relationship table). Also, your recommendations about normalization are generic and do not relate to this specific question. The most voted answer summarizes much better the motivations to create distinct tables for a 1:1 relationship: performance and security. – Alan Evangelista Nov 14 '18 at 11:31
1

Just a few samples from past projects:

  • a TestRequests table can have only one matching Report. But depending on the nature of the Request, the fields in the Report may be totally different.
  • in a banking project, an Entities table hold various kind of entities: Funds, RealEstateProperties, Companies. Most of those Entities have similar properties, but Funds require about 120 extra fields, while they represent only 5% of the records.
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Both these examples are not "1:1", but "1:0 or 1". – Branko Dimitrijevic Mar 13 '12 at 17:10
  • @Branko: that is perfectly right, but then I don't see any use for a real 1:1. Furthermore, I do not see how referential integrity could be enforced for a real 1:1 ! One side MUST be saved alone before the other one can be checked. – iDevlop Mar 13 '12 at 20:02
  • This sort of bi-directional foreign key is discussed here. http://stackoverflow.com/questions/1607916/bidirectional-foreign-key-constraint – sam yi Mar 13 '12 at 23:47
  • @iDevlop Please see [my answer](http://stackoverflow.com/a/9688442/533120) for some uses. The "true" 1:1 is typically enforced by deferring FOREIGN KEY constraints, but that's not supported by all DBMSes (most notably MS SQL Server). It can be emulated to some degree by using different PKs in the two tables (by introducing a surrogate PK if necessary), then allowing one of the FK child endpoints to be NULL-able (to break the insert cycle), and then protecting the non-NULL from reverting back to NULL by a trigger. It's usually better to live with 1:0..1 and enforce 1:1 at the application level. – Branko Dimitrijevic Mar 14 '12 at 00:32
  • @Branko: +1 for your reply. Which known RDBMS is supporting that 1:1 ? – iDevlop Mar 14 '12 at 06:41
  • @iDevlop Of the top of my head, Oracle and PostgreSQL support deferred constraints. I _believe_ IBM DB2, MySQL and Interbase/Firebird don't, but I'd have to double-check that. – Branko Dimitrijevic Mar 14 '12 at 07:52