Modeling Defect Scenarios Key Points

A modeling defect is a gap between what a schema actually allows and what the domain actually requires, and it rarely announces itself as a schema problem.

It shows up instead as a wrong dashboard number, a support ticket about a broken link, a deploy that stalls under load, or a migration nobody wants to touch.

This page is a diagnosis framework: a way to recognize which category of modeling defect you are looking at before reaching for a fix, so the specific incident pages in this section read as instances of a small number of recurring patterns rather than as an unrelated list of bugs.

Summary

Core Idea: Modeling defects fall into a small number of recurring categories, and diagnosing the category is the first step, before attempting any fix.
Why It Matters: Fixing the visible symptom without identifying the underlying category tends to leave the same defect free to resurface somewhere else in the schema.
Key Concepts: integrity defect, cardinality defect, temporal defect, evolution defect, symptom distance.
When to Use: Any time a production incident, a wrong report, or a strange query result seems to trace back to how data is shaped rather than how it was queried.
Limitations / Trade-offs: This framework helps diagnosis, but it does not replace the specific fix each defect category requires.
Related Topics: referential integrity, join cardinality, timezone handling, enum and type evolution.

Foundations

An integrity defect is a missing or incomplete rule that lets the database hold data the domain considers invalid, most commonly a missing foreign key that allows orphan rows once a parent is deleted.

A cardinality defect is a mismatch between the relationship a query assumes and the relationship the schema actually has, most commonly a join that silently turns one-to-many into many-to-many and inflates an aggregate.

A temporal defect is a gap in how the schema represents time, most commonly storing timestamp without time zone for values that need an unambiguous instant.

An evolution defect is friction introduced by how a type or constraint was designed to change over time, most commonly a PostgreSQL ENUM that requires a schema-level ALTER TYPE for every new business value.

These four categories are not exhaustive of every possible schema mistake, but they cover the large majority of defects that produce real production incidents in relational schemas.

Each category has a recognizable signature in how it surfaces, and learning that signature is what makes diagnosis fast instead of exploratory.

Mechanics & Interactions

The defining trait of a modeling defect is symptom distance: the gap between where the defect lives (the schema) and where it becomes visible (a dashboard, a support ticket, a deploy).

A missing foreign key does not raise an error when a parent row is deleted, it simply leaves child rows pointing at nothing, and the symptom appears later as broken links or comments attached to deleted posts.

-- Detect orphan rows a missing FK allowed
SELECT c.comment_id
FROM comments c
LEFT JOIN posts p ON p.post_id = c.post_id
WHERE p.post_id IS NULL;

A cardinality defect does not raise an error either, because the query runs and returns a result, it is simply the wrong number, which is why fan-out inflation is so often caught by finance or analytics noticing a total that does not match an external source of truth.

Diagnosing a defect starts by asking which category the symptom's shape matches, before asking what query or code path produced it.

A wrong aggregate total points toward a cardinality defect; a record that should not exist points toward an integrity defect; a value off by exactly one or a few hours points toward a temporal defect; a deploy blocked or slowed by a migration points toward an evolution defect.

That first classification step narrows the investigation from "somewhere in this codebase" to a specific, well-understood category with known causes and known fixes.

Advanced Considerations & Applications

Each category has a different relationship to prevention, which matters when deciding where to invest review effort.

Integrity defects are the most preventable through DDL alone: a foreign key constraint, once added, structurally cannot be violated, so the review question is whether every *_id-shaped column that references another table actually has one.

Cardinality defects are harder to prevent structurally, because the schema can be perfectly correct while a specific query still assumes the wrong relationship; prevention here leans on query review and EXPLAIN output more than on constraints.

Temporal defects are largely preventable by a single standing rule (store timestamptz, convert at display time), which is cheap to enforce in review but expensive to retrofit once a table has years of ambiguous local-time data in it.

Evolution defects are preventable by choosing a representation upfront that expects to change, such as text with a CHECK constraint instead of ENUM, trading a small amount of validation strength for the ability to add values without a blocking schema change.

Category	Typical Symptom	Root Cause	Structural Fix
Integrity	Orphan rows, broken links, impossible states	Missing or incomplete foreign key or constraint	Add `REFERENCES` with an explicit `ON DELETE` action
Cardinality	Inflated aggregates, duplicated rows in results	Query assumes 1:N where the real relationship is M:N	Aggregate before joining, or model the join explicitly
Temporal	Off-by-hours values, DST-season support spikes	`timestamp without time zone` storing ambiguous local time	Store `timestamptz`, convert to local time only at display
Evolution	Deploys blocked or slowed by a migration	`ENUM` or rigid type requiring schema-level change per new value	`text` + `CHECK`, or an expand-contract enum strategy

Turning a diagnosed incident into a lasting fix usually means two separate actions: correcting the specific instance, and adding a check (a lint rule, a migration review step, a CI query) that catches the same category before it reaches production again.

Treating each incident as a one-off fix without that second step is how the same category of defect keeps reappearing under a different table name.

Common Misconceptions

"If a query runs without an error, the underlying data model is fine." Cardinality and temporal defects both produce results that look valid and run without error, while still being wrong, which is exactly what makes them dangerous.
"Missing foreign keys are a minor style issue." A missing FK is a structural integrity gap that allows the database to hold states the domain considers impossible, and it tends to surface much later and further from its cause than a typical bug.
"Fan-out join bugs are a query-writing mistake, not a modeling issue." The query is the trigger, but the underlying condition, a relationship being genuinely many-to-many where the schema or team assumed one-to-many, is a modeling fact the query merely exposed.
"Timestamp bugs only matter for globally distributed users." Even a single-region product hits DST transitions twice a year, and timestamp without time zone is ambiguous during those transitions regardless of how many time zones the user base spans.
"ENUM is always the right type for a fixed set of values." ENUM is fine for truly fixed, rarely-changing sets, but a status column that gains new values as the business evolves turns every addition into a schema-level migration event.

FAQs

What is a modeling defect, in one sentence?

A gap between what a schema actually allows and what the domain actually requires, which surfaces as a symptom rather than as a direct error.

It is the underlying cause behind most of the incident scenarios documented in this section.

What are the four defect categories this page describes?

Integrity defects: missing rules that allow invalid states, like orphan rows.
Cardinality defects: queries assuming the wrong relationship shape.
Temporal defects: ambiguous or incomplete time representation.
Evolution defects: types or constraints that resist adding new values.

Why don't modeling defects usually raise an error?

Most of them produce results that are technically valid SQL output, just factually wrong or incomplete, rather than triggering a constraint violation.

That is what makes "symptom distance" the key diagnostic idea on this page.

How do I know which category an incident belongs to?

Match the symptom shape to a category: a wrong aggregate total points to cardinality, an impossible or orphaned record points to integrity, an off-by-hours value points to temporal, and a blocked deploy points to evolution.

Why does classifying the defect matter before fixing it?

Each category has a different structural fix, and treating the visible symptom without identifying the category tends to leave the schema free to produce the same defect somewhere else.

Classification turns a broad investigation into a targeted one.

Are missing foreign keys really this common a cause of production incidents?

Yes - a missing FK is one of the most preventable defects, because the fix is a single constraint, yet it remains common because the absence of a constraint produces no immediate error to catch it.

What makes cardinality defects harder to prevent than integrity defects?

A schema can be perfectly correct while a specific query still assumes the wrong relationship between tables, so no constraint alone can prevent it.

Prevention depends more on query review and reading EXPLAIN output than on DDL.

Why is timestamp without time zone considered a modeling defect and not just a type choice?

It stores a value with no recorded offset, which makes the same stored value represent different real instants depending on interpretation.

That ambiguity is exactly the kind of domain-versus-schema gap this page defines as a modeling defect.

How does ENUM cause an evolution defect?

Adding a new value to a PostgreSQL ENUM is a DDL event (ALTER TYPE ... ADD VALUE) that can briefly lock dependent operations, which turns a routine business change (a new status value) into a deploy-sensitive schema migration.

What should happen after fixing one instance of a modeling defect?

Add a check, such as a lint rule, migration review step, or a recurring CI query, that catches the same category of defect before it reaches production again.

Fixing only the specific instance leaves the category free to reappear elsewhere.

Can a single incident involve more than one defect category?

Yes - a fan-out join bug (cardinality) is sometimes discovered because it also relied on data that should have been rejected by a missing constraint (integrity).

Diagnosing the primary category first still helps scope the investigation even when a second category is involved.

Is this framework meant to replace the specific defect pages in this section?

No - this page gives the categories and the diagnostic approach, while the other pages document specific, concrete instances (missing foreign keys, fan-out joins, timestamp bugs, enum migrations) in full detail.

Read this page first to know which specific page to reach for.

Defect Scenarios Basics - hands-on examples across all four categories
Missing Foreign Keys - integrity defects in detail
Fan-Out Join Explosion - cardinality defects in detail
Timestamp Without Timezone Bugs - temporal defects in detail
Enum Migration Pain - evolution defects in detail
Data Modeling Foundations - the modeling process these defects deviate from

Stack versions: This page is conceptual and not tied to a specific stack version, though the ENUM migration behavior described applies to PostgreSQL 18.4 (stable 18, maintenance 17).