Database Relationships Explained: One-to-One, One-to-Many, Many-to-Many

Most of an app's data is connected: users have orders, orders have items, posts have tags. In a relational database you model these connections as relationships between tables. There are three kinds.

One-to-many (the most common)

One user has many orders. Each order belongs to exactly one user.

You model it by putting a foreign key on the "many" side — each order stores the ID of its user:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text UNIQUE NOT NULL
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  total numeric(10,2) NOT NULL
);

CREATE INDEX ON orders (user_id);

REFERENCES users(id) is the foreign key: the database won't allow an order pointing at a user who doesn't exist. (Primary key vs foreign key)

The index makes "find this user's orders" fast. Postgres does not create it automatically. (Database indexes)

Other examples: a blog post has many comments; a company has many employees.

Many-to-many

A post can have many tags, and a tag can be on many posts. A foreign key on either side can't express that.

You add a third table — a join table — with a row for each connection:

CREATE TABLE posts (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text);
CREATE TABLE tags  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text UNIQUE);

CREATE TABLE post_tags (
  post_id bigint REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  bigint REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

The composite primary key stops the same tag being added to a post twice. (ON DELETE CASCADE)

The join table can also hold extra details about the relationship: for students and courses, the enrollments table might store enrolled_at and grade.

Other examples: users and teams, products and categories, actors and films.

One-to-one

Each user has exactly one profile. Often you'd just put those columns on the users table. A separate table makes sense when the data is optional, large, or accessed separately:

CREATE TABLE profiles (
  user_id bigint PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio text,
  avatar_url text
);

Using the foreign key as the primary key guarantees one profile per user.

Querying across relationships

You combine related tables with joins:

-- each order with its user's email
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id;

-- all tags on post 7
SELECT t.name
FROM tags t
JOIN post_tags pt ON pt.tag_id = t.id
WHERE pt.post_id = 7;

(SQL joins explained)

In an ORM

ORMs describe the same relationships in code. In Prisma:

model User {
  id     Int     @id @default(autoincrement())
  orders Order[]
}

model Order {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int
}

Then prisma.user.findMany({ include: { orders: true } }) fetches users with their orders. Watch out for loading related rows one at a time in a loop — the N+1 query problem. (What is an ORM?)

Mistakes to avoid

1. Storing lists in a text column — tags: "red,blue,green". You can't query, index or validate it properly. Use a join table.
2. No foreign key constraint, just an ID column. Nothing stops orphaned rows pointing at deleted users.
3. Forgetting the index on the foreign key column.
4. Copying data instead of referencing it — storing the user's email on every order, then it changes. (Database normalization)

---

EasySpawn gives each app its own Postgres database, with Claude Code on the same server to design tables, write migrations and explain the relationships an AI builder created. See how it works or join the waitlist.

Related: How to Design Your First Database · Primary Key vs Foreign Key · SQL Joins Explained · Database Normalization