Database Management SystemsRelationship Mapping (1:1)

Mapping One-to-One Relationships

LevelIntermediate

Duration60 mins

TopicRelationship Mapping (1:1)

5 / 5

Examples

Theory Meets Practice

Throughout this module, we've built a comprehensive understanding of 1:1 relationship characteristics, mapping strategies, and decision criteria. Now it's time to apply everything to realistic scenarios.

This page presents five detailed case studies, each walking through the complete process: understanding the domain, analyzing participation, applying the decision framework, implementing the schema, and explaining the rationale. These examples span different industries and demonstrate how the same principles apply universally while producing context-appropriate solutions.

By working through these examples, you'll develop the pattern recognition and analytical skills to confidently handle any 1:1 mapping challenge you encounter in practice.

What You Will Learn

By the end of this page, you will see the decision framework applied to real-world scenarios, understand how participation analysis drives strategy selection, recognize common 1:1 patterns across industries, and gain confidence in your ability to make and defend mapping decisions.

Example 1: Healthcare Patient Records

Domain: Hospital Patient Management System

A hospital system manages patients and their medical records. Each patient has a core demographic record (name, date of birth, contact information, insurance) and a comprehensive medical record (diagnoses, medications, allergies, visit history).

ER Model Analysis:

PATIENT: Core demographic entity
MEDICAL_RECORD: Clinical data entity
Relationship: Each patient has exactly one medical record; each medical record belongs to exactly one patient

Participation Analysis:

Can a PATIENT exist without a MEDICAL_RECORD? No — Medical record created at registration
Can a MEDICAL_RECORD exist without a PATIENT? No — Records are always patient-specific
Participation Pattern: Total-Total (1,1):(1,1)

Decision Framework Application

Level 1 (Participation): Total-Total → Both merge and FK are viable Level 2 (Coupling): Strong lifecycle coupling (created together), but different access patterns Level 3 (Non-Functional): Different security requirements—admin staff access demographics, clinical staff access medical records

Decision: Foreign Key Approach

Despite total-total participation, we choose the foreign key approach because:

Security divergence: HIPAA compliance requires different access controls for demographic vs. clinical data
Access pattern divergence: Reception queries demographics; doctors query clinical records
Professional boundaries: Different staff roles authorize different data views

The FK goes in MEDICAL_RECORD since medical records are conceptually dependent on patients and created after the patient record.

Healthcare Schema Implementation
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Demographics (accessible by administrative staff)
CREATE TABLE patient (
    patient_id          INT PRIMARY KEY,
    mrn                 VARCHAR(20) UNIQUE NOT NULL,  -- Medical Record Number
    first_name          VARCHAR(50) NOT NULL,
    last_name           VARCHAR(50) NOT NULL,
    date_of_birth       DATE NOT NULL,
    gender              CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    address             VARCHAR(200),
    phone               VARCHAR(20),
    email               VARCHAR(100),
    insurance_provider  VARCHAR(100),
    insurance_policy    VARCHAR(50),
    emergency_contact   VARCHAR(100),
    registered_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Clinical data (accessible by clinical staff)
CREATE TABLE medical_record (
    record_id           INT PRIMARY KEY,
    patient_id          INT UNIQUE NOT NULL,  -- 1:1 enforced by UNIQUE
    blood_type          CHAR(3),
    allergies           TEXT,
    chronic_conditions  TEXT,
    current_medications TEXT,
    primary_physician   VARCHAR(100),
    last_visit_date     DATE,
    notes               TEXT,
    
    CONSTRAINT fk_patient
        FOREIGN KEY (patient_id)
        REFERENCES patient(patient_id)
        ON DELETE RESTRICT  -- Prevent accidental deletion
        ON UPDATE CASCADE
);
 
-- Grant different access
GRANT SELECT ON patient TO admin_staff;
GRANT SELECT ON medical_record TO clinical_staff;
 
-- Combined view for authorized users
CREATE VIEW v_patient_complete AS
SELECT p.*, m.blood_type, m.allergies, m.current_medications
FROM patient p
JOIN medical_record m ON p.patient_id = m.patient_id;
 
GRANT SELECT ON v_patient_complete TO attending_physicians;

Example 2: E-Commerce User Accounts

Domain: Online Shopping Platform

An e-commerce platform has user accounts with authentication credentials and profile settings (preferences, display options, notification choices).

ER Model Analysis:

USER_ACCOUNT: Authentication entity (email, password hash, MFA settings)
USER_PROFILE: Preferences entity (display name, avatar, theme, language, notifications)
Relationship: Each user account has one profile; each profile belongs to one account

Participation Analysis:

Can a USER_ACCOUNT exist without a USER_PROFILE? No — Profile created with defaults at registration
Can a USER_PROFILE exist without a USER_ACCOUNT? No — Profiles are always account-owned
Participation Pattern: Total-Total (1,1):(1,1)

Decision Framework Application:

Participation: Total-Total → Merge and FK both viable
Lifecycle coupling: Very strong—created together at registration
Query coupling: High—user data and profile loaded on every request
Security considerations: Same access level for both
Row size: Combined columns easily fit within row limits
Evolution likelihood: Unlikely to change to 1:N

Decision: Merged Table Approach

All factors point toward merging:

Same security model
Always accessed together
Strong lifecycle coupling
No foreseeable cardinality change

E-Commerce Merged User Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Merged user table
CREATE TABLE user_account (
    user_id             INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Authentication attributes (originally USER_ACCOUNT)
    email               VARCHAR(100) UNIQUE NOT NULL,
    password_hash       CHAR(60) NOT NULL,  -- bcrypt hash
    mfa_enabled         BOOLEAN DEFAULT false,
    mfa_secret          VARCHAR(32),
    email_verified      BOOLEAN DEFAULT false,
    account_status      VARCHAR(20) DEFAULT 'active' 
                        CHECK (account_status IN ('active', 'suspended', 'deleted')),
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login          TIMESTAMP,
    
    -- Profile attributes (originally USER_PROFILE)
    display_name        VARCHAR(50),
    avatar_url          VARCHAR(255),
    bio                 TEXT,
    
    -- Preferences (also from USER_PROFILE)
    pref_theme          VARCHAR(10) DEFAULT 'system'
                        CHECK (pref_theme IN ('light', 'dark', 'system')),
    pref_language       CHAR(5) DEFAULT 'en-US',
    pref_currency       CHAR(3) DEFAULT 'USD',
    pref_timezone       VARCHAR(50) DEFAULT 'UTC',
    
    -- Notification settings
    notify_email        BOOLEAN DEFAULT true,
    notify_push         BOOLEAN DEFAULT true,
    notify_sms          BOOLEAN DEFAULT false,
    
    -- Metadata
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
-- Indexes for common queries
CREATE INDEX idx_user_email ON user_account(email);
CREATE INDEX idx_user_status ON user_account(account_status);
 
-- Application authentication query (very fast - single table)
-- SELECT user_id, password_hash, mfa_enabled, mfa_secret
-- FROM user_account WHERE email = ? AND account_status = 'active';
 
-- Page render query (all needed in one row)
-- SELECT display_name, avatar_url, pref_theme, pref_language
-- FROM user_account WHERE user_id = ?;

Example 3: Corporate Fleet Management

Domain: Corporate Vehicle Assignment

A company manages a fleet of vehicles that may be assigned to employees. Not all employees have vehicles; not all vehicles are assigned.

ER Model Analysis:

EMPLOYEE: Company staff members
COMPANY_VEHICLE: Fleet vehicles
Relationship: ASSIGNED_VEHICLE — An employee may have a vehicle; a vehicle may be assigned to an employee

Participation Analysis:

Can an EMPLOYEE exist without an assigned COMPANY_VEHICLE? Yes — Most employees use public transit
Can a COMPANY_VEHICLE exist without an EMPLOYEE assignment? Yes — Pool vehicles, new arrivals, maintenance
Participation Pattern: Partial-Partial (0,1):(0,1)

The Partial-Partial Challenge

Partial-partial is the most challenging 1:1 pattern. Neither entity always participates, so a foreign key in either table creates NULLs. We must choose between accepting NULLs or using a cross-reference table.

Decision Framework Application:

Participation: Partial-Partial → FK with NULLs or cross-reference table
NULL ratio analysis: 80% employees without vehicles; 30% vehicles unassigned
- FK in EMPLOYEE: 80% NULL
- FK in VEHICLE: 30% NULL (better)
Relationship attributes: Assignments have dates, approval status
Historical tracking: May want to track past assignments

Decision: Cross-Reference Table

Given the need for relationship attributes and potential history tracking, plus NULL avoidance, we choose the cross-reference table approach.

Fleet Management Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Employees (no vehicle FK)
CREATE TABLE employee (
    employee_id         INT PRIMARY KEY,
    first_name          VARCHAR(50) NOT NULL,
    last_name           VARCHAR(50) NOT NULL,
    department          VARCHAR(50) NOT NULL,
    hire_date           DATE NOT NULL,
    position            VARCHAR(100)
);
 
-- Company vehicles (no employee FK)
CREATE TABLE company_vehicle (
    vehicle_id          INT PRIMARY KEY,
    license_plate       VARCHAR(20) UNIQUE NOT NULL,
    make                VARCHAR(50) NOT NULL,
    model               VARCHAR(50) NOT NULL,
    year                INT NOT NULL,
    vin                 CHAR(17) UNIQUE NOT NULL,
    vehicle_type        VARCHAR(30),
    purchase_date       DATE,
    status              VARCHAR(20) DEFAULT 'available'
                        CHECK (status IN ('available', 'assigned', 'maintenance', 'retired'))
);
 
-- Cross-reference table with relationship attributes
CREATE TABLE vehicle_assignment (
    assignment_id       INT PRIMARY KEY AUTO_INCREMENT,
    employee_id         INT UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    vehicle_id          INT UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    
    -- Relationship attributes
    assigned_date       DATE NOT NULL,
    assigned_by         INT,
    assignment_reason   VARCHAR(200),
    expected_return     DATE,
    mileage_at_assign   INT,
    
    CONSTRAINT fk_employee
        FOREIGN KEY (employee_id) REFERENCES employee(employee_id),
    CONSTRAINT fk_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES company_vehicle(vehicle_id),
    CONSTRAINT fk_assigned_by
        FOREIGN KEY (assigned_by) REFERENCES employee(employee_id)
);
 
-- Query: Find an employee's assigned vehicle
SELECT e.first_name, e.last_name, v.make, v.model, v.license_plate
FROM employee e
JOIN vehicle_assignment va ON e.employee_id = va.employee_id
JOIN company_vehicle v ON va.vehicle_id = v.vehicle_id
WHERE e.employee_id = ?;
 
-- Query: Find unassigned employees (for eligibility check)
SELECT e.*
FROM employee e
LEFT JOIN vehicle_assignment va ON e.employee_id = va.employee_id
WHERE va.employee_id IS NULL;
 
-- Query: Find available vehicles
SELECT v.*
FROM company_vehicle v
LEFT JOIN vehicle_assignment va ON v.vehicle_id = va.vehicle_id
WHERE va.vehicle_id IS NULL AND v.status = 'available';
 
-- Trigger to update vehicle status on assignment
CREATE TRIGGER after_assignment_insert
AFTER INSERT ON vehicle_assignment
FOR EACH ROW
    UPDATE company_vehicle SET status = 'assigned' 
    WHERE vehicle_id = NEW.vehicle_id;

Example 4: Geopolitical Data System

Domain: Global Geographic Information System

A geographic database tracks countries and their capitals for diplomatic, logistics, and reference purposes.

ER Model Analysis:

COUNTRY: Sovereign nations
CAPITAL_CITY: Capital cities (as a concept, not as a CITY entity)
Relationship: Each country has exactly one capital; each capital is the capital of exactly one country

Participation Analysis:

Can a COUNTRY exist without a capital? No — Every recognized country designates a capital
Can a designated CAPITAL_CITY exist without a country? No — A capital is defined by its country
Participation Pattern: Total-Total (1,1):(1,1)

Important distinction: We're modeling CAPITAL_CITY as the capital attribute set, not as a general CITY entity. If cities were a separate entity with many cities per country (1:N), and some cities are capitals, the design would be different.

Decision Framework Application:

Participation: Total-Total → Both strategies viable
Lifecycle coupling: Very strong—capital is inherent to country identity
Query coupling: Nearly always accessed together for country info
Security: No differentiation needed
Evolution: Will reliably remain 1:1

Decision: Merged Table Approach

The capital is effectively an attribute set of the country, not an independent entity. Merging is natural.

Geopolitical Merged Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Merged country table with capital attributes
CREATE TABLE country (
    country_id          INT PRIMARY KEY,
    country_code        CHAR(2) UNIQUE NOT NULL,  -- ISO 3166-1 alpha-2
    country_code_3      CHAR(3) UNIQUE NOT NULL,  -- ISO 3166-1 alpha-3
    country_name        VARCHAR(100) NOT NULL,
    official_name       VARCHAR(200),
    
    -- Core country data
    population          BIGINT,
    area_km2            DECIMAL(12, 2),
    continent           VARCHAR(20),
    region              VARCHAR(50),
    currency_code       CHAR(3),
    
    -- Capital attributes (merged from CAPITAL_CITY concept)
    capital_name        VARCHAR(100) NOT NULL,
    capital_lat         DECIMAL(9, 6),
    capital_lng         DECIMAL(9, 6),
    capital_population  INT,
    capital_timezone    VARCHAR(50),
    capital_elevation   INT,  -- meters above sea level
    
    -- Metadata
    independence_date   DATE,
    government_type     VARCHAR(100),
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Sample data
INSERT INTO country (
    country_id, country_code, country_code_3, country_name,
    population, area_km2, continent,
    capital_name, capital_lat, capital_lng, capital_population, capital_timezone
) VALUES
(1, 'FR', 'FRA', 'France', 67390000, 551695, 'Europe',
    'Paris', 48.8566, 2.3522, 2161000, 'Europe/Paris'),
(2, 'JP', 'JPN', 'Japan', 125800000, 377975, 'Asia',
    'Tokyo', 35.6762, 139.6503, 13960000, 'Asia/Tokyo'),
(3, 'BR', 'BRA', 'Brazil', 212600000, 8515767, 'South America',
    'Brasília', -15.7942, -47.8825, 3055000, 'America/Sao_Paulo');
 
-- Typical queries are simple (no join needed)
SELECT country_name, capital_name, capital_population
FROM country
WHERE continent = 'Europe';
 
-- Geographic queries straightforward
SELECT country_name, capital_name, capital_lat, capital_lng
FROM country
WHERE capital_lat BETWEEN -10 AND 10;  -- Countries with equatorial capitals

Alternative Design Consideration

If the system also tracked general cities (tourism data, airports, etc.), capital would be a 1:1 relationship between COUNTRY and a subset of CITY entities. In that case, a foreign key approach (capital_city_id in COUNTRY) would be appropriate, with the FK referencing the CITY table.

Example 5: Document Checkout System

Domain: Legal Document Management

A law firm manages confidential documents that can be checked out for editing. Only one person can have a document checked out at a time to prevent conflicts.

ER Model Analysis:

DOCUMENT: Legal documents in the system
CHECKOUT_RECORD: Active checkout information
Relationship: A document may have an active checkout; each checkout is for exactly one document

Participation Analysis:

Can a DOCUMENT exist without an active checkout? Yes — Most documents are not actively being edited
Can a CHECKOUT_RECORD exist without a document? No — Checkouts are always for a specific document
Participation Pattern: Partial-Total (0,1):(1,1)

Decision Framework Application:

Participation: Partial-Total → Foreign key approach
FK Placement: CHECKOUT_RECORD (the total participation side)

This is a clear case: participation type directly determines the approach.

Document Checkout Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Document entity (partial participation - not all are checked out)
CREATE TABLE document (
    document_id         INT PRIMARY KEY,
    title               VARCHAR(200) NOT NULL,
    document_type       VARCHAR(50) NOT NULL,
    client_id           INT NOT NULL,
    case_number         VARCHAR(50),
    security_level      VARCHAR(20) DEFAULT 'confidential'
                        CHECK (security_level IN ('public', 'internal', 'confidential', 'restricted')),
    created_date        DATE NOT NULL,
    created_by          INT NOT NULL,
    last_modified       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version             INT DEFAULT 1,
    file_path           VARCHAR(500) NOT NULL
);
 
-- Checkout entity (total participation - every checkout is for a document)
CREATE TABLE checkout_record (
    checkout_id         INT PRIMARY KEY AUTO_INCREMENT,
    document_id         INT UNIQUE NOT NULL,  -- FK + UNIQUE = 1:1
    checked_out_by      INT NOT NULL,
    checkout_time       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expected_return     TIMESTAMP,
    purpose             VARCHAR(500),
    workstation         VARCHAR(100),
    
    CONSTRAINT fk_document
        FOREIGN KEY (document_id)
        REFERENCES document(document_id)
        ON DELETE CASCADE,  -- If document deleted, checkout record goes too
        
    CONSTRAINT fk_user
        FOREIGN KEY (checked_out_by)
        REFERENCES employee(employee_id)
);
 
-- Check if document is available for checkout
SELECT d.document_id, d.title,
       CASE WHEN cr.checkout_id IS NULL THEN 'Available' ELSE 'Checked Out' END AS status,
       cr.checked_out_by, cr.checkout_time
FROM document d
LEFT JOIN checkout_record cr ON d.document_id = cr.document_id
WHERE d.document_id = ?;
 
-- Attempt to checkout (would fail if already checked out due to UNIQUE constraint)
INSERT INTO checkout_record (document_id, checked_out_by, purpose)
VALUES (?, ?, 'Client review edits');
-- If document already checked out, UNIQUE violation error
 
-- Check in document (delete the checkout record)
DELETE FROM checkout_record WHERE document_id = ? AND checked_out_by = ?;
 
-- Find all documents checked out by a user
SELECT d.document_id, d.title, cr.checkout_time
FROM checkout_record cr
JOIN document d ON cr.document_id = d.document_id
WHERE cr.checked_out_by = ?;
 
-- Trigger to update document last_modified on checkout
CREATE TRIGGER after_checkout_insert
AFTER INSERT ON checkout_record
FOR EACH ROW
    UPDATE document 
    SET last_modified = CURRENT_TIMESTAMP 
    WHERE document_id = NEW.document_id;

Example Comparison Summary

Let's summarize the five examples to reinforce pattern recognition:

1:1 Mapping Examples Summary
Domain	Relationship	Participation	Strategy	Key Factor
Healthcare	Patient ↔ Medical Record	Total-Total	Foreign Key	Security divergence
E-Commerce	User ↔ Profile	Total-Total	Merged Table	Strong coupling, same security
Fleet Mgmt	Employee ↔ Vehicle	Partial-Partial	Cross-Reference	NULL avoidance, relationship attrs
Geopolitical	Country ↔ Capital	Total-Total	Merged Table	Capital is attribute set of country
Legal Docs	Document ↔ Checkout	Partial-Total	Foreign Key	Participation determines placement

Pattern Recognition

Notice how participation patterns strongly correlate with strategy choices:

• Total-Total → Evaluate coupling; merge if strong, FK if security/access diverges • Total-Partial or Partial-Total → Foreign key in total-participation side (nearly always) • Partial-Partial → Cross-reference if NULL-averse or relationship has attributes; otherwise FK with NULLs in lower-NULL-ratio side

Common Mistakes to Avoid

Before concluding, let's highlight mistakes commonly made when mapping 1:1 relationships:

Common 1:1 Mapping Mistakes

•Forgetting UNIQUE constraint: A foreign key without UNIQUE creates 1:N, not 1:1. This is the most common error—test by attempting duplicate FK values.
•Placing FK in wrong table: FK in partial-participation table creates unnecessary NULLs. Always identify participation correctly first.
•Merging with partial participation: Creates NULL-laden rows that violate normalization and waste storage.
•Merging when security differs: If entities need different access controls, separate tables are required despite total-total participation.
•Over-engineering partial-partial: Not every partial-partial needs a cross-reference table. If NULLs are few and acceptable, simple FK is fine.
•Ignoring future evolution: Merging when 1:N is plausible creates painful refactoring later. When in doubt, keep separate.
•Not indexing FK appropriately: The UNIQUE constraint usually creates an index, but verify. FK columns should always be indexed.
•Wrong referential action: ON DELETE CASCADE when you should RESTRICT, or vice versa. Think through entity lifecycle implications.

Summary: Mastering 1:1 Mapping

Through theory and practical examples, you've now developed comprehensive expertise in mapping 1:1 relationships from ER diagrams to relational schemas.

Module Key Takeaways

•1:1 relationships enforce bidirectional uniqueness — Each entity instance associates with at most one instance of the related entity.
•Three strategies exist: Foreign key (two tables), merged table (one table), and cross-reference (three tables).
•Participation is the primary decision factor — Total vs. partial participation often determines the optimal strategy.
•Foreign key approach requires UNIQUE constraint — This enforces 1:1 cardinality on top of referential integrity.
•Merge only with total-total and strong coupling — Otherwise, NULLs or flexibility loss result.
•Cross-reference tables eliminate NULLs — Use for partial-partial with relationship attributes or NULL aversion.
•Consider evolution — Prefer flexibility when future requirements are uncertain.
•Document decisions — Future maintainers need to understand why, not just what.

Congratulations!

You've completed the module on mapping 1:1 relationships. You now possess the knowledge to analyze any 1:1 relationship, apply the decision framework, implement the optimal strategy, and defend your design choices. This expertise forms a critical component of your overall database design capability.

In subsequent modules, we'll explore 1:N and M:N relationship mapping, building on the foundations established here.

Module Complete

You have completed Module 3: Relationship Mapping (1:1). You've mastered the semantic understanding of 1:1 relationships, all three mapping strategies, the systematic decision framework, and practical implementation across diverse domains. Apply this knowledge confidently in your database design work.

5 / 5

Loading learning content...

Database Management SystemsRelationship Mapping (1:1)

Mapping One-to-One Relationships

LevelIntermediate

Duration60 mins

TopicRelationship Mapping (1:1)

5 / 5

Examples

Theory Meets Practice

By working through these examples, you'll develop the pattern recognition and analytical skills to confidently handle any 1:1 mapping challenge you encounter in practice.

What You Will Learn

Example 1: Healthcare Patient Records

Domain: Hospital Patient Management System

ER Model Analysis:

PATIENT: Core demographic entity
MEDICAL_RECORD: Clinical data entity
Relationship: Each patient has exactly one medical record; each medical record belongs to exactly one patient

Participation Analysis:

Can a PATIENT exist without a MEDICAL_RECORD? No — Medical record created at registration
Can a MEDICAL_RECORD exist without a PATIENT? No — Records are always patient-specific
Participation Pattern: Total-Total (1,1):(1,1)

Decision Framework Application

Decision: Foreign Key Approach

Despite total-total participation, we choose the foreign key approach because:

Security divergence: HIPAA compliance requires different access controls for demographic vs. clinical data
Access pattern divergence: Reception queries demographics; doctors query clinical records
Professional boundaries: Different staff roles authorize different data views

The FK goes in MEDICAL_RECORD since medical records are conceptually dependent on patients and created after the patient record.

Healthcare Schema Implementation
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Demographics (accessible by administrative staff)
CREATE TABLE patient (
    patient_id          INT PRIMARY KEY,
    mrn                 VARCHAR(20) UNIQUE NOT NULL,  -- Medical Record Number
    first_name          VARCHAR(50) NOT NULL,
    last_name           VARCHAR(50) NOT NULL,
    date_of_birth       DATE NOT NULL,
    gender              CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    address             VARCHAR(200),
    phone               VARCHAR(20),
    email               VARCHAR(100),
    insurance_provider  VARCHAR(100),
    insurance_policy    VARCHAR(50),
    emergency_contact   VARCHAR(100),
    registered_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Clinical data (accessible by clinical staff)
CREATE TABLE medical_record (
    record_id           INT PRIMARY KEY,
    patient_id          INT UNIQUE NOT NULL,  -- 1:1 enforced by UNIQUE
    blood_type          CHAR(3),
    allergies           TEXT,
    chronic_conditions  TEXT,
    current_medications TEXT,
    primary_physician   VARCHAR(100),
    last_visit_date     DATE,
    notes               TEXT,
    
    CONSTRAINT fk_patient
        FOREIGN KEY (patient_id)
        REFERENCES patient(patient_id)
        ON DELETE RESTRICT  -- Prevent accidental deletion
        ON UPDATE CASCADE
);
 
-- Grant different access
GRANT SELECT ON patient TO admin_staff;
GRANT SELECT ON medical_record TO clinical_staff;
 
-- Combined view for authorized users
CREATE VIEW v_patient_complete AS
SELECT p.*, m.blood_type, m.allergies, m.current_medications
FROM patient p
JOIN medical_record m ON p.patient_id = m.patient_id;
 
GRANT SELECT ON v_patient_complete TO attending_physicians;

Example 2: E-Commerce User Accounts

Domain: Online Shopping Platform

An e-commerce platform has user accounts with authentication credentials and profile settings (preferences, display options, notification choices).

ER Model Analysis:

USER_ACCOUNT: Authentication entity (email, password hash, MFA settings)
USER_PROFILE: Preferences entity (display name, avatar, theme, language, notifications)
Relationship: Each user account has one profile; each profile belongs to one account

Participation Analysis:

Can a USER_ACCOUNT exist without a USER_PROFILE? No — Profile created with defaults at registration
Can a USER_PROFILE exist without a USER_ACCOUNT? No — Profiles are always account-owned
Participation Pattern: Total-Total (1,1):(1,1)

Decision Framework Application:

Participation: Total-Total → Merge and FK both viable
Lifecycle coupling: Very strong—created together at registration
Query coupling: High—user data and profile loaded on every request
Security considerations: Same access level for both
Row size: Combined columns easily fit within row limits
Evolution likelihood: Unlikely to change to 1:N

Decision: Merged Table Approach

All factors point toward merging:

Same security model
Always accessed together
Strong lifecycle coupling
No foreseeable cardinality change

E-Commerce Merged User Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Merged user table
CREATE TABLE user_account (
    user_id             INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Authentication attributes (originally USER_ACCOUNT)
    email               VARCHAR(100) UNIQUE NOT NULL,
    password_hash       CHAR(60) NOT NULL,  -- bcrypt hash
    mfa_enabled         BOOLEAN DEFAULT false,
    mfa_secret          VARCHAR(32),
    email_verified      BOOLEAN DEFAULT false,
    account_status      VARCHAR(20) DEFAULT 'active' 
                        CHECK (account_status IN ('active', 'suspended', 'deleted')),
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login          TIMESTAMP,
    
    -- Profile attributes (originally USER_PROFILE)
    display_name        VARCHAR(50),
    avatar_url          VARCHAR(255),
    bio                 TEXT,
    
    -- Preferences (also from USER_PROFILE)
    pref_theme          VARCHAR(10) DEFAULT 'system'
                        CHECK (pref_theme IN ('light', 'dark', 'system')),
    pref_language       CHAR(5) DEFAULT 'en-US',
    pref_currency       CHAR(3) DEFAULT 'USD',
    pref_timezone       VARCHAR(50) DEFAULT 'UTC',
    
    -- Notification settings
    notify_email        BOOLEAN DEFAULT true,
    notify_push         BOOLEAN DEFAULT true,
    notify_sms          BOOLEAN DEFAULT false,
    
    -- Metadata
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
-- Indexes for common queries
CREATE INDEX idx_user_email ON user_account(email);
CREATE INDEX idx_user_status ON user_account(account_status);
 
-- Application authentication query (very fast - single table)
-- SELECT user_id, password_hash, mfa_enabled, mfa_secret
-- FROM user_account WHERE email = ? AND account_status = 'active';
 
-- Page render query (all needed in one row)
-- SELECT display_name, avatar_url, pref_theme, pref_language
-- FROM user_account WHERE user_id = ?;

Example 3: Corporate Fleet Management

Domain: Corporate Vehicle Assignment

A company manages a fleet of vehicles that may be assigned to employees. Not all employees have vehicles; not all vehicles are assigned.

ER Model Analysis:

EMPLOYEE: Company staff members
COMPANY_VEHICLE: Fleet vehicles
Relationship: ASSIGNED_VEHICLE — An employee may have a vehicle; a vehicle may be assigned to an employee

Participation Analysis:

Can an EMPLOYEE exist without an assigned COMPANY_VEHICLE? Yes — Most employees use public transit
Can a COMPANY_VEHICLE exist without an EMPLOYEE assignment? Yes — Pool vehicles, new arrivals, maintenance
Participation Pattern: Partial-Partial (0,1):(0,1)

The Partial-Partial Challenge

Decision Framework Application:

Participation: Partial-Partial → FK with NULLs or cross-reference table
NULL ratio analysis: 80% employees without vehicles; 30% vehicles unassigned
- FK in EMPLOYEE: 80% NULL
- FK in VEHICLE: 30% NULL (better)
Relationship attributes: Assignments have dates, approval status
Historical tracking: May want to track past assignments

Decision: Cross-Reference Table

Given the need for relationship attributes and potential history tracking, plus NULL avoidance, we choose the cross-reference table approach.

Fleet Management Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Employees (no vehicle FK)
CREATE TABLE employee (
    employee_id         INT PRIMARY KEY,
    first_name          VARCHAR(50) NOT NULL,
    last_name           VARCHAR(50) NOT NULL,
    department          VARCHAR(50) NOT NULL,
    hire_date           DATE NOT NULL,
    position            VARCHAR(100)
);
 
-- Company vehicles (no employee FK)
CREATE TABLE company_vehicle (
    vehicle_id          INT PRIMARY KEY,
    license_plate       VARCHAR(20) UNIQUE NOT NULL,
    make                VARCHAR(50) NOT NULL,
    model               VARCHAR(50) NOT NULL,
    year                INT NOT NULL,
    vin                 CHAR(17) UNIQUE NOT NULL,
    vehicle_type        VARCHAR(30),
    purchase_date       DATE,
    status              VARCHAR(20) DEFAULT 'available'
                        CHECK (status IN ('available', 'assigned', 'maintenance', 'retired'))
);
 
-- Cross-reference table with relationship attributes
CREATE TABLE vehicle_assignment (
    assignment_id       INT PRIMARY KEY AUTO_INCREMENT,
    employee_id         INT UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    vehicle_id          INT UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    
    -- Relationship attributes
    assigned_date       DATE NOT NULL,
    assigned_by         INT,
    assignment_reason   VARCHAR(200),
    expected_return     DATE,
    mileage_at_assign   INT,
    
    CONSTRAINT fk_employee
        FOREIGN KEY (employee_id) REFERENCES employee(employee_id),
    CONSTRAINT fk_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES company_vehicle(vehicle_id),
    CONSTRAINT fk_assigned_by
        FOREIGN KEY (assigned_by) REFERENCES employee(employee_id)
);
 
-- Query: Find an employee's assigned vehicle
SELECT e.first_name, e.last_name, v.make, v.model, v.license_plate
FROM employee e
JOIN vehicle_assignment va ON e.employee_id = va.employee_id
JOIN company_vehicle v ON va.vehicle_id = v.vehicle_id
WHERE e.employee_id = ?;
 
-- Query: Find unassigned employees (for eligibility check)
SELECT e.*
FROM employee e
LEFT JOIN vehicle_assignment va ON e.employee_id = va.employee_id
WHERE va.employee_id IS NULL;
 
-- Query: Find available vehicles
SELECT v.*
FROM company_vehicle v
LEFT JOIN vehicle_assignment va ON v.vehicle_id = va.vehicle_id
WHERE va.vehicle_id IS NULL AND v.status = 'available';
 
-- Trigger to update vehicle status on assignment
CREATE TRIGGER after_assignment_insert
AFTER INSERT ON vehicle_assignment
FOR EACH ROW
    UPDATE company_vehicle SET status = 'assigned' 
    WHERE vehicle_id = NEW.vehicle_id;

Example 4: Geopolitical Data System

Domain: Global Geographic Information System

A geographic database tracks countries and their capitals for diplomatic, logistics, and reference purposes.

ER Model Analysis:

COUNTRY: Sovereign nations
CAPITAL_CITY: Capital cities (as a concept, not as a CITY entity)
Relationship: Each country has exactly one capital; each capital is the capital of exactly one country

Participation Analysis:

Can a COUNTRY exist without a capital? No — Every recognized country designates a capital
Can a designated CAPITAL_CITY exist without a country? No — A capital is defined by its country
Participation Pattern: Total-Total (1,1):(1,1)

Decision Framework Application:

Participation: Total-Total → Both strategies viable
Lifecycle coupling: Very strong—capital is inherent to country identity
Query coupling: Nearly always accessed together for country info
Security: No differentiation needed
Evolution: Will reliably remain 1:1

Decision: Merged Table Approach

The capital is effectively an attribute set of the country, not an independent entity. Merging is natural.

Geopolitical Merged Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Merged country table with capital attributes
CREATE TABLE country (
    country_id          INT PRIMARY KEY,
    country_code        CHAR(2) UNIQUE NOT NULL,  -- ISO 3166-1 alpha-2
    country_code_3      CHAR(3) UNIQUE NOT NULL,  -- ISO 3166-1 alpha-3
    country_name        VARCHAR(100) NOT NULL,
    official_name       VARCHAR(200),
    
    -- Core country data
    population          BIGINT,
    area_km2            DECIMAL(12, 2),
    continent           VARCHAR(20),
    region              VARCHAR(50),
    currency_code       CHAR(3),
    
    -- Capital attributes (merged from CAPITAL_CITY concept)
    capital_name        VARCHAR(100) NOT NULL,
    capital_lat         DECIMAL(9, 6),
    capital_lng         DECIMAL(9, 6),
    capital_population  INT,
    capital_timezone    VARCHAR(50),
    capital_elevation   INT,  -- meters above sea level
    
    -- Metadata
    independence_date   DATE,
    government_type     VARCHAR(100),
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Sample data
INSERT INTO country (
    country_id, country_code, country_code_3, country_name,
    population, area_km2, continent,
    capital_name, capital_lat, capital_lng, capital_population, capital_timezone
) VALUES
(1, 'FR', 'FRA', 'France', 67390000, 551695, 'Europe',
    'Paris', 48.8566, 2.3522, 2161000, 'Europe/Paris'),
(2, 'JP', 'JPN', 'Japan', 125800000, 377975, 'Asia',
    'Tokyo', 35.6762, 139.6503, 13960000, 'Asia/Tokyo'),
(3, 'BR', 'BRA', 'Brazil', 212600000, 8515767, 'South America',
    'Brasília', -15.7942, -47.8825, 3055000, 'America/Sao_Paulo');
 
-- Typical queries are simple (no join needed)
SELECT country_name, capital_name, capital_population
FROM country
WHERE continent = 'Europe';
 
-- Geographic queries straightforward
SELECT country_name, capital_name, capital_lat, capital_lng
FROM country
WHERE capital_lat BETWEEN -10 AND 10;  -- Countries with equatorial capitals

Alternative Design Consideration

Example 5: Document Checkout System

Domain: Legal Document Management

A law firm manages confidential documents that can be checked out for editing. Only one person can have a document checked out at a time to prevent conflicts.

ER Model Analysis:

DOCUMENT: Legal documents in the system
CHECKOUT_RECORD: Active checkout information
Relationship: A document may have an active checkout; each checkout is for exactly one document

Participation Analysis:

Can a DOCUMENT exist without an active checkout? Yes — Most documents are not actively being edited
Can a CHECKOUT_RECORD exist without a document? No — Checkouts are always for a specific document
Participation Pattern: Partial-Total (0,1):(1,1)

Decision Framework Application:

Participation: Partial-Total → Foreign key approach
FK Placement: CHECKOUT_RECORD (the total participation side)

This is a clear case: participation type directly determines the approach.

Document Checkout Schema
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Document entity (partial participation - not all are checked out)
CREATE TABLE document (
    document_id         INT PRIMARY KEY,
    title               VARCHAR(200) NOT NULL,
    document_type       VARCHAR(50) NOT NULL,
    client_id           INT NOT NULL,
    case_number         VARCHAR(50),
    security_level      VARCHAR(20) DEFAULT 'confidential'
                        CHECK (security_level IN ('public', 'internal', 'confidential', 'restricted')),
    created_date        DATE NOT NULL,
    created_by          INT NOT NULL,
    last_modified       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version             INT DEFAULT 1,
    file_path           VARCHAR(500) NOT NULL
);
 
-- Checkout entity (total participation - every checkout is for a document)
CREATE TABLE checkout_record (
    checkout_id         INT PRIMARY KEY AUTO_INCREMENT,
    document_id         INT UNIQUE NOT NULL,  -- FK + UNIQUE = 1:1
    checked_out_by      INT NOT NULL,
    checkout_time       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expected_return     TIMESTAMP,
    purpose             VARCHAR(500),
    workstation         VARCHAR(100),
    
    CONSTRAINT fk_document
        FOREIGN KEY (document_id)
        REFERENCES document(document_id)
        ON DELETE CASCADE,  -- If document deleted, checkout record goes too
        
    CONSTRAINT fk_user
        FOREIGN KEY (checked_out_by)
        REFERENCES employee(employee_id)
);
 
-- Check if document is available for checkout
SELECT d.document_id, d.title,
       CASE WHEN cr.checkout_id IS NULL THEN 'Available' ELSE 'Checked Out' END AS status,
       cr.checked_out_by, cr.checkout_time
FROM document d
LEFT JOIN checkout_record cr ON d.document_id = cr.document_id
WHERE d.document_id = ?;
 
-- Attempt to checkout (would fail if already checked out due to UNIQUE constraint)
INSERT INTO checkout_record (document_id, checked_out_by, purpose)
VALUES (?, ?, 'Client review edits');
-- If document already checked out, UNIQUE violation error
 
-- Check in document (delete the checkout record)
DELETE FROM checkout_record WHERE document_id = ? AND checked_out_by = ?;
 
-- Find all documents checked out by a user
SELECT d.document_id, d.title, cr.checkout_time
FROM checkout_record cr
JOIN document d ON cr.document_id = d.document_id
WHERE cr.checked_out_by = ?;
 
-- Trigger to update document last_modified on checkout
CREATE TRIGGER after_checkout_insert
AFTER INSERT ON checkout_record
FOR EACH ROW
    UPDATE document 
    SET last_modified = CURRENT_TIMESTAMP 
    WHERE document_id = NEW.document_id;

Example Comparison Summary

Let's summarize the five examples to reinforce pattern recognition:

1:1 Mapping Examples Summary
Domain	Relationship	Participation	Strategy	Key Factor
Healthcare	Patient ↔ Medical Record	Total-Total	Foreign Key	Security divergence
E-Commerce	User ↔ Profile	Total-Total	Merged Table	Strong coupling, same security
Fleet Mgmt	Employee ↔ Vehicle	Partial-Partial	Cross-Reference	NULL avoidance, relationship attrs
Geopolitical	Country ↔ Capital	Total-Total	Merged Table	Capital is attribute set of country
Legal Docs	Document ↔ Checkout	Partial-Total	Foreign Key	Participation determines placement

Pattern Recognition

Notice how participation patterns strongly correlate with strategy choices:

Common Mistakes to Avoid

Before concluding, let's highlight mistakes commonly made when mapping 1:1 relationships:

Common 1:1 Mapping Mistakes

•Forgetting UNIQUE constraint: A foreign key without UNIQUE creates 1:N, not 1:1. This is the most common error—test by attempting duplicate FK values.
•Placing FK in wrong table: FK in partial-participation table creates unnecessary NULLs. Always identify participation correctly first.
•Merging with partial participation: Creates NULL-laden rows that violate normalization and waste storage.
•Merging when security differs: If entities need different access controls, separate tables are required despite total-total participation.
•Over-engineering partial-partial: Not every partial-partial needs a cross-reference table. If NULLs are few and acceptable, simple FK is fine.
•Ignoring future evolution: Merging when 1:N is plausible creates painful refactoring later. When in doubt, keep separate.
•Not indexing FK appropriately: The UNIQUE constraint usually creates an index, but verify. FK columns should always be indexed.
•Wrong referential action: ON DELETE CASCADE when you should RESTRICT, or vice versa. Think through entity lifecycle implications.

Summary: Mastering 1:1 Mapping

Through theory and practical examples, you've now developed comprehensive expertise in mapping 1:1 relationships from ER diagrams to relational schemas.

Module Key Takeaways

•1:1 relationships enforce bidirectional uniqueness — Each entity instance associates with at most one instance of the related entity.
•Three strategies exist: Foreign key (two tables), merged table (one table), and cross-reference (three tables).
•Participation is the primary decision factor — Total vs. partial participation often determines the optimal strategy.
•Foreign key approach requires UNIQUE constraint — This enforces 1:1 cardinality on top of referential integrity.
•Merge only with total-total and strong coupling — Otherwise, NULLs or flexibility loss result.
•Cross-reference tables eliminate NULLs — Use for partial-partial with relationship attributes or NULL aversion.
•Consider evolution — Prefer flexibility when future requirements are uncertain.
•Document decisions — Future maintainers need to understand why, not just what.

Congratulations!

In subsequent modules, we'll explore 1:N and M:N relationship mapping, building on the foundations established here.

Module Complete

5 / 5