skip to content

For a growing SQLAlchemy 2.1 Payment hierarchy, how do you choose between single-table and joined-table inheritance, and which loading rules do you set?

level: principalimportance: should knowfreq 20%

answer

  1. nullable columns versus extra joins
  2. who filters on subtype columns
  3. the two forms can be mixed
  4. inline for single, selectin for joined
  5. of_type on subtype joins

basics

~20 s

Single-table keeps base queries on one table and lets base-class filters reach subtype columns, but every subtype column must be nullable. Joined keeps NOT NULL per subtype at the cost of joins and later loads; set polymorphic_load either way.

solid answer

~40 s

I start from the queries. If listings across all payments dominate and subtypes add a few small columns, single-table wins: one table, `select(CardPayment)` is just `WHERE type IN (...)`, and a base query can filter on `CardPayment.card_last4`. The price is that subtype columns must be `Mapped[X | None]` on a widening table, so required fields need check constraints. If subtypes carry many or required columns, or the base table is too hot to keep altering, joined-table keeps NOT NULL and foreign keys per subtype but adds joins, per-table writes and the need for `of_type()` or `with_polymorphic()` when filtering subtype columns. SQLAlchemy lets one hierarchy mix both. Whatever I pick, every subclass sets `polymorphic_load` (`inline` for single, `selectin` for joined) so listings have a bounded statement count.

code

python · 43 lines
python
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Payment(Base):
    __tablename__ = "payment"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]
    type: Mapped[str] = mapped_column(String(20))
    __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "payment"}


class CardPayment(Payment):
    # joined: its own table, NOT NULL enforced by the database
    __tablename__ = "card_payment"
    id: Mapped[int] = mapped_column(ForeignKey("payment.id"), primary_key=True)
    card_last4: Mapped[str] = mapped_column(String(4))
    __mapper_args__ = {"polymorphic_identity": "card", "polymorphic_load": "selectin"}


class WalletPayment(Payment):
    # single-table: no __tablename__, so wallet_ref is added to payment
    wallet_ref: Mapped[str | None] = mapped_column(String(64))
    __mapper_args__ = {"polymorphic_identity": "wallet", "polymorphic_load": "inline"}


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
print({name: list(t.c.keys()) for name, t in Base.metadata.tables.items()})

with Session(engine) as session, session.begin():
    session.add_all(
        [CardPayment(amount_cents=1999, card_last4="4242"), WalletPayment(amount_cents=750, wallet_ref="w-81")]
    )

engine.echo = True
with Session(engine) as session:
    payments = session.scalars(select(Payment).order_by(Payment.id)).all()
    print([(type(p).__name__, getattr(p, "card_last4", None) or p.wallet_ref) for p in payments])

go deeper

for a junior

Know the two shapes: one shared table with a type column, or a table per subtype joined to the base, and that SQLAlchemy maps both with the same mapper arguments.

for a middle

Explain what each form emits for base and subclass selects, why single-table columns are nullable, and what polymorphic_load inline and selectin do.

for a senior

Show the operational costs you have lived with: hot-table ALTERs, per-row subtype loads, cartesian products after a storage change, and how the rules prevent them.

for a principal

Choose by query mix, column count and integrity needs, allow mixing, and write the defaults (polymorphic_load, of_type, identity naming) into the team's mapping rules.

## The decision a lead owns A `Payment` family that started with cards and bank transfers will keep growing: wallets, vouchers, instalment plans. In SQLAlchemy the storage choice is mostly a **mapping** choice, so it is cheap to write and expensive to change once data and queries depend on it. The real question is which costs the team prefers to carry every day: **nullable subtype columns and one wide table**, or **extra tables, joins and a loading policy**. There is no universally right answer; the signals below decide it. ## How little the code differs Both forms share the same base class and the same `__mapper_args__`: - the base names the table and sets `polymorphic_on`; each instantiable subclass sets `polymorphic_identity`; - a **joined** subclass adds `__tablename__` and a primary key that is a `ForeignKey` to the base; a **single-table** subclass omits `__tablename__`, and its columns move onto the base table; - the two can be **mixed** in one hierarchy: omit `__tablename__` on any subclass to make it single-inheriting under a joined base, or give a deeper subclass its own table; - only **one** discriminator may be configured per hierarchy, and `polymorphic_abstract=True` gives intermediate groupings a mapped class without an identity. ## What each choice costs in SQLAlchemy | Concern | Single-table | Joined-table | |---|---|---| | `select(Payment)` | one table, no joins | base table only, subtype columns loaded later | | `select(CardPayment)` | `WHERE type IN (...)` | `JOIN card_payment` | | Filter on a subtype column from a base query | works; ORM adds the `type IN` criterion | needs `of_type()` or `with_polymorphic()`, else a cartesian product | | `NOT NULL` on subtype columns | impossible on the shared table; map them as optional and add a check constraint | enforced per subtype table | | New subtype | adds columns to a hot, ever-wider table | adds a new table, base untouched | | Writes | one INSERT or DELETE per object | one statement per table in the object's path, unless the mapper's `passive_deletes` relies on `ON DELETE CASCADE` | | Recommended `polymorphic_load` | `"inline"` for subtypes read often | `"selectin"` for subtypes read often | ## Signals that favour each - **Single-table** when subtypes add a handful of small columns, listings across all payments are the hot path, and analysts want one table to read. - **Joined-table** when subtypes carry many or required columns, compliance needs `NOT NULL` and foreign keys per subtype, or the base table is large enough that every `ALTER TABLE` on it is an operational event. - **Mixed** when a few heavy subtypes deserve their own tables and many light ones do not; SQLAlchemy supports it without special configuration. - **Concrete** only when subtypes are almost never listed together; a polymorphic base query then becomes a `UNION ALL`. ## Rules worth writing down Whichever form you choose, the cost the team actually feels comes from defaults nobody set. A short rule set keeps it bounded: 1. Every subclass declares `polymorphic_load` explicitly, so a base-class listing has a known statement count instead of one extra `SELECT` per row. 2. Criteria on a subtype column after a relationship join always go through `of_type()`, so moving a subtype from single to joined storage cannot silently change results. 3. Single-table subtype columns are annotated `Mapped[X | None]`, and any subtype-required column gets a table check constraint such as `type != 'card' OR card_last4 IS NOT NULL`. 4. Identity strings are short, stable data values reviewed like column names; a class may be renamed, its identity may not without a data migration. 5. Groupings such as "card-like" use `polymorphic_abstract=True` rather than a second discriminator, which SQLAlchemy does not support. ## Changing your mind later Because a hierarchy can be mixed, a migration can move **one subtype at a time**: give the subclass a `__tablename__`, create its table, copy its columns out of the base, then drop them. The mapping change is a few lines; the data move is a schema migration and a deploy plan, which is why the choice deserves a decision up front rather than a default. Moving the other way, from joined to single-table, is the same work in reverse and lands on the widest, busiest table, so one cautious path is to start light subtypes single-table and promote a subtype to its own table once its columns grow.

  • What breaks if a subtype moves from single-table to joined storage?
    Queries that filter a base-class select, or a relationship join, on that subtype's columns without `of_type()` or `with_polymorphic()`: the column's table now enters the FROM clause unjoined, SQLAlchemy warns of a cartesian product and the rows are wrong. Listings that read its columns also gain per-object SELECTs unless the subclass sets `polymorphic_load="selectin"`.
  • How do you keep a required subtype column honest in single-table storage?
    Map it as nullable, because the shared table cannot require it, and add a check constraint on the base table, such as `type != 'card' OR card_last4 IS NOT NULL`, declared in `__table_args__` with `CheckConstraint`. Validation in application code helps, but only the constraint protects rows written outside the ORM.
  • When would you reject both and use separate unrelated mappings?
    When nothing ever lists or relates to payments as one family. Then each subtype is simply its own mapped class and table, with no discriminator, no unions and no polymorphic loading to configure; inheritance mapping only pays off when code queries or relates to the base type.

  • Joined-table inheritance is always slower, so single-table is always the right default.
  • Single-table inheritance lets the database enforce NOT NULL on each subtype's columns.
  • A hierarchy must be entirely single-table or entirely joined-table.
  • Loading defaults can be left alone because SQLAlchemy loads subtype columns eagerly.
  • A second discriminator column can split a subtree into its own family.