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

Why AI Coders Need to Understand Database Relationships

When you prompt AI to "build me a blog with users and posts," the AI creates multiple database tables and connects them. Most of the time it works fine — until you try to delete a user and the app crashes because their posts still exist, or you query for "all tags on this post" and get nothing back.

Database relationships are the backbone of almost every real application. Understanding them lets you:

• Debug the "foreign key constraint violation" errors that stop deployments cold
• Know when to ask AI for a join table vs. a simple foreign key
• Understand why deleting one record breaks other records
• Write better prompts that produce correct database schemas from the start

You don't need to memorize SQL JOIN syntax. You do need to understand why relationships exist and what they protect.

Real Scenario: Building a Blog Platform

This single prompt contains three different relationship types hidden inside it. Let's look at what AI generates and break down each one.

What AI Generated (Prisma Schema)

// schema.prisma
// Tested with Prisma 5.x + PostgreSQL 16

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())

  // One-to-many: one user → many posts
  posts     Post[]
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String
  published   Boolean   @default(false)
  createdAt   DateTime  @default(now())

  // Many-to-one: many posts → one user (foreign key)
  authorId    Int
  author      User      @relation(fields: [authorId], references: [id])

  // Many-to-many: posts ↔ tags (via join table)
  tags        PostTag[]
}

model Tag {
  id    Int       @id @default(autoincrement())
  name  String    @unique

  // Many-to-many: tags ↔ posts (via join table)
  posts PostTag[]
}

// Join table for the many-to-many relationship
model PostTag {
  postId Int
  tagId  Int

  post   Post @relation(fields: [postId], references: [id])
  tag    Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])  // Composite primary key
}

Three relationships, three different structures. Let's understand each one.

Understanding Each Relationship Type

1. One-to-Many (Most Common)

One user can write many posts. But each post has exactly one author. This is a one-to-many relationship — the most common type in real apps.

How it works: The "many" table stores a reference to the "one" table. In SQL terms, the posts table has an authorId column that stores the user's ID. This column is a foreign key.

Notice that authorId: 1 appears twice in posts — that's the "many" side. User 1 (Chuck) has two posts. The foreign key points back to the user.

What the foreign key enforces: You cannot insert a post with authorId: 99 if user 99 doesn't exist. The database rejects it. This is referential integrity — the database guarantees your data makes sense.

2. Many-to-Many (Requires a Join Table)

A post can have many tags. A tag can belong to many posts. Neither side "owns" the other. This is a many-to-many relationship, and you cannot model it with a simple foreign key.

Why? Because a single column can't store multiple values cleanly. Where would you put the tag IDs on the post? As a comma-separated string? That breaks SQL's fundamental rule: one value per cell.

The solution is a join table (also called a junction table, pivot table, or bridge table). The PostTag table in the schema above is that join table. It only has two columns: postId and tagId. Each row says "post X is related to tag Y."

Post 1 has tags "ai" AND "css." Post 2 has tag "css." Tag "css" belongs to two posts. The join table makes this clean.

3. One-to-One (Less Common)

One user has exactly one profile. One profile belongs to exactly one user. Neither side can exist without the other (in most designs). This is a one-to-one relationship.

// Example: user ↔ profile (one-to-one)
model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?  // Optional: user might not have a profile yet
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?

  userId Int    @unique  // @unique enforces one-to-one
  user   User   @relation(fields: [userId], references: [id])
}

The key is @unique on the userId column. This prevents two profiles from claiming the same user — which would turn it into one-to-many.

Raw SQL Equivalent

If you're using raw SQL instead of Prisma, the same relationships look like this:

-- One-to-many: users → posts
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  content TEXT,
  author_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Many-to-many: posts ↔ tags
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

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

Notice ON DELETE CASCADE — this is the cascade rule that AI frequently omits.

Querying Relationships

In Prisma (ORM)

// Get all posts by a user with their tags
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        tags: {
          include: {
            tag: true  // Go through the join table to get tag data
          }
        }
      }
    }
  }
})

// Create a post with tags in one operation
const post = await prisma.post.create({
  data: {
    title: "Understanding AI Tools",
    content: "...",
    authorId: 1,
    tags: {
      create: [
        { tag: { connect: { name: "ai" } } },  // Link existing tag
        { tag: { create: { name: "tutorial" } } }  // Create new tag
      ]
    }
  }
})

In Raw SQL

-- Get all posts with author name (one-to-many JOIN)
SELECT 
  posts.title,
  posts.content,
  users.name AS author_name
FROM posts
JOIN users ON posts.author_id = users.id
WHERE users.id = 1;

-- Get all tags for a post (many-to-many JOIN through join table)
SELECT 
  posts.title,
  tags.name AS tag_name
FROM posts
JOIN post_tags ON posts.id = post_tags.post_id
JOIN tags ON post_tags.tag_id = tags.id
WHERE posts.id = 1;

The many-to-many query requires TWO joins: one to the join table, one to the destination table. AI sometimes generates only one join and wonders why the query returns nothing.

What AI Gets Wrong About Database Relationships

1. Missing Foreign Key Constraints

AI sometimes creates relationships in code (Prisma, SQLAlchemy) without enforcing them in the actual database. The ORM thinks the relationship exists; the database doesn't. Result: orphaned records that break queries.

Fix: Always check your migration files. Look for FOREIGN KEY or REFERENCES keywords in the generated SQL. If they're missing, ask AI to "add database-level foreign key constraints."

2. No Cascade Rules

When you delete a user, what happens to their posts? Without cascade rules, the database throws a foreign key constraint error and refuses the deletion. AI often forgets to add ON DELETE CASCADE or ON DELETE SET NULL.

-- With cascade: deleting user also deletes their posts
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE

-- With SET NULL: deleting user sets post.author_id to null
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL

-- Without anything: error when you try to delete the user

3. N+1 Query Problem

AI frequently generates code that queries the database in a loop — one query to get all posts, then one query per post to get the author. For 100 posts, that's 101 database queries.

// BAD: N+1 queries (AI commonly generates this)
const posts = await prisma.post.findMany()
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } })
  // This runs once per post — 100 posts = 100 extra queries
}

// GOOD: One query with include
const posts = await prisma.post.findMany({
  include: { author: true }  // Single JOIN query
})

4. Circular Relationships Without Planning

AI sometimes creates relationships where Table A references Table B, and Table B references Table A. This creates a chicken-and-egg problem: you can't create A without B, and can't create B without A. Always make sure one side of a relationship is optional (? in Prisma, NULL in SQL) to break the cycle.

How to Debug Relationship Errors with AI

Choosing the Right Relationship Type

What to Learn Next

Frequently Asked Questions