पाठ 10 / 35

Many-to-Many Relations and Join Tables

यह पाठ अभी हिंदी में अनुवादित नहीं है, इसलिए अंग्रेज़ी में दिखाया जा रहा है।

Model posts and tags with a join table, and know when to promote the join table to its own model with extra columns.

लेखक: · 15 साल से वेब डेवलपर

Two one-to-manys in disguise

A post can have many tags and a tag can label many posts. Neither table can hold the foreign key alone, so the database uses a join table (also called a junction or link table) with two foreign keys, one per side, usually with a composite primary key so a pair cannot repeat. ORMs can manage this table for you. Prisma's implicit many-to-many appears when both models have list fields pointing at each other; Prisma creates a hidden table such as _PostToTag. TypeORM uses @ManyToMany with @JoinTable() on the owning side. Sequelize uses belongsToMany on both models with the same through name. The moment you need extra data on the link itself, such as the date a student enrolled in a course or their grade, you should define the join table as a real model with its own fields and two one-to-many relations. This explicit form is more verbose but much easier to query and evolve.

Implicit vs explicit in Prisma

Implicit is fine for simple tags. Enrolment needs extra columns, so it becomes its own model with a composite key.

// implicit: Prisma manages the join table
model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}
model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

// explicit: the link carries data
model Enrollment {
  studentId  Int
  courseId   Int
  enrolledAt DateTime @default(now())
  grade      String?
  student    Student  @relation(fields: [studentId], references: [id])
  course     Course   @relation(fields: [courseId], references: [id])
  @@id([studentId, courseId])
}

TypeORM and Sequelize

Only one side gets @JoinTable() in TypeORM. In Sequelize, both belongsToMany calls must use the same through table.

// TypeORM: Post owns the join table
@ManyToMany(() => Tag, (tag) => tag.posts)
@JoinTable()
tags!: Tag[];

// Sequelize
Post.belongsToMany(Tag, { through: 'PostTags' });
Tag.belongsToMany(Post, { through: 'PostTags' });

Start explicit if you are unsure

Converting an implicit join table into an explicit model later means a migration that renames or rebuilds the table. If there is any chance the link will need a timestamp, a role or a status, model it explicitly from the start.

त्वरित जाँच: Students enrol in courses and you must store each student's grade per course. What is the best model?

  • A grades array column on the Student table
  • An implicit many-to-many between Student and Course
  • An explicit Enrollment model with studentId, courseId and grade
  • A courseId column on Student
Answer

An explicit Enrollment model with studentId, courseId and grade — The grade belongs to the pair (student, course), so it must live on the join table. That means making the join table an explicit model.