Give a many-to-many table extra columns with an association object

My orders and products already connect fine through a plain many-to-many table, but the business also needs the quantity and the price recorded for every single line in an order.

Beginner to intermediateRead 6 minDoing: an afternoon, with your project open8 steps

▶ Start

What you'll have at the end

A join-table row that stores quantity and unit price per line, queryable from either the order or the product side

Before you start

You need

Product and Order model classes already mapped to live tables in SQLAlchemy, connected through a plain many-to-many relationship (the automatic join table SQLAlchemy's secondary argument manages on its own), with a migration tool already wired up and able to apply schema changes.

Not covered

Choosing a numeric type for the price column or handling currency rounding isn't covered here; use whatever type your other money columns already use.

Leans on

8 steps

Where to go next

Find and fix the N+1 queries hiding in a nested API response

once an order's line items get read in a loop across many orders, this is where those extra queries get caught and fixed.

Make a multi-step write one transaction

creating an order and every one of its line items as separate writes is exactly the kind of multi-step save that belongs inside a single transaction.

Checked 18 Aug 2026

Part of the ORM and Database Abstraction Tools cookbook