Lesson 9 / 35

One-to-Many and One-to-One Relations

Model a user who writes many posts, place the foreign key on the correct side and choose a sensible delete behaviour.

By · 15 years in web development

The foreign key lives on the many side

In a one-to-many relation such as user to posts, the foreign key column goes on the many side: each post stores authorId. The user table needs no extra column, because a list of IDs inside one cell would break normalisation. ORMs let you declare the relation from both sides so you can navigate either way: Prisma uses a list field posts Post[] plus @relation(fields: [authorId], references: [id]) on the post; TypeORM uses @OneToMany on the user and @ManyToOne on the post; Sequelize uses User.hasMany(Post) and Post.belongsTo(User). A one-to-one relation is the same idea with a unique constraint on the foreign key, so at most one profile points at each user. Also decide what happens on delete: CASCADE removes the posts with the user, RESTRICT blocks deleting a user who still has posts, and SET NULL keeps the posts but clears the author, which needs a nullable column.

Declaring the relation in TypeORM and Sequelize

Both sides are declared so you can load user.posts and post.author. The as alias in Sequelize must match what you later use in include.

// TypeORM
@Entity()
export class Post {
  @PrimaryGeneratedColumn() id!: number;
  @Column() title!: string;
  @ManyToOne(() => User, (user) => user.posts, { onDelete: 'CASCADE' })
  author!: User; // creates an authorId foreign key column
}
// in User: @OneToMany(() => Post, (post) => post.author) posts!: Post[];

// Sequelize
User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

Class teacher and students

A class teacher has many students, but each student has exactly one class teacher. You write the teacher's name on each student's ID card rather than writing forty names on the teacher's card. The ID card field is the foreign key.

Index your foreign keys

PostgreSQL does not automatically index foreign key columns. If you often run WHERE author_id = ? or join on it, add an index: @@index([authorId]) in Prisma, @Index() on the column in TypeORM, or indexes: [{ fields: ['authorId'] }] in Sequelize model options.

Quick check: For a relation 'a course has many lectures', which table should hold the foreign key?

  • courses, as an array of lecture IDs
  • A third join table is always required
  • Both tables, pointing at each other
  • lectures, as a courseId column
Answer

lectures, as a courseId column — In one-to-many, the many side (lectures) stores the key of the one side (course). Join tables are only needed for many-to-many.