One to one relationships

Most relationships between entities will be of the 1:* kind, and *:* relationships can be decomposed into two 1:* relationships. However, that still leaves 1:1 relationships. These are rarer, but here are some examples:

One EMPLOYEE has one CONTRACT
One PLAYER captains one TEAM
One EMPLOYEE leases one CAR

An immediate question that should come to mind is whether we need to keep 1:1 relationships in the database. After all, one of the reasons for using a database it to eliminate redundancy, so if we can subsume one table into another, isn't that a good thing? The answer is yes, but this is not always desirable, and occasionally impossible.

The starting point for deciding how to handle 1:1 relationships is to look at the optionality of the relationship where there are three possibilities.

The relationship can be:

mandatory at both ends
mandatory at one end and optional at the other
optional at both ends

Mandatory at both ends

If the relationship is mandatory at both ends it is often possible to subsume one entity into the other.

The choice of which entity type subsumes the other depends on which is the most important entity type (more attributes, better key, semantic nature of them)
The result of this amalgamation is that all the attributes of the 'swallowed up' entity become attributes of the more important entity
The primary key of the subsumed entity type becomes a normal attribute
If there are any attributes in common, the duplicates are removed
The primary key of the new combined entity is usually the same as that of the original more important entity type

When not to combine

There are a few reasons why you might not combine a 1:1 mandatory relationship.

the two entity types represent different entities in the real world
the entities participate in very different relationships with other entities
a combined entity would slow down some database operations

If not combined...

If the two entity types are kept separate then the association between them must be represented by a foreign key.

The primary key of one entity type becomes the foreign key in the other.
It does not matter which way around it is done but you should not have a foreign key in both entities

Example

Two entities are EMPLOYEE and CONTRACT
Each EMPLOYEE must have one CONTRACT and each CONTRACT must have one EMPLOYEE associated with it
It is therefore a mandatory relations at both ends

These two entity types could be combined into one:

Alternatively, they could be kept separate, in which case there are two possibilities:

Mandatory / Optional

The entity at the optional end may be subsumed into the one at the mandatory end, or both entities may be retained.

Example

Two entities are PLAYER and TEAM
One PLAYER may captain one TEAM, and each TEAM must have one CAPTAIN
However, there are other PLAYERS who do not captain a TEAM; therefore the relationship is mandatory on one direction and optional in the other

Notice the use of may and must in the relationship description, and the multiplicity labels in the diagram

Combining the two entities would give the result below. Only those players who are team captains would have values in the team_name and league_position.

The reasons for not subsuming are the same as before with one additional reason:

the two entity types represent different entities in the real world
the entities participate in very different relationships with other entities
a combined entity would slow down some database operations
very few of the entities from the mandatory end are involved in the relationship. This could cause a lot of wasted space with many blank or null entries.

Thinking about the team captain example, there will be many PLAYERS who are not the captain of a TEAM. As noted above, the team_name and league_position fields would be empty for these people. That is a lot of wasted space, and in this case we would probably maintain two separate entities.

To summarise the approach for 1:1 mandatory/optional relationships, take the primary key from the 'mandatory end' and add it to the 'optional end' as a foreign key:

Notice in this example that the name of the foreign key column in TEAM is different from the name of the primary key column in PLAYER. This is perfectly legal, and in this case it makes the purpose of the column clearer. In most cases, however, the two columns will have the same name.

Optional at both ends

Such examples cannot be combined because you could not select a primary key. Instead, one foreign key is used as before.

Example

Imagine a company that operates a company car scheme where some employees can have exclusive use of a car.

Two entities are EMPLOYEE and CAR
One EMPLOYEE may lease one CAR, and one CAR may be leased by one EMPLOYEE
However, there are EMPLOYEES who do not lease CARS, and CARS that are not allocated to EMPLOYEES. The relationship is therefore optional in both directions

Notice the use of the word may in the description, and the multiplicity labels on the diagram.

Remember that every record in a table should have a primary key. If we tried to combined these two entities, we have three options for the primary key:

Use emp_no
Use registration
Us a composite key made up up emp_no and registration

If we use emp_no, any unallocated cars would have no primary key

If we use registration, any employees without a car would have no key

If we use the composite solution, sometimes one of the key attributes would have no value. This is not permitted: a primary key field must always have a value.

The only solution is to maintain two separate entities. There is still one remaining problem however: where does the foreign key go? Putting it in either table would work, but would also require null values.