Entity Relationships & the ORM

Why this is the heart of backend work

Real applications are about related data: a user has posts, an order has line items, a student enrolls in many courses. The backend's job is to model those relationships so the database enforces them and your code can read and write them safely. This page goes: the four relationship shapes → how each maps to tables (foreign keys, join tables) → how you declare them in an ORM → and the loading and transaction gotchas that cause most real bugs. It builds on SQL joins and ACID transactions.

The four relationship shapes

Three insights make all of this click:

1. 1:N and N:1 are one relationship seen from two ends. A post has one author (N:1); an author has many posts (1:N). A single foreign key — posts.author_id — expresses both. Naming them separately just states which side you're looking from.
2. The foreign key always lives on the "many" side. Many posts point to one user, so user_id lives on posts. You never store a list of post IDs on the user row — a column can't hold "many."
3. Many-to-many needs a third table. A student has many courses and a course has many students, so no single FK works. You introduce a junction table enrollment(student_id, course_id) — which turns one N:M into two 1:N relationships.

At the table level (the universal truth)

Before any ORM, this is just SQL — every framework compiles down to this:

-- One-to-One: a profile row points to exactly one user (UNIQUE makes it 1:1, not 1:N)
CREATE TABLE profiles (
  id       BIGINT PRIMARY KEY,
  user_id  BIGINT NOT NULL UNIQUE REFERENCES users(id),
  bio      TEXT
);

-- One-to-Many / Many-to-One: the FK lives on the MANY side (posts)
CREATE TABLE posts (
  id         BIGINT PRIMARY KEY,
  author_id  BIGINT NOT NULL REFERENCES users(id),   -- many posts → one user
  title      TEXT
);

-- Many-to-Many: a junction table, with the pair as the primary key
CREATE TABLE enrollment (
  student_id BIGINT REFERENCES students(id),
  course_id  BIGINT REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

The REFERENCES clause is the foreign key — the database now guarantees you can't insert a post for a user who doesn't exist (referential integrity), and ON DELETE decides what happens to children when a parent is removed (CASCADE deletes them, RESTRICT blocks it, SET NULL orphans them).

Declaring it in the ORM

An ORM (Object-Relational Mapper) maps a class (an entity) to a table and a field to a column, so you work with objects instead of writing SQL by hand. Here are the same three shapes in the three stacks this track covers.

Spring Boot / JPA (Java):

@Entity
class User {
  @Id @GeneratedValue Long id;

  @OneToOne(mappedBy = "user", cascade = CascadeType.ALL)
  Profile profile;                       // 1:1

  @OneToMany(mappedBy = "author")        // 1:N — inverse side, no FK column here
  List<Post> posts = new ArrayList<>();

  @ManyToMany
  @JoinTable(name = "enrollment",
    joinColumns = @JoinColumn(name = "student_id"),
    inverseJoinColumns = @JoinColumn(name = "course_id"))
  Set<Course> courses = new HashSet<>(); // N:M
}

@Entity
class Post {
  @Id @GeneratedValue Long id;
  @ManyToOne @JoinColumn(name = "author_id")   // N:1 — OWNING side, holds the FK
  User author;
}

FastAPI / SQLAlchemy (Python):

class User(Base):
    __tablename__ = "users"
    id = Column(BigInteger, primary_key=True)
    posts = relationship("Post", back_populates="author")          # 1:N
    courses = relationship("Course", secondary="enrollment",       # N:M via junction
                           back_populates="students")

class Post(Base):
    __tablename__ = "posts"
    id = Column(BigInteger, primary_key=True)
    author_id = Column(ForeignKey("users.id"))                     # the FK column (N:1)
    author = relationship("User", back_populates="posts")

Node / Prisma (schema):

model User {
  id      Int      @id @default(autoincrement())
  posts   Post[]                       // 1:N
  courses Course[] @relation("Enrollment")   // N:M (Prisma manages the join table)
}
model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int                          // the FK (N:1)
}

The loading gotcha: lazy vs eager, and N+1

When you load a User, should its posts come too?

• Lazy (the default for collections): fetch posts only when you first access them.
• Eager: fetch them in the same query as the user.

Lazy loading causes the single most common ORM performance bug — the N+1 query problem:

1 query :  SELECT * FROM users LIMIT 100;          -- load 100 users
N queries: for each user → SELECT * FROM posts WHERE author_id = ?;   -- 100 more!
           = 101 queries to show 100 users and their posts

The fix is to fetch the relationship in one query — a JOIN FETCH (JPA), selectinload / joinedload (SQLAlchemy), or include (Prisma) — turning 101 queries into 1 or 2. Don't "fix" it by making everything eager: that over-fetches on the pages that don't need the children. Name "N+1" out loud in an interview and reach for an explicit fetch — it's a senior signal.

Creating linked entities inside one transaction

This is where relationships meet transactions. When you create a parent and its children together — an Order with its OrderItems — three things must hold:

1. Atomicity: all rows commit together or none do. If item 3 fails a constraint, the order and items 1–2 must roll back too — otherwise you've saved a half-order. Wrap the whole operation in one transaction (@Transactional, a SQLAlchemy session commit, or Prisma's $transaction). (See ACID.)
2. Insert order: the parent must exist before a child's foreign key can point at it. The ORM's unit of work orders the INSERTs for you (parent first, then children).
3. Cascade: cascade means "apply this operation to related entities." With CascadeType.PERSIST, saving the order saves its items in the same unit of work, so you don't save each child by hand.

@Transactional                                  // one transaction for the whole graph
public Order placeOrder(Long userId, List<Item> items) {
  Order order = new Order(userRepo.getReferenceById(userId));
  for (Item i : items) order.addItem(new OrderItem(order, i));  // sets BOTH sides
  return orderRepo.save(order);   // cascade persists the items; all-or-nothing on commit
}

If anything inside throws, the transaction rolls back and the database is untouched — no orphaned order, no half-written items.