← Back to all products

Data Modeling Patterns

$29

Relational and NoSQL modeling patterns, normalization guides, temporal data handling, and schema versioning.

📁 18 files
MarkdownSQLPostgreSQL

📁 File Structure 18 files

data-modeling-patterns/ ├── LICENSE ├── README.md ├── docs/ │ ├── anti-patterns.md │ ├── indexing-strategy.md │ ├── normalization-vs-denormalization.md │ ├── partitioning-guide.md │ ├── scd-types.md │ └── star-vs-snowflake.md ├── examples/ │ ├── ecommerce-er-diagram.md │ └── ecommerce-model.sql └── sql/ ├── audit-columns.sql ├── normalization-examples.sql ├── partitioning.sql ├── scd-type1-3.sql ├── scd-type2.sql ├── snowflake-schema.sql ├── soft-delete.sql └── star-schema.sql

📖 Documentation Preview README excerpt

Data Modeling Patterns

Runnable, heavily-commented SQL patterns and decision guides for designing

relational schemas that stay correct and fast as they grow. This is not theory —

every pattern is a working .sql file you can run top-to-bottom on PostgreSQL,

paired with a guide that explains when to use it and why.

Built for backend engineers, data engineers, and DBAs who design schemas and want

defensible answers to "normalize or not?", "which SCD type?", "should I

partition?", and "why is this an anti-pattern?".

What's inside

  • 9 runnable SQL files — dimensional models, all the SCD types, audit columns,

soft delete, partitioning, a full normalization walk-through, and a complete

worked e-commerce schema.

  • 6 decision guides — each with quick-reference tables, an explicit decision

tree, and an FAQ, so you can choose correctly under time pressure.

  • A worked example — one coherent e-commerce OLTP schema with an

entity-relationship diagram and design rationale that ties every pattern together.

All SQL is PostgreSQL syntax with notes where other engines differ. No external

dependencies — you need only a database to run it against (or just read it).

Quick start

1. Unzip the product.

2. Skim this README, then open [examples/ecommerce-er-diagram.md](examples/ecommerce-er-diagram.md)

for the big picture.

3. Run a pattern against a scratch database, e.g.:


   psql "postgres://localhost/scratch" -f sql/normalization-examples.sql
   psql "postgres://localhost/scratch" -f sql/scd-type2.sql
   psql "postgres://localhost/scratch" -f examples/ecommerce-model.sql

Each file is self-contained: it creates its own schema, seeds demo data, and

ends with verification queries (commented) you can run to see the result.

4. Read the matching guide in docs/ for the why and the decision rules.

The SQL files create schemas named dw, app, norm, and shop. Run them on
a throwaway database so they never touch anything real.

File-by-file guide

`sql/` — runnable patterns

| File | Pattern | What it teaches |

|------|---------|-----------------|

| [star-schema.sql](sql/star-schema.sql) | Star dimensional model | Fact + denormalized dimensions, grain, surrogate keys, a date dimension |

| [snowflake-schema.sql](sql/snowflake-schema.sql) | Snowflake dimensional model | Normalized dimension hierarchies and when they earn their keep |

| [scd-type2.sql](sql/scd-type2.sql) | Slowly Changing Dimension, Type 2 | Full history: validity windows, current flag, correct fact joins |

| [scd-type1-3.sql](sql/scd-type1-3.sql) | SCD Types 1 & 3 | Overwrite vs keep-previous-value, chosen per column |

| [audit-columns.sql](sql/audit-columns.sql) | Audit columns | created/updated bookkeeping, a tamper-proof trigger, full audit log |

| [soft-delete.sql](sql/soft-delete.sql) | Soft delete | deleted_at marker, a safety-net view, partial unique indexes, purge job |

| [partitioning.sql](sql/partitioning.sql) | Table partitioning | RANGE / LIST / HASH strategies, pruning, instant retention |

| [normalization-examples.sql](sql/normalization-examples.sql) | Normalization | One messy table walked from unnormalized → 1NF → 2NF → 3NF |

`docs/` — decision guides

... continues with setup instructions, usage examples, and more.

📄 Code Sample .sql preview

examples/ecommerce-model.sql -- ============================================================================= -- Worked end-to-end model: a small e-commerce OLTP schema -- ============================================================================= -- This ties the individual patterns together into one coherent, runnable -- transactional (OLTP) schema for an online store. It demonstrates, in context: -- * 3NF entity separation (customer / product / order / order_line). -- * Surrogate primary keys + natural unique keys (sku, email). -- * Foreign keys with deliberate ON DELETE behaviour. -- * Audit columns (created_at/updated_at) on mutable entities. -- * Soft delete on customer (see sql/soft-delete.sql for the full rationale). -- * Money as integer cents (never float) and CHECK constraints for integrity. -- * A purpose-built index for the dominant access path. -- -- See examples/ecommerce-er-diagram.md for the entity-relationship picture and -- the access patterns this design is optimised for. -- -- PostgreSQL syntax. Run top-to-bottom on an empty database/schema. -- ============================================================================= CREATE SCHEMA IF NOT EXISTS shop; -- ----------------------------------------------------------------------------- -- Customer. Soft-deletable (orders must survive a customer "deletion"), with a -- partial unique index so email is unique among LIVE customers only. -- ----------------------------------------------------------------------------- CREATE TABLE shop.customer ( customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, full_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz -- NULL => live ); CREATE UNIQUE INDEX uq_customer_email_live ON shop.customer (email) WHERE deleted_at IS NULL; -- ----------------------------------------------------------------------------- -- Product. sku is the stable natural key; price stored as integer cents. -- ----------------------------------------------------------------------------- CREATE TABLE shop.product ( product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku text NOT NULL UNIQUE, product_name text NOT NULL, unit_price_cents bigint NOT NULL CHECK (unit_price_cents >= 0), is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); # ... 85 more lines ...
Buy Now — $29 Back to Products