Lesson 9: Relationship Modeling: One-to-One (1:1)

Relationships describe how two entities interact. The 1:1 relationship is the simplest, though it is the least common.

Defining a One-to-One Relationship

A 1:1 relationship means that for every record in Table A, there is at most one matching record in Table B, and vice versa.

When to Use 1:1?

While you could put all data in a single table, there are situations where splitting data into two tables is beneficial:

1. Security/Privacy: Storing highly sensitive data (like medical records or detailed salary info) in a separate, more restricted table.
2. Performance: If a table has many columns and some are rarely accessed (e.g., a massive Biography text field), separating them improves query speed for common operations.

Implementation of 1:1

To enforce the 1:1 rule, the Foreign Key in the secondary table must also be designated as UNIQUE.

Example: Employees and Parking Permits

Each employee can have only one parking permit, and each permit belongs to only one employee.

1. Table A (Employees): PK = EmployeeID
2. Table B (Permits): FK = EmployeeID (must also be UNIQUE)

sql CREATE TABLE Permits ( PermitID INTEGER PRIMARY KEY, EmployeeID INTEGER UNIQUE, -- Makes the relationship 1:1 IssueDate DATE, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) );