One-to-One Relationships

Build ER diagrams with one-to-one relationships, understand when to use them, and implement them correctly in BookVault's database schema.

Understanding One-to-One Relationships

One-to-one relationships are the least common type of database relationship. But they solve specific problems elegantly. Think of it this way: each record in Table A connects to exactly one record in Table B, and vice versa.

Why not just put everything in one table then? Good question. Performance is usually the answer. Or security. Sometimes you need to separate data that logically belongs together.

Each user has exactly one profile. Each profile belongs to exactly one user. That's a one-to-one relationship.

Common One-to-One Scenarios

Real companies use one-to-one relationships more than you'd think. Here are the most common patterns:

BookVault Example: Author Details

BookVault's authors table contains basic information. But what about detailed biographies, social media links, and publication history? That's perfect for a one-to-one relationship.

Why split this? The main authors table stays fast for book listings. Profile data loads only when someone views an author's detailed page. Smart performance optimization.

Implementing One-to-One in SQL

Creating one-to-one relationships requires careful foreign key placement. You have two options, but one is clearly better.

Here's how to create the BookVault author profile relationship:

-- Create the author_profiles table for BookVault
-- Links to existing authors table via foreign key
CREATE TABLE author_profiles (
    profile_id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL UNIQUE,
    biography TEXT,
    website_url VARCHAR(255),
    twitter_handle VARCHAR(50),
    awards_json JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign key creates the one-to-one relationship
    CONSTRAINT fk_author_profile 
        FOREIGN KEY (author_id) 
        REFERENCES authors(author_id)
        ON DELETE CASCADE
);

CREATE TABLE
Table "author_profiles" created successfully.
Constraint "fk_author_profile" added.
Unique constraint on "author_id" enforces one-to-one relationship.

Adding Sample Data

Now populate both tables to see the relationship in action:

-- Insert authors first (parent table)
INSERT INTO authors (first_name, last_name, nationality) VALUES
('J.K.', 'Rowling', 'British'),
('Stephen', 'King', 'American'),
('Agatha', 'Christie', 'British');

-- Then insert profiles for some authors
INSERT INTO author_profiles (author_id, biography, website_url, twitter_handle) VALUES
(1, 'British author best known for the Harry Potter series...', 
 'https://www.jkrowling.com', '@jk_rowling'),
(2, 'American author of horror, supernatural fiction, and fantasy...', 
 'https://stephenking.com', '@StephenKing');

INSERT 0 3
INSERT 0 2

Authors table now has 3 records.
Author_profiles table has 2 records.
Notice: Agatha Christie has no profile yet - that's allowed in one-to-one.

Querying One-to-One Relationships

Querying one-to-one relationships looks similar to other joins, but with important differences. You'll often use LEFT JOIN because the dependent table might not have matching records.

-- Get all authors with their profile information
-- LEFT JOIN because not all authors have profiles yet
SELECT 
    a.first_name,
    a.last_name,
    a.nationality,
    p.biography,
    p.website_url,
    p.twitter_handle
FROM authors a
LEFT JOIN author_profiles p ON a.author_id = p.author_id
ORDER BY a.last_name;

first_name | last_name | nationality | biography              | website_url        | twitter_handle
-----------|-----------|-------------|------------------------|--------------------|--------------
Agatha     | Christie  | British     | NULL                   | NULL               | NULL
Stephen    | King      | American    | American author of...  | stephenking.com    | @StephenKing
J.K.       | Rowling   | British     | British author best... | jkrowling.com      | @jk_rowling

Filtering for Complete Records

Sometimes you only want authors who have detailed profiles:

-- Find authors who have complete profile information
-- INNER JOIN excludes authors without profiles
SELECT 
    a.first_name || ' ' || a.last_name AS author_name,
    LENGTH(p.biography) AS bio_length,
    CASE 
        WHEN p.website_url IS NOT NULL THEN 'Has website'
        ELSE 'No website'
    END AS website_status
FROM authors a
INNER JOIN author_profiles p ON a.author_id = p.author_id
WHERE p.biography IS NOT NULL;

author_name   | bio_length | website_status
--------------|------------|---------------
J.K. Rowling  | 52         | Has website
Stephen King  | 59         | Has website

When NOT to Use One-to-One

Honestly, most developers overuse one-to-one relationships. Here's when to avoid them:

The BookVault author profile example is borderline. In a real system, you'd measure query performance before splitting. But it's educationally perfect for understanding one-to-one concepts.

Alternative: JSON Columns

Modern databases support JSON columns. Sometimes that's cleaner than one-to-one relationships:

-- Alternative: store profile data as JSON in authors table
-- Simpler structure but less queryable
ALTER TABLE authors 
ADD COLUMN profile_data JSON;

-- Update with profile information
UPDATE authors 
SET profile_data = '{"biography": "British author best known...", "website": "jkrowling.com"}'
WHERE author_id = 1;

ALTER TABLE
UPDATE 1

Profile data now stored as JSON in the main authors table.
Trade-off: simpler structure but harder to query specific profile fields.

JSON columns work well when profile data is mostly display-only. Use separate tables when you need to query, index, or join on profile fields frequently.

Real-World Examples

Major platforms use one-to-one relationships strategically:

Notice the pattern: one-to-one relationships solve specific architectural problems. They're not just "extra tables for organization."

Quiz