One-to-one

We have seen one-to-many (an author has many books) and many-to-many (a book has many tags and a tag has many books). There is one more relationship shape to cover: one-to-one.

A one-to-one relationship means each row in table A corresponds to exactly one row in table B. A user has one profile. An order has one shipping address. Your first instinct might be: why not just put all those columns in one table? And honestly, most of the time that is the right instinct. But there are specific situations where splitting makes sense, and knowing when to reach for this pattern is a useful skill.

When to use one-to-one

Optional data. A user has a required name and email. A profile has an optional bio, avatar_url, and website. If most users never fill in their profile, you end up with a lot of NULL columns sitting in the users table. A separate profiles table keeps things cleaner. If a user has no profile, there is simply no row in profiles, rather than a bunch of empty columns.

Large data. A user has a small name and email that get queried on nearly every request. They might also have a large bio text that is almost never read. Keeping the bio in a separate table means listing users does not load big text blobs you do not need.

Different access patterns. User settings are read and written by the user themselves. User data is read by many parts of the system. Splitting them means you can apply different caching or access control strategies to each side.

Writing migration 005

Let’s say we want to let users fill in optional profile details: a bio, an avatar URL, a website, and a location. We already have a bio column on users from migration 002, but imagine we decide that does not scale once we want to add the rest of the profile fields. Instead of stuffing them all onto users, we split them out.

Here is the migration:

-- migrations/005_create_profiles.sql
BEGIN;

CREATE TABLE profiles (
  user_id INTEGER PRIMARY KEY,
  bio TEXT,
  avatar_url TEXT,
  website TEXT,
  location TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO schema_migrations (version, name)
VALUES ('005', 'create_profiles');

COMMIT;

Notice something important: profiles uses user_id as both the primary key and the foreign key. That is a nice trick for one-to-one. Because primary keys are unique by definition, each user can have at most one profile. No two rows in profiles can share a user_id. The foreign key on top of that enforces that the profile belongs to a real user, and ON DELETE CASCADE makes sure the profile disappears if the user is deleted.

Also notice what this migration does not do: it does not remove the old bio column from users. In a real codebase you would probably want a follow-up migration to copy existing bio data into profiles and then drop the old column. That is a common pattern, and migrations are what make it safe: each step is its own file, applied in order, easy to review.

Add some sample data

Before we query, let’s give one of our existing users a profile and leave another without one, because that asymmetry is exactly what makes the LEFT JOIN demonstration meaningful. We will use Bob and Eve, both of whom were inserted back in the inserting-data lesson and are still around. We refer to them by email rather than by id, because earlier lessons inserted, replaced, and deleted users in ways that make id-based lookups unreliable across a real workflow (Alice in particular has a different id by now, thanks to INSERT OR REPLACE).

-- Bob gets a profile (look him up by email)
INSERT INTO profiles (user_id, bio, avatar_url, website)
SELECT id, 'Reads every Hemingway novel. Twice.', 'https://example.com/avatars/bob.png', 'https://bob.example.com'
FROM users WHERE email = '[email protected]';

That INSERT INTO ... SELECT ... FROM users WHERE email = ? pattern is worth pausing on. The SELECT produces one row (Bob’s id, plus the literal values for the other columns), and the INSERT writes that row into profiles. We never had to know Bob’s id ourselves. From here on we use the same trick anywhere we need to point at a specific user: look them up by email, let the database fill in the id.

Eve still has no profile. That is exactly the state most users in a real app are in.

Querying

Bob has a profile, so the LEFT JOIN returns the profile data alongside the user data:

SELECT users.name, users.email, profiles.bio, profiles.avatar_url
FROM users
LEFT JOIN profiles ON users.id = profiles.user_id
WHERE users.email = '[email protected]';

name  email            bio                                 avatar_url
----  ---------------  ----------------------------------  ------------------------------------
Bob   [email protected]  Reads every Hemingway novel. Twice. https://example.com/avatars/bob.png

Eve does not have a profile, so the same query against her email returns her user row with NULL for the profile columns:

SELECT users.name, users.email, profiles.bio, profiles.avatar_url
FROM users
LEFT JOIN profiles ON users.id = profiles.user_id
WHERE users.email = '[email protected]';

name  email            bio   avatar_url
----  ---------------  ----  ----------
Eve   [email protected]  NULL  NULL

Eve still appears in the result, but with NULL for the profile columns. That is the LEFT JOIN doing its job: every user shows up, profile columns are filled in when there is a profile and NULL when there is not.

[!NOTE] We use LEFT JOIN (not INNER JOIN) because the profile is optional. An INNER JOIN would exclude users who have no profile at all. LEFT JOIN includes every user and fills in NULL for the missing profile columns.

Updating and upserting

Bob already has a profile, so we can practice updating it and demonstrating the upsert pattern. The UPDATE is destructive enough to wrap in a transaction; the upsert is a single atomic statement, so it does not need a wrapper.

-- Update Bob's profile, wrapped so we can preview before committing
BEGIN;
UPDATE profiles
SET bio = 'Reads every Hemingway novel. Three times now.'
WHERE user_id = (SELECT id FROM users WHERE email = '[email protected]');
SELECT user_id, bio FROM profiles
WHERE user_id = (SELECT id FROM users WHERE email = '[email protected]');
COMMIT;

-- Upsert: create if not exists, update if exists.
-- Bob already has a profile so this hits the DO UPDATE path.
INSERT INTO profiles (user_id, bio)
VALUES (
  (SELECT id FROM users WHERE email = '[email protected]'),
  'Reads every Hemingway novel. Many times now.'
)
ON CONFLICT(user_id) DO UPDATE SET bio = excluded.bio;

-- Same upsert, but for Eve. Since Eve has no profile yet,
-- this hits the INSERT path and creates one.
INSERT INTO profiles (user_id, bio)
VALUES (
  (SELECT id FROM users WHERE email = '[email protected]'),
  'Wrote a thesis on Le Guin.'
)
ON CONFLICT(user_id) DO UPDATE SET bio = excluded.bio;

The upsert is the part worth dwelling on. The ON CONFLICT ... DO UPDATE pattern is particularly useful for one-to-one relationships. The first time a user sets their bio, it inserts a new row. Every time after that, it updates the existing row. Your application code does not need to check whether the profile exists first. Less branching, fewer bugs. We ran the same upsert against Bob (who already had a profile) and Eve (who did not), and the same statement did the right thing in both cases.

Signup as a transaction

A common place one-to-one relationships show up is signup: you create a new user row and an empty profile row for them at the same time, so every user in the system is guaranteed to have a profile the rest of the app can point at. That is two inserts that must stick together, which means a transaction:

BEGIN;
  INSERT INTO users (name, email) VALUES ('Chen', '[email protected]');
  INSERT INTO profiles (user_id) VALUES (last_insert_rowid());
COMMIT;

If the profile insert fails, the user insert rolls back too, and you do not end up with a user that the rest of the app assumes has a profile but does not. This is the exact pattern the transactions lesson called out, now shipped against real tables.

When to just use one table

If the related data is small, almost always present, and accessed together with the main data, skip the split. You are just adding join complexity for no real benefit:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  bio TEXT,            -- optional profile data, inline
  avatar_url TEXT,     -- no separate table needed
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Simpler queries. No joins. Fewer tables. This is the right choice when the data is small and frequently accessed alongside the user data. One-to-one splits are an optimization, not a default.

Exercises

Exercise 1: Write migration 005_create_profiles.sql and run it. Insert a user without a profile. Query with LEFT JOIN. Verify the profile columns are NULL.

Exercise 2: Add a profile for the user. Query again. Verify the profile data appears.

Exercise 3: Try inserting two profiles for the same user. Verify the primary key prevents it.

We now know all three relationship types: one-to-many, many-to-many, and one-to-one. We have also written migrations for each one, so our database has actually grown as we learned. In the next lesson, we will take a step back and look at the formal framework behind all of these design decisions: normalization.