What are advantages of using a one-to-one table relationship? (MySQL)

Question

What are advantages of using a one-to-one table relationship as opposed to simply storing all the data in one table? I understand and make use of one-to-many, many-to-one, and many-to-many all the time, but implementing a one-to-one relationship seems like a tedious and unnecessary task, especially if you use naming conventions for relating (php) objects to database tables.

I couldn't find anything on the net or on this site that could supply a good real-world example of a one-to-one relationship. At first I thought it might be logical to separate 'users', for example, into two tables, one containing public information like an 'about me' for profile pages and one containing private information such as login/password, etc. But why go through all the trouble of using unnecessary JOINS when you can just choose which fields to select from that table anyway? If I'm displaying the user's profile page, obviously I would only SELECT id,username,email,aboutme etc. and not the fields containing their private info.

Anyone care to enlighten me with some real-world examples of one-to-one relationships?

Edit: Thank you for the good replies so far.

I'd like to add to my question, is a one-to-one good for representing inheritance? In my site, I'm planning to have articles also behave as forum topics, i.e. they will possess more information when viewed on the 'articles' page, but can also be viewed as a forum topic (simply with a text preview linking to the main article) in a specific subforum to facilitate commenting.

So in the forum view, I would only select from the 'topics' table, but in the 'article' view I would select from both?

Answer 1

One possible use is when part of the information is optional. This way you don't need to have a bunch of nullable fields in one big table, but can separate it logically into the mandatory table and an optional table.

Other use is when some of the data is shared with different tables. For instance let's say you have a site where you sell computer parts. You could put the details that all components share into eg. "parts" table, but put the specifics in "motherboards", "cpus", etc. which would just use parts table with one-to-one relation.

Answer 2

The database engine may have to load entire rows into memory in order to pull data from them, even if only a subset of fields are being read. Fewer fields per row means more rows per page which means fewer disk accesses.

Answer 3

Here's two, I'm sure others will post more

• you want to extend an existing table without actually modifying the table. Perhaps it was supplied by a third party vendor and you want to keep your extensions separated by simply having a second table that shares the same key.
• maybe there are fixed width columns in the table which are accessed frequently, and variable ones which aren't. In this case, there might be efficiency gains to be had from having a table with a fixed row length for the frequent stuff, with a secondary table for everything else.

Also, when normalizing a database, say to 3rd Normal Form (3NF) you might find you've got columns which aren't really "about" the key and need to be pulled out to a separate table.

Answer 4

We have used "one to one relationship" when we needed to extend one of our table with too many fields (96 fields!); so, we created another table and put every new field inside of it.

Anyway, an approach i like is:

Table_Base:
    ID
    MANDATORY_FIELD
Table_Option:
    ID
    OPTIONAL_FIELD

Answer 5

Specifically for your example: You don't want user information like name and address stored in the same table as the login and password information, because this way you can grant someone in your organization permission on the user info table, and not on the login data table. I don't agree with the others on the "too many fields for one table" subject. If there's many fields, and you don't need them all in your form or report, you can select just the fields you need with sql, or even use a view to make sure you don't get too much data.

Answer 6

In OO you have inheritance. So you could have a table containing the data of the parent object, and other tables containing fields that are specific for the children.

Answer 7

I've used one to one relationship for extending some features in existing applications, without affecting the application db structure. This is an unobtrusive way to extending existing db tables.

Another reason to use one-to-one relationship is for implementing Class Table Inheritance, in which each class in the hierarchy has a table, and an object has a corresponding row in his table class, in his parent class table, in his grandparent class table and so on.

See, for example, Doctrine 2 Class Table Inheritance Page

Answer 8

Most common reason is that the relationship may be non-obligatory. i.e. there is a set of attributes applicable to every base entity but some attributes which only apply to a subset.

Another valid reason for doing this is to control access rights - you might have a table of customers used throughout your application, and although every customer ha a password and credit card, you may want to restrict visibility / update privileges.

Marga Keuvelaar's comment on Ignacio Vazquez-Abrams answer is wrong. You may be able to alleviate the impact using better DML/DDL but not in all cases. However you are trading off transparency of the data-model against performance benefits - which always needs to be carefully considered.

C.

Answer 9

In addition to what has already been said,

some columns might have different "security clearance" requirements than others. E.g. the name of an employee might be "a bit more public" than his salary. Now, column-level security is not commonly enforced by the DBMS, but table-level security often is.

So by singling out the salary in a separate table, you buy yourself the possibility to implement the user's security requirements using just the DBMS's security facilities.