Relational Database Design: One-to-One Relationships and When to Use Them
One-to-one relationships are the result of splitting one entity into two parts. This article covers some reasons to do that.
(This article also assumes familiarity with terms such as entity, cardinality, modality and attributes. If you’re not familiar with these, you might have a look at Relational Database Design: Entity Relationships where I explain them.)
Extra Attributes
The main reason to do this is when some instances of an entity contain extra attributes.
For example, suppose we have a Persons entity. Some of the Person instances in this entity have died, while most have not. We want to keep several Person attributes for deceased persons that we don’t want to keep for living persons. Date of death, for example. If we keep attributes that only pertain to deceased persons in the Persons entity, for most instances (those of living persons) these attributes will be null values. This takes up unnecessary space.
Information that only pertains to the deceased could be treated as a separate entity, split apart into a table with a one-to-one relationship with the Persons entity. The cardinality would be one to one, and the modality would be one on the people side and zero on the deceased side. (This is sometimes called a zero-to-one relationship.)
领英推荐
For another example, a hospital database might have an employees entity with general information that pertains to all employees. But doctors, nurses and other licensed health care providers have extra information. It’s more efficient to keep the extra practitioner information in a separate table. We don’t want to provide space for practitioners’ extra attributes for employees that don’t have them.
Improving Caching and Performance
Suppose that the hospital accesses practitioner information much more often than general employee information. Having that information in a separate table improves performance: databases will put often-accessed “pages” of data in a cache, and the more instances that will fit in those pages, the more efficient the caching is. So, putting often-accessed values in smaller instances allows the DBMS to do a better job of caching them.
Architectural Flexibility
One other reason for one-to-one relationships is to provide some flexibility in architecture. If, for example, you have an Employee entity and you want to keep a class of information about employees on a trial basis (let’s say each employee’s social media information, such as LinkedIn, Facebook and Twitter account information), then it’s much easier to remove that information later on if it is in a separate table, especially if the Employee entity has a lot of instances (i.e. the Employee table has a lot of rows). It’s easier because you don’t have to do anything to the main table; you can just drop the auxiliary table.
All in all, it is best not to use one-to-one relationships unless there is a good technical reason (extra attributes for some instances, improved caching) or business reason (keeping options open for modification) to break an entity apart.