Single Table or Multi-Table Inheritance for Thing Model

[ksmuczynski]

---

status: Accepted
date: 2025-7-29

ADR: Single Table or Multi-Table Inheritance for Thing Model

Context and Problem Statement

The Bureau is developing a new database schema to more effectively store data associated with hydrogeologic monitoring stations or Things. A Thing is a generic entity with common properties (name, date installed). There also exist specific, mutually exclusive subtypes of stations, such as Wells and Precipitation Stations. Each subtype has its own unique set of attributes that do not overlap.

• Wells have a total depth and a measuring point height
• Precipitation has orifice diameter and material

The core architectural problem is to model this generic-parent-to-unique-child relationship in a way that:

• Enforces the mutual exclusivity rule (a Thing can be a Well or a Precipitation Station, but not both).
• Provides good query performance.
• Is clear and maintainable for future development.

The system must be able to efficiently query for all Things, as well as filter and retrieve Things of a specific type with their unique attributes. We anticipate adding more Thing types in the future (e.g., Spring, Soil), each with its own set of unique fields. We need a database schema design that accommodates this inheritance-like data model cleanly and efficiently, and can also apply to other similar instances of parent-child related tables (e.g. Sample, Observation).

Decision Drivers

• Data Integrity: The schema must prevent inconsistent data, especially the creation of a Thing that is multiple subtypes.
• Query Performance: The solution should allow for efficient retrieval of both generic and specific station data (e.g., "find all Things installed before 2021-01-01 ")
• Simplicity: The chosen solution should be relatively simple to understand, implement, and maintain. The chosen schema should apply to other similar instances of parent-child related tables (e.g. Sample, Observation). The database schema and the corresponding application-level code should not be overly complex.
• Extensibility: Adding new Thing types in the future should be a straightforward process with minimal schema migrations and application code changes.

Considered Options

1. Multi-Table Inheritance (MTI) / Class Table Inheritance: Have a base Thing table for common attributes. Each Thing type gets its own separate table (e.g., well, precipitation) with a foreign key back to the products table. These specific tables hold the unique attributes for that type.
2. Single-Table Inheritance (STI): Use a single Thing table for all Thing types. A thing_type column will differentiate between them. All possible fields for all types exist in this one table, with fields being NULL for types where they don't apply.
3. JSON Field / Unstructured Data: Use a single Thing table with an attributes column of type JSONB or JSON. This column would store a JSON object containing the type-specific fields.

Decision Outcome

Chosen Option: Single-Table Inheritance (STI).

We will implement a single Thing table. This decision is based on the following rationale:

• Superior Query Performance for Common Cases: STI excels at fetching heterogeneous collections of products. Queries that operate on common attributes (name, date installed) or that fetch all products do not require any JOIN operations, making them significantly faster and less complex than the MTI approach.
• Simplicity of Implementation: The data access logic is simpler. A single repository or data access object can handle all Thing types, with conditional logic to handle the different thing_type values. This avoids the complexity of managing multiple tables and JOINs inherent in MTI.
• Exclusivity at the database level: Guarantees a Thing can only be of one type.
• Ease of Extension: Adding a new Thing type (e.g., Spring) involves adding a new value to the thing_type enum and adding new nullable columns to the Thing table. While this requires a schema migration, it's a non-breaking change and is generally simpler than creating an entirely new table and updating queries to JOIN with it.

While we acknowledge the drawback of having potentially many NULL values, the data integrity benefit of guaranteeing that a Thing can only be one subtype, along with the performance benefits for our most common query patterns and the overall simplicity of the model, outweigh this concern for our current scale and foreseeable future. The JSON field approach was deemed too flexible, sacrificing the data integrity and queryability that a structured relational model provides. The MTI approach failed to ensure that a Thing can only be one subtype.

Consequences

Positive

• Fast Queries: Queries for all products or filtering on common attributes will be very fast.
• Simplified Codebase: Application logic for CRUD operations on products can be centralized.
• Exclusivity at the database level: Guarantees a Thing can only be of one type.
• Easy to Get Started: The initial implementation is straightforward.

Negative

• Sparse Table: The Thing table will have many NULL values, which can feel "messy" and use slightly more disk space than a perfectly normalized MTI schema.
• Schema Rigidity: Adding a new Thing type with many new fields requires adding multiple columns to a potentially very large table, which can be a slow migration process.
• Weaker Data Integrity: It's not possible to enforce a NOT NULL constraint on a type-specific field (e.g., casing diameter must be non-null for Well but must be null for Precipitation) at the database level without complex check constraints. This logic must be enforced by the application.
• Column Limit: Relational databases have a limit on the number of columns per table. This approach could hit that limit if we add many product types with many distinct attributes.

Pros and Cons of the Options

1. Multi-Table Inheritance (MTI)

Pros:

• Clean, normalized schema. No NULL values for fields that don't apply.
• Strong data integrity. Subtype-specific fields can have NOT NULL constraints.
• Scales well to a large number of types without making one table too wide.

Cons:

• Fails to enforce exclusivity. The database would allow a single Thing to be both a Well and a Precipitation Station.
• Requires JOIN operations to retrieve a complete product object, which is more complex and can be slower.
• Fetching a list of different product types requires complex queries (e.g., multiple LEFT JOINs or UNIONs).
• Adding a new type requires creating a new table.

2. Single-Table Inheritance (STI)

Pros:

• Excellent performance for polymorphic queries (fetching different types at once).
• No JOINs needed for most common operations.
• Simple to understand and implement initially.
• Strong data integrity, guaranteeing that a Thing can only be of one type.

Cons:

• Leads to sparse tables with many NULL values.
• Cannot enforce NOT NULL constraints on subtype-specific fields at the DB level easily.
• Table can become very wide, potentially hitting column limits.

3. JSON Field

Pros:

• Extremely flexible. No schema migrations needed to add new attributes or types.
• Keeps the main table narrow.

Cons:

• Difficult to query the data within the JSON blob efficiently and consistently across all database systems.
• No database-level data integrity (type checking, constraints) for the attributes inside the JSON.
• Indexing fields within the JSON is more complex.
• Increases the burden on application code to validate and process the unstructured data.