Modeling 1:1 Relationships in ERDs

1. What is a One-to-One Relationship?

A one-to-one (1:1) relationship exists when a single record in one entity is linked to exactly one record in another entity, and vice-versa. Think of a PERSON and their PASSPORT; one person has one passport, and that one passport belongs to only that one person.

While it might seem that data in a 1:1 relationship could just be combined into a single table, there are specific design reasons to keep them separate. Let's explore the definition and the strategic value of this relationship type.

The video highlighted two key reasons for using a 1:1 relationship:

• Separating Optional Data: If an entity has many attributes that are often empty (null) for most records, you can move those optional attributes into a separate table. This keeps your main table clean and can improve performance.
• Data Organization and Security: You might separate data for clarity, or to apply different security rules. For example, general employee data could be in one table, while sensitive payroll or health information is in a separate, more restricted table linked via a 1:1 relationship.

To solidify your understanding, let's look at some more real-world examples.

2. Drawing a One-to-One Relationship

The process for drawing a 1:1 relationship is the same one you learned for 1:M relationships. The only difference lies in the cardinality symbols you place at the ends of the relationship line. We will continue using Crow's Foot notation.

Cardinality Symbols for 1:1 Relationships

In a 1:1 relationship, the maximum cardinality on both sides is "one." The key symbols are:

• || (One and Only One): This indicates a mandatory relationship. The entity instance must be related to exactly one instance in the other entity.
• |O (Zero or One): This indicates an optional relationship. The entity instance can be related to at most one instance, but it can also be related to zero.

Let's see how these are used in practice.

3. Practical Application: Manager and Department

Let's apply this to a business scenario.

Scenario: A company wants to track which manager is responsible for which department. The business rules are:

1. A department must have exactly one manager.
2. A manager is in charge of exactly one department.

Let's follow our steps:

1. Identify the Entities: The nouns are DEPARTMENT and MANAGER.
2. Determine the Relationship:
   • Does a DEPARTMENT have many MANAGERs? No, it has one.
   • Does a MANAGER have many DEPARTMENTs? No, they manage one.
   • This confirms a 1:1 relationship.
3. Determine Cardinality (Minimum and Maximum):
   • From DEPARTMENT to MANAGER: The rule "A department must have exactly one manager" means the minimum is 1 and the maximum is 1. The symbol is || (one and only one).
   • From MANAGER to DEPARTMENT: The rule "A manager is in charge of exactly one department" implies this is also mandatory. The minimum is 1 and the maximum is 1. The symbol is || (one and only one).
4. Draw the ERD:

A mandatory 1:1 relationship. Each department must have exactly one manager, and each manager must manage exactly one department.

An Example with Optionality

Now, let's adjust the scenario.

New Scenario: An employee in the company may be issued a company laptop. Each company laptop, if issued, is assigned to only one employee.

1. Entities: EMPLOYEE, LAPTOP.
2. Relationship: 1:1.
3. Cardinality:
   • From EMPLOYEE to LAPTOP: An employee "may be issued" one. This is optional. The minimum is 0, maximum is 1. The symbol is |O (zero or one).
   • From LAPTOP to EMPLOYEE: A laptop "is assigned to only one employee." We'll assume a laptop in this table must be assigned. The minimum is 1, maximum is 1. The symbol is || (one and only one).
4. Draw the ERD:

An optional 1:1 relationship. An employee can have zero or one laptop, but a laptop must be assigned to exactly one employee.

4. How Keys Make it Work (A Preview)

In the previous lesson, we established that a 1:M relationship is implemented by placing the primary key of the parent ("one" side) into the child table ("many" side) as a foreign key. How does it work for 1:1? The principle is similar but with a twist to enforce the "one" limit on both sides.

Understanding this is crucial for our next lesson, where we'll focus on correctly placing key indicators on our diagrams.

The video below demonstrates how the optional "health insurance" data from the example at the start of the lesson is separated into its own table and linked back to the main Employee table.

As the video shows, we use a foreign key, but to ensure the relationship is truly 1:1 and not 1:M, the database needs an additional rule. There are two common strategies for this, which are explained well in the following reading.

Don't worry about the CREATE TABLE code for now. The key idea to grasp is that the placement and properties of the foreign key are what technically enforce the one-to-one rule you draw in the ERD.

Conclusion

In this lesson, you've added another essential relationship type to your database design toolkit. While less frequent than 1:M relationships, 1:1 relationships are a powerful tool for creating organized, efficient, and secure data models.

Key Takeaways:

• A one-to-one (1:1) relationship links exactly one record from one entity to exactly one record in another.
• They are primarily used to separate optional or sensitive data to improve clarity, performance, and security.
• In Crow's Foot notation, a mandatory 1:1 relationship uses the || symbol, while an optional one uses the |O symbol.
• A 1:1 relationship is implemented using a foreign key, either with a UNIQUE constraint or as a shared primary key, to enforce the "one" limit on both sides.

You now understand how to model one-to-many and one-to-one relationships. In our next lesson, we will formalize the process of documenting keys in our diagrams by learning how to correctly place primary and foreign key indicators on an ERD.