Data Modeling Patterns

What it is

Data modeling patterns are proven structural approaches for representing domain entities and their relationships in a specific database model. Each pattern addresses a recurring problem — how to store hierarchical data, how to represent polymorphic entities with varying attributes, how to model many-to-many relationships efficiently, how to store schemaless attributes alongside structured fields — with tradeoffs in query expressiveness, write performance, storage, and maintainability.

The most common patterns span five database model categories: Relational (PostgreSQL, MySQL) — normalized tables with foreign keys, optimized for ACID transactions and flexible querying; Document (MongoDB, DynamoDB) — embedded sub-documents, optimized for entity-centric reads; Key-Value (Redis, DynamoDB) — hash-keyed record access, optimized for single-record reads at scale; Wide-Column (Cassandra, HBase) — partition key + clustering key, optimized for time-series and high-write workloads; Graph (Neo4j, Neptune) — nodes and edges, optimized for relationship traversal.

Within relational databases, structural patterns for complex data shapes include: Adjacency List (parent_id foreign key) and Nested Set (left/right boundary integers) for tree/hierarchy storage; Entity-Attribute-Value (EAV) for sparse, user-defined attributes; JSONB hybrid for mixing structured columns with schemaless payloads; Polymorphic association for single-table entities with varying types.

Why it exists

Domain data rarely fits cleanly into flat tables with fixed columns. Organizational hierarchies are trees. Product catalogs have varying attributes per category. Access logs are time-series with high write volume. Social connections are graphs. Using the wrong structural pattern produces schemas that require expensive query rewrites, cause join explosions, resist indexing, or require application-level workarounds that shift complexity out of the database into code. Each pattern exists because it solves a specific structural mismatch between domain shape and relational (or other) model constraints.

When to use

• Adjacency List: Use for tree structures (org charts, comment threads, categories) where you need to find a node's immediate parent or children, and tree depth is bounded and shallow (<10 levels); simple to update (change parent_id on reparent).
• Nested Set: Use for trees where you frequently query all descendants of a node (entire subtree) and updates are infrequent — subtree read is a single range scan, not a recursive join.
• EAV (Entity-Attribute-Value): Use for truly sparse, user-defined attributes where the set of attributes per entity varies widely and is not known at schema design time — product catalogs with 200+ configurable attribute types where each product has 5–20.
• JSONB hybrid: Use in PostgreSQL when most attributes are structured and queryable, but a subset of fields are genuinely schemaless or variable — store the structured fields as columns (indexed, typed) and the variable payload as a JSONB column (GIN-indexed for containment queries).
• Wide-column (Cassandra) for time-series: Use when the primary access pattern is "all events for entity X in time range T1–T2" at high write volume — partition by entity_id, cluster by timestamp for efficient range scans per entity.

When not to use

• Don't use EAV for well-known, fixed attribute sets — it sacrifices type safety, indexing, and query clarity for schema flexibility that isn't needed; use typed columns instead.
• Don't use Nested Set for frequently updated hierarchies — every insert or move requires updating left/right values for potentially all nodes in the tree, causing write amplification and lock contention.
• Don't default to document embedding just because the data is related — deeply nested documents in MongoDB become problematic when sub-documents need to be updated independently or queried in isolation without fetching the parent.
• Don't model many-to-many in a document database by embedding arrays of IDs — querying from either direction requires loading the owning document and filtering in the application, or creating redundant indexes that must be kept synchronized.

Typical architecture

  1. Adjacency List (relational hierarchy):
  categories(id, name, parent_id REFERENCES categories(id))
  -- Fast: find children of node X
  SELECT * FROM categories WHERE parent_id = $id;
  -- Recursive subtree (PostgreSQL CTE):
  WITH RECURSIVE subtree AS (
    SELECT * FROM categories WHERE id = $root
    UNION ALL
    SELECT c.* FROM categories c JOIN subtree s ON c.parent_id = s.id
  ) SELECT * FROM subtree;

  2. Nested Set (efficient subtree reads):
  categories(id, name, lft INT, rgt INT)
  -- All descendants of node with lft=4, rgt=19:
  SELECT * FROM categories WHERE lft >= 4 AND rgt <= 19;
  -- Depth: (rgt - lft - 1) / 2 = number of descendants

  3. EAV (sparse attributes):
  products(id, name, base_price)
  product_attributes(product_id, attr_name, attr_value TEXT)
  -- All attributes for product 42:
  SELECT attr_name, attr_value FROM product_attributes WHERE product_id = 42;
  -- "Color = Red" filter (requires pivot or application processing):
  SELECT DISTINCT product_id FROM product_attributes
  WHERE attr_name = 'color' AND attr_value = 'red';

  4. JSONB Hybrid (PostgreSQL):
  products(id, name, price NUMERIC, category_id INT, attributes JSONB)
  -- Query structured + JSONB:
  SELECT name FROM products
  WHERE category_id = 5 AND attributes @> '{"color": "red", "size": "M"}';
  -- GIN index on JSONB for containment queries:
  CREATE INDEX ON products USING GIN (attributes jsonb_path_ops);

  5. Cassandra wide-column (time-series access pattern):
  CREATE TABLE events (
    device_id  UUID,
    ts         TIMESTAMP,
    event_type TEXT,
    payload    MAP<TEXT, TEXT>,
    PRIMARY KEY ((device_id), ts)   -- partition by device, cluster by time
  ) WITH CLUSTERING ORDER BY (ts DESC);
  -- Efficient range query:
  SELECT * FROM events WHERE device_id = ? AND ts >= ? AND ts <= ?;
          

Pros and cons

Implementation notes

For hierarchical data in PostgreSQL, prefer Adjacency List with recursive CTEs as the default — it's simple, easy to update (reparenting is a single UPDATE), and PostgreSQL's recursive CTE performance is acceptable for trees up to a few thousand nodes. Switch to Nested Set only when you have a proven performance bottleneck on subtree reads and updates are infrequent. A practical middle ground is ltree extension (PostgreSQL) — stores materialized path as a text label tree (e.g., 1.4.7.12), enabling both subtree queries (ancestor-match via @> operator) and efficient updates (only the moved node's path changes, not the entire tree).

For the polymorphic entity problem (multiple entity types in one table, e.g., products with different attribute sets per category), evaluate three approaches: Single Table Inheritance (STI) — one table with a type discriminator column and many nullable columns for type-specific attributes (simple queries, wasteful columns); Class Table Inheritance (CTI) — base entity table plus type-specific child tables joined by FK (normalized, complex joins); JSONB hybrid — base columns for shared attributes, JSONB for type-specific fields (flexible, no joins, GIN-indexed). CTI is typically cleanest when type-specific attributes are well-known; JSONB is better when attributes are user-configurable or highly variable.

Common failure modes

• EAV for known attributes: A team uses EAV for a product catalog with a defined attribute taxonomy; 40 attributes per product require 40 rows in the attribute table per product; queries that filter on 3 attributes need 3 self-joins; performance degrades to seconds per query on 10M products.
• Adjacency list unbounded recursion: A tree with cycles (a → b → a due to a bug) causes a recursive CTE to loop infinitely; PostgreSQL's default recursion limit prevents infinite loops but the query errors rather than returning results.
• Cassandra partition size explosion: An IoT events table partitioned by device_id with no time-bucketing; a high-frequency device generates millions of rows per partition; Cassandra's compaction and read performance degrade for partitions exceeding a few hundred MB.
• JSONB without GIN index: A team stores filter attributes in JSONB and queries with attributes @> '{"color": "red"}'; without a GIN index, this performs a full sequential scan; the query goes from <10ms to 30 seconds on 1M rows.
• Deep document embedding: An order document embeds the customer profile, shipping address, and all line items with product details; when a product description changes, all order documents containing that product must be updated — creating a fan-out update problem across millions of orders.

Decision checklist

• For hierarchical data: is the tree read-heavy (Nested Set) or write-heavy (Adjacency List)? Is tree depth bounded? Is ltree a simpler middle ground?
• For variable attributes: are the attribute names fixed and well-known (typed columns or CTI), user-configurable (JSONB hybrid), or truly sparse with hundreds of possible types (EAV)?
• For document databases: are related entities read together as a unit (embed) or accessed independently and updated separately (reference with ID)?
• For wide-column (Cassandra): is the primary access pattern known and fixed? Are all future query patterns served by the partition + clustering key combination?
• Are all JSONB containment queries backed by a GIN index (using jsonb_path_ops for containment-only queries, or generic_jsonb_ops for key existence)?
• For Cassandra time-series: is the partition key time-bucketed (e.g., by month) to prevent unbounded partition growth for high-frequency data sources?

Example use cases

• E-commerce product catalog (JSONB hybrid): Products have 10 fixed attributes (name, price, SKU, category, weight) stored as typed PostgreSQL columns with standard B-tree indexes. Category-specific attributes (clothing: size, color, material; electronics: voltage, wattage, connector) are stored in a JSONB column with a GIN index. Faceted search filters on both fixed and JSONB attributes in a single query: WHERE category_id = 12 AND attributes @> '{"size": "M", "color": "blue"}'.
• Organizational hierarchy (Adjacency List + ltree): An HR system stores 50,000 employees with manager relationships as an adjacency list. The ltree path (1.234.5678.90123) is materialized on write. Org chart reads use ltree path prefix matching for subtree queries. Reparenting (manager change) updates only the moved employee's ltree path — not the entire subtree. Subtree queries run in <5ms vs. 300ms for recursive CTE on the same data.
• IoT telemetry (Cassandra wide-column): 50,000 sensors each emit readings every 10 seconds. Partition key: (sensor_id, month) — bucketing by month prevents unbounded partition growth. Clustering key: timestamp DESC — most recent readings are at the top of the partition for fast "last N readings" queries. Daily retention policy: time-based compaction discards partitions older than 90 days with no row-level deletes needed.

Related patterns

• Normalization vs Denormalization — Data modeling patterns operate within the normalization/denormalization tradeoff space.
• Polyglot Persistence — Different modeling patterns often lead to different database technology choices per domain.
• Database Indexing — Each modeling pattern has specific indexing requirements: GIN for JSONB, ltree GiST for path queries, composite indexes for Cassandra-like access patterns.

Further reading

• PostgreSQL ltree Extension Documentation
• Apache Cassandra Data Modeling Guide
• MongoDB Data Model Design — Embedded vs References