Skip to main content

Advanced Relational Design Patterns

A deep dive into relational database design patterns beyond basic normalization.
This book focuses on OLTP (Online Transaction Processing) systems, leaving OLAP (data warehousing) for another time.

Table of Contents​

  1. Fundamentals

  2. When Not to Build It Yourself

  3. Well-Known Patterns

  4. Lesser-Known / Advanced Patterns

  5. Appendices

Fundamentals​

Bridge Tables​

Purpose: Implement many-to-many relationships between entities.
TODO: Add code example + when to include additional attributes.

Surrogate vs Natural Keys​

Purpose: Trade-offs between business-defined keys and generated keys.
TODO: Add pros/cons table + sample schemas.

Foreign Key Constraints​

Purpose: Enforce referential integrity.
TODO: Add cascading vs restricted delete/update scenarios.

Normalization & Denormalization​

Purpose: Ensure data integrity while balancing performance.
TODO: Show normalized vs denormalized schema comparisons.

ON DELETE / ON UPDATE Behaviors​

Purpose: Define behavior when referenced rows are updated/deleted.
TODO: Cover CASCADE, SET NULL, RESTRICT.

When Not to Build It Yourself​

TOAST (Postgres Overflow Storage)​

Purpose: Automatically handles oversized column values (e.g., large text or JSON) without manual overflow tables.
TODO: Show how this works behind the scenes.

Clustered & Index-Organized Tables​

Purpose: Store rows in index order for faster lookups, removing need for custom ordered child tables.
TODO: Compare SQL Server clustered indexes vs Oracle index-organized tables.

MVCC Row Versioning​

Purpose: Allows concurrent reads/writes via row versioning, often replacing DIY version tables for transactional consistency.
TODO: Explain row versions and vacuuming.

Materialized Views​

Purpose: Pre-computed join or aggregation results handled natively.
TODO: Contrast with DIY summary tables.

Partial & Expression Indexes​

Purpose: Optimize queries on data subsets or derived values without separate filtered tables.
TODO: Show index syntax and performance impact.

Computed Columns​

Purpose: Derived columns calculated by the DB engine, avoiding stored procedures for periodic sync of summary values.
TODO: Example persisted vs non-persisted computed columns.

Automatic Partitioning & Sharding​

Purpose: Distribute data transparently instead of building custom sharding logic.
TODO: Discuss native implementations (e.g., Postgres declarative partitioning).

Full-text Search​

Purpose: Native search capabilities (Postgres tsvector, SQL Server Full-Text).
TODO: Avoid building custom search indexing tables.

Row-Level Security (RLS)​

Purpose: Apply row-specific access rules at the database level.
TODO: Example: Postgres RLS policies vs manual filtering logic.

Well-Known Patterns​

EAV (Entity-Attribute-Value)​

Purpose: Flexible schema for variable attributes.
TODO: Example schema + pros/cons.

RBAC & ABAC​

Purpose: Role-based and attribute-based access control.
TODO: Example schemas for each.

Object-Level Access Control (OLAC)​

Purpose: Control access at a per-object or per-record level.
Challenges:

  • Permutational explosion: users × entities × records × CRUD permissions can grow quickly.
  • Performance: index-based lookups can still be efficient (O(log n)), but data volume and write churn must be considered.

Patterns:

  • Ownership Column: one owner per row, simple filter.
  • Access Control List (ACL) Table: multiple users per object with explicit permissions.
  • Group-Based ACL: assign permissions to groups, then map users to groups.
  • Hybrid (RBAC + ACL): role defaults + explicit overrides.
  • Inherited Permissions: derive from parent objects to avoid duplicate rows.

DB Built-in Alternative: Row-Level Security (see When Not to Build It Yourself).

TODO: Add schema examples for each approach + indexing recommendations.

Soft Deletes​

Purpose: Logical deletion without physical row removal.
TODO: Add pattern variants.

Audit Logging​

Purpose: Track changes to rows over time.
TODO: Example with triggers vs application logic.

Multi-Tenancy​

Purpose: Separate or shared schemas for multiple customers.
TODO: Compare design trade-offs.

Recursive Trees​

Purpose: Represent hierarchical data.
TODO: Compare adjacency list, nested sets, closure table.

Lesser-Known / Advanced Patterns​

Self-Referencing Bridge Table​

Purpose: Represent relationships between rows of the same entity (e.g., user friendships).
TODO: Example schema + handling directionality.

Polymorphic Associations Without Explicit FK Constraints​

Purpose: Link a single table to multiple entity types without strict referential integrity.
TODO: Discuss safety trade-offs and indexing.

Dynamic Attribute Modeling​

Purpose: Mix structured columns and flexible JSON attributes.
TODO: Show hybrid schema example.

Conditional Relationships​

Purpose: Model mutually exclusive relationships (e.g., A belongs to B or C, not both).
TODO: Implementation strategies.

Versioned Data (SCD Type 2)​

Purpose: Keep historical row versions while allowing current active state.
TODO: Example schema + query patterns.

Split Relations​

Purpose: Break wide tables into optional 1:0-1 relationships.
TODO: Performance trade-offs.

Type Tables & Inheritance Modeling​

Purpose: Represent class hierarchies in relational models.
TODO: Compare single-table vs class-table inheritance.

Appendices​

SQL DDL Examples​

TODO: Provide SQL for every pattern.

Performance & Indexing Notes​

TODO: Summarize indexing strategies.

Migration Strategies​

TODO: Discuss refactoring schemas without downtime.

Testing Relational Logic​

TODO: Strategies for testing constraints and relational integrity.

Future Exploration​

This book focuses on schema patterns and built-in features.
A future volume or video series will explore OLAC implementations in depth, including:

  • Row-Level Security vs manual ACL approaches
  • Permission caching strategies
  • Performance benchmarks on large-scale datasets

Comments

No comments yet. Be the first!