ORM Relationships — Core Concepts

Why relationships matter

Relational databases store data across normalized tables — users in one table, orders in another, products in a third. Relationships define how rows in one table connect to rows in another. Without them, you’d write manual SQL joins for every connected query, duplicating logic throughout your codebase.

Python ORMs like SQLAlchemy and Django’s ORM let you define these connections once in your models, then navigate between related objects with simple attribute access.

The three relationship types

One-to-Many (the most common)

A single parent record connects to multiple child records. Example: one author writes many books.

In the database, the “many” side holds a foreign key pointing back to the “one” side. The books table has an author_id column.

From the author’s perspective, you access author.books and get a list. From a book’s perspective, book.author returns the single author object.

One-to-One

Like one-to-many, but each parent has exactly one child. Example: a user has one settings profile.

The foreign key still lives on one side, but the ORM enforces (or expects) that only one child record exists per parent. It’s really a special case of one-to-many with a uniqueness constraint.

Many-to-Many

Both sides can have multiple connections. Example: students enroll in courses, and courses have many students.

This requires a junction table (also called an association table) — a third table with two foreign key columns linking the two main tables. Neither the students table nor the courses table has a foreign key to the other. The junction table enrollment has student_id and course_id.

How it works in SQLAlchemy

SQLAlchemy uses the relationship() function to define how models connect:

One-to-many: Define a ForeignKey on the child, then relationship() on the parent. The back_populates parameter creates the reverse link.

Many-to-many: Create an association table with two foreign keys, then use relationship() with a secondary parameter pointing to that table.

One-to-one: Same as one-to-many, but pass uselist=False to tell SQLAlchemy the result is a single object, not a list.

How it works in Django

Django embeds relationship definitions into field types:

• ForeignKey creates one-to-many
• OneToOneField creates one-to-one
• ManyToManyField creates many-to-many (Django auto-creates the junction table)

Django automatically creates reverse accessors. If a Book model has ForeignKey(Author), then author.book_set.all() returns all related books without any extra configuration.

Key concepts

Lazy vs. eager loading

By default, most ORMs load related objects lazily — only when you access them. Calling author.books triggers a database query at that moment.

This is convenient but dangerous in loops. If you load 100 authors and access .books on each one, you execute 101 queries (1 for authors + 100 for books). This is the N+1 problem, and it’s the most common performance issue with ORM relationships.

Eager loading fetches related data upfront in fewer queries. Both SQLAlchemy and Django provide mechanisms for this.

Cascading behavior

What happens to child records when a parent is deleted? Cascading rules control this:

• CASCADE: Delete children automatically (user deleted → their orders deleted)
• SET NULL: Set the foreign key to NULL (user deleted → orders kept but unlinked)
• RESTRICT: Prevent deletion if children exist
• SET DEFAULT: Set the foreign key to a default value

Choosing the wrong cascade behavior leads to either orphaned records or unexpected data loss.

Common misconception

“Many-to-many relationships don’t need a junction table if you structure your data correctly.”

They always need one in a relational database. Some ORMs hide the junction table from you (Django creates it automatically), but it exists. And if you need to store extra data about the relationship — like when a student enrolled in a course, or what grade they got — you need an explicit junction model, not a hidden auto-generated table.

One thing to remember: Choose the simplest relationship type that fits your data model. One-to-many handles most cases. Reach for many-to-many only when both sides truly have multiple connections, and always consider whether the relationship itself carries data.