Database Relationships

Real-world analogy

What is it?

Database relationships define how tables (or collections) connect to each other. Instead of cramming everything into one giant table, you split data into related tables — Users, Posts, Comments — and link them with foreign keys. This normalization reduces duplication, maintains consistency, and enables powerful queries like 'find all posts by users who joined this month'. Every real application depends on well-designed relationships.

Real-world relevance

Twitter: Users → Tweets (one-to-many), Users ↔ Users via Follows (many-to-many). E-commerce: Products → Reviews (one-to-many), Orders ↔ Products via OrderItems (many-to-many with quantity). GitHub: Users → Repos (one-to-many), Repos ↔ Users via Stars (many-to-many). Every app you use daily is a web of interconnected data relationships.

Key points

• One-to-One (1:1) — Each record in Table A links to exactly one record in Table B, and vice versa. Example: a User has one Profile. In Prisma, use @relation with a unique foreign key. One-to-one is less common but useful for splitting large tables or optional data (not every user needs billing info on the main table).
• One-to-Many (1:N) — One record in Table A links to many records in Table B. This is the most common relationship. Example: one User has many Posts. The 'many' side holds the foreign key (Post has userId). In Prisma: User has 'posts Post[]' and Post has 'author User @relation(fields: [authorId], references: [id])'.
• Many-to-Many (M:N) — Records in Table A link to many records in Table B, and vice versa. Example: Students enroll in many Courses, and each Course has many Students. This requires a join table (enrollment) that holds both foreign keys. Prisma can create implicit join tables automatically or you can define explicit ones for extra fields like enrolledAt.
• Foreign Keys — A foreign key is a column that references the primary key of another table. It enforces referential integrity — you can't create a Post with a userId that doesn't exist. In Prisma, @relation(fields: [authorId], references: [id]) defines the link. Foreign keys are the glue that holds relational data together.
• Cascading Deletes & Updates — When you delete a User, what happens to their Posts? Cascade: delete all posts too. SetNull: keep posts but clear authorId. Restrict: block deletion if posts exist. In Prisma, use onDelete: Cascade, SetNull, or Restrict. Choose carefully — cascading deletes in production can wipe out more data than intended!
• Eager vs Lazy Loading — Eager loading fetches related data immediately (include: { posts: true }). Lazy loading fetches only when accessed. Prisma uses explicit eager loading with 'include' — no hidden queries. Always be intentional: don't load 1000 posts when you only need the user's name. The N+1 problem (querying each relation separately in a loop) is the #1 performance killer.
• Self-Relations — A table can reference itself. Example: an Employee has a managerId pointing to another Employee. Or a Comment has a parentId for threaded replies. In Prisma, define two relation fields on the same model. Self-relations power org charts, category trees, threaded comments, and file/folder hierarchies.
• Querying Relations with Prisma — Prisma makes relation queries readable. Find user with posts: prisma.user.findUnique({ where: { id }, include: { posts: true } }). Filter by relation: prisma.post.findMany({ where: { author: { email: { contains: 'gmail' } } } }). Nested creates: prisma.user.create({ data: { name: 'Ali', posts: { create: [{ title: 'Hello' }] } } }).

Code example

// ── Prisma Schema: All Relationship Types ──

// 1:1 — User has one Profile
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  profile   Profile? // optional 1:1
  posts     Post[]   // 1:N
  courses   Enrollment[] // M:N via explicit join
  createdAt DateTime @default(now())
}

model Profile {
  id     String @id @default(cuid())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String @unique // @unique makes it 1:1
}

// 1:N — User has many Posts
model Post {
  id       String    @id @default(cuid())
  title    String
  content  String
  author   User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId String
  comments Comment[] // 1:N (Post has many Comments)
}

// Self-relation — Comment replies
model Comment {
  id       String    @id @default(cuid())
  text     String
  post     Post      @relation(fields: [postId], references: [id])
  postId   String
  parent   Comment?  @relation("Replies", fields: [parentId], references: [id])
  parentId String?
  replies  Comment[] @relation("Replies")
}

// M:N — Users enroll in Courses (explicit join table)
model Course {
  id          String       @id @default(cuid())
  title       String
  enrollments Enrollment[]
}

model Enrollment {
  id         String   @id @default(cuid())
  user       User     @relation(fields: [userId], references: [id])
  userId     String
  course     Course   @relation(fields: [courseId], references: [id])
  courseId    String
  enrolledAt DateTime @default(now())
  grade      Float?

  @@unique([userId, courseId]) // prevent duplicate enrollment
}

// ── Querying Relations ──
// Eager load user with posts and profile
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    profile: true,
    posts: { orderBy: { createdAt: 'desc' }, take: 10 },
  },
});

// Nested create: user + profile + first post in one query
const newUser = await prisma.user.create({
  data: {
    email: 'ali@example.com',
    name: 'Ali',
    profile: { create: { bio: 'Full-stack dev' } },
    posts: { create: [{ title: 'My First Post', content: 'Hello!' }] },
  },
  include: { profile: true, posts: true },
});

// Filter by relation
const gmailPosts = await prisma.post.findMany({
  where: { author: { email: { endsWith: '@gmail.com' } } },
  include: { author: { select: { name: true } } },
});

Line-by-line walkthrough

1. 1. Comment: Prisma Schema with all relationship types
2. 2.
3. 3. Comment: 1:1 relationship section
4. 4. User model — the central entity
5. 5. Auto-generated cuid ID
6. 6. Unique email field
7. 7. User name
8. 8. Optional one-to-one Profile relation
9. 9. One-to-many: user has many posts
10. 10. Many-to-many: user enrollments via join table
11. 11. Creation timestamp
12. 12. Close User model
13. 13.
14. 14. Profile model — 1:1 with User
15. 15. Profile ID
16. 16. Optional bio text
17. 17. Optional avatar URL
18. 18. @relation to User with cascading delete
19. 19. @unique on userId makes this 1:1 (not 1:N)
20. 20. Close Profile model
21. 21.
22. 22. Comment: 1:N relationship section
23. 23. Post model — many posts belong to one user
24. 24. Post ID
25. 25. Post title
26. 26. Post content
27. 27. @relation to User (author) with cascade delete
28. 28. Foreign key: authorId references User.id
29. 29. One-to-many: post has many comments
30. 30. Close Post model
31. 31.
32. 32. Comment: Self-relation for threaded replies
33. 33. Comment model with self-referencing relation
34. 34. Comment ID
35. 35. Comment text content
36. 36. Relation to parent Post
37. 37. Foreign key to Post
38. 38. Optional parent comment (null = top-level)
39. 39. Optional parentId for threading
40. 40. Child replies via named 'Replies' relation
41. 41. Close Comment model
42. 42.
43. 43. Comment: M:N explicit join table section
44. 44. Course model
45. 45. Course ID
46. 46. Course title
47. 47. Link to enrollments
48. 48. Close Course model
49. 49.
50. 50. Enrollment — explicit join table for User and Course
51. 51. Enrollment ID
52. 52. Relation to User
53. 53. Foreign key: userId
54. 54. Relation to Course
55. 55. Foreign key: courseId
56. 56. Extra field: enrollment date (only with explicit join)
57. 57. Extra field: optional grade
58. 58.
59. 59. @@unique prevents duplicate enrollment
60. 60. Close Enrollment model
61. 61.
62. 62. Comment: Querying Relations section
63. 63. Comment: Eager loading example
64. 64. findUnique to get a single user by ID
65. 65. where clause with userId
66. 66. include eagerly loads related data
67. 67. Include profile (1:1)
68. 68. Include posts sorted by newest, limit 10
69. 69. Close include
70. 70. Close findUnique query
71. 71.
72. 72. Comment: Nested create example
73. 73. Create user + profile + post in one transaction
74. 74. data object with user fields
75. 75. User email
76. 76. User name
77. 77. Nested create: profile created alongside user
78. 78. Nested create: first post created alongside user
79. 79. Close data
80. 80. Include created relations in response
81. 81. Close create query
82. 82.
83. 83. Comment: Filter by relation example
84. 84. Find posts where author matches a condition
85. 85. where: filter by author's email ending
86. 86. include author but select only name field
87. 87. Close filter-by-relation query

Spot the bug

model User {
  id    String @id @default(cuid())
  email String @unique
  posts Post[]
}

model Post {
  id       String @id @default(cuid())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId String
}

// Query
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
  console.log(user.email, posts.length);
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Prisma Relations (Prisma)
• Prisma One-to-Many (Prisma)
• Database Design Course (freeCodeCamp)