ER Diagrams   «Prev  Next»

Lesson 5One-to-one relationships, part 2
Objective Identify one-to-one Relationships

One-to-one Relationships

Relationship types are determined by interpreting business rules and common-sense rules that characterize entities in relationships. Consider this business rule: Employees in the company are each assigned their own office.
This rule means that for each employee there exists a unique office, and for each office there exists a unique employee. The entities in the example are EMPLOYEE and OFFICE, with EMPLOYEE:OFFICE forming a 1:1 relationship, and OFFICE:EMPLOYEE forming a 1:1 relationship.

One-to-one relationships should be analyzed carefully. For example, what if the organization grows and more employees need to be hired? Is the organization willing to abandon its business rule and assign more than one employee to an office? The designer should verify the intent behind all one-to-one relationships with the organization's managers.
The next lesson defines one-to-many relationships.

Example of One-to-One Relationships

Consider, for a moment, an airport in a small town, where both the airport and the town are described in a database of small-town airports. Each of these might be represented by an instance of a different type of entity.
The relationship between the two instances can be expressed as "The airport is located in one and only one town, and the town has one and only one airport."
This is a true one-to-one relationship because at no time can a single airport be related to more than one town, and no town can be related to more than one airport. (Although there are municipalities that have more than one airport, the towns in the database are too small for that to ever happen.)
If we have instances of two entities (A and B) called Ai and Bi, then a one-to-one relationship exists if at all times Ai is related to no instances of entity B or one instance of entity B, and Bi is related to no instances of entity A or one instance of entity A.
True one-to-one relationships are very rare in business. For example, assume that DistributedNetworks decides to start dealing with a new distributor of DVDs.
At first, the company orders only one specialty title from the new distributor. If we peered inside the database, we would see that the instance of the distributor entity was related to just the one merchandise item instance. This would then appear to be a one-to-one relationship. However, over time, DistributedNetworks may choose to order more titles from the new distributor, which would violate the rule that the distributor must be related to no more than one merchandise item.

one To one Relationships - Exercise

Before moving on to the next lesson, click the Exercise link below to check your understanding of one-to-one relationships.
one-to-one Relationships - Exercise

Six-Step Relational Database Design