No history yet

Single Table Inheritance

The One Table Approach

In object-oriented programming, inheritance is a powerful tool. A Manager is an Employee. An Engineer is also an Employee. When mapping these relationships to a database, one common pattern is Single Table Inheritance (STI). With STI, you store all variations of a base class—like Employee, Manager, and Engineer—in a single database table.

The core idea is simple: one table holds all related object types. A special column, called a discriminator, tells you what kind of object each row represents.

Imagine a table named employee. It would have columns for attributes shared by all employees, like id and name. It would also have a type column. For a regular employee, this column might say 'employee'. For a manager, it would say 'manager'. This discriminator is the key to making STI work.

Implementing STI in SQLAlchemy

SQLAlchemy's declarative system makes setting up STI straightforward. You start by defining a base class, like Employee. In this class, you'll define the columns shared by all subclasses and the special discriminator column. We'll call ours type.

Then, you use the __mapper_args__ dictionary to configure the inheritance. There are two key parameters:

  • polymorphic_on: This tells SQLAlchemy which column to use as the discriminator. We'll point it to our type column.
  • polymorphic_identity: This specifies the value that identifies a row as an instance of this specific class. For the base Employee class, we'll use the string 'employee'.
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = 'employee'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    # The discriminator column
    type: Mapped[str]

    __mapper_args__ = {
        # Use the 'type' column to determine the class
        'polymorphic_on': type,
        # This class is identified by the value 'employee'
        'polymorphic_identity': 'employee',
    }

Now we can define our subclasses. A Manager inherits from Employee. It doesn't need to redefine the id, name, or type columns. It only needs to declare its own polymorphic_identity within its __mapper_args__.

Handling Subclass Attributes

What if a manager has a special attribute that other employees don't, like manager_data? Or an engineer has engineer_info? With STI, these unique attributes become new columns in the same employee table.

This leads to the central trade-off of STI. For a row representing an Engineer, the manager_data column will be NULL. For a Manager row, the engineer_info column will be NULL. If you have many subclasses with many unique attributes, your table can become "sparse," with lots of empty cells.

class Manager(Employee):
    # Manager-specific column
    manager_data: Mapped[str | None]

    __mapper_args__ = {
        # This class is identified by the value 'manager'
        'polymorphic_identity': 'manager',
    }

class Engineer(Employee):
    # Engineer-specific column
    engineer_info: Mapped[str | None]

    __mapper_args__ = {
        # This class is identified by the value 'engineer'
        'polymorphic_identity': 'engineer',
    }

Notice that manager_data and engineer_info are defined as nullable (str | None). This is crucial. Since any given row will only belong to one subclass, the columns for other subclasses must allow NULL values.

The magic of SQLAlchemy is that it handles the filtering for you. When you query for Manager objects, it automatically adds a WHERE type = 'manager' clause to the SQL query. You don't have to think about the discriminator column; you just interact with your Python classes.

# This Python code...
query = select(Manager)

# ...generates this SQL.
# SELECT employee.id, employee.name, employee.type, employee.manager_data 
# FROM employee 
# WHERE employee.type = 'manager'

STI is a powerful pattern when your class hierarchy is relatively simple and subclasses don't have a large number of unique, non-overlapping attributes. It keeps queries fast and simple at the cost of potentially creating a wide, sparse table.

Quiz Questions 1/5

What is the primary purpose of the discriminator column in Single Table Inheritance (STI)?

Quiz Questions 2/5

In a SQLAlchemy STI setup, you define a Manager subclass of Employee. The Manager has a unique attribute called manager_data. Why must the manager_data column in the database table be nullable?