Data modeling best practices

ascade' }),

// Denormalized fields for access pattern: "List orders for customer dashboard" // Avoids join to users table for read-heavy operations customer_email_snapshot: varchar('customer_email_snapshot', { length: 255 }).notNull(), customer_name_snapshot: varchar('customer_name_snapshot', { length: 255 }).notNull(),

total_amount_cents: integer('total_amount_cents').notNull(), status: varchar('status', { length: 20 }).notNull(), metadata: jsonb('metadata').default({}), created_at: timestamp('created_at').defaultNow().notNull(), }, (table) => { return { // Constraint: Amount must be positive amountCheck: check('orders_amount_positive', sql${table.total_amount_cents} > 0), // Constraint: Valid status transition logic can be enforced via triggers or app logic, // but DB constraint ensures finite state set. statusCheck: check('orders_status_check', sql${table.status} IN ('pending', 'paid', 'shipped', 'cancelled')),

// Index: Composite index for "Dashboard" query pattern
// WHERE user_id = ? ORDER BY created_at DESC
userCreatedIdx: index('orders_user_created_idx').on(table.user_id, table.created_at.desc),

// Partial Index: Only index active orders for reporting
activeOrdersIdx: index('orders_active_idx')
  .on(table.created_at)
  .where(sql`${table.status} != 'cancelled'`),

}; });

// Audit Log: Append-only model for compliance export const auditLogs = pgTable('audit_logs', { id: uuid('id').defaultRandom().primaryKey(), entity_type: varchar('entity_type', { length: 50 }).notNull(), entity_id: uuid('entity_id').notNull(), action: varchar('action', { length: 50 }).notNull(), payload: jsonb('payload').notNull(), performed_by: uuid('performed_by').references(() => users.id), created_at: timestamp('created_at').defaultNow().notNull(), }, (table) => { return { // Index: Optimizes "Get audit trail for entity" entityTrailIdx: index('audit_logs_entity_idx').on(table.entity_type, table.entity_id, table.created_at), }; });

**Architecture Rationale:**
*   **`users` table:** Strict normalization. Email is unique and indexed. Status is constrained.
*   **`orders` table:** Hybrid approach. `user_id` maintains referential integrity. `customer_email_snapshot` and `customer_name_snapshot` are denormalized to satisfy the high-frequency dashboard query without a join. Constraints enforce business rules. Composite index supports the dashboard query pattern. Partial index optimizes reporting.
*   **`audit_logs` table:** Append-only design. No updates or deletes. Indexed for retrieval by entity trail.

---

### Pitfall Guide

#### 1. Entity-First Modeling
**Mistake:** Designing tables based on domain nouns (User, Product, Order) without analyzing how data is accessed.
**Impact:** Results in excessive joins, N+1 query problems, and inability to scale read workloads.
**Best Practice:** Start with a "Query Matrix." List every critical query and design tables/indexes to satisfy them. Denormalize only where access patterns demand it.

#### 2. Weak Constraint Enforcement
**Mistake:** Relying on application code for validation (e.g., checking email format in Node.js) and omitting database constraints.
**Impact:** Data corruption when multiple services write to the DB, batch jobs bypass validation, or bugs in application logic.
**Best Practice:** Enforce `NOT NULL`, `UNIQUE`, `CHECK`, and foreign key constraints at the database layer. The DB is the source of truth; constraints are the final gatekeeper.

#### 3. Ignoring Index Fragmentation and Selectivity
**Mistake:** Adding indexes blindly or using random UUIDs as primary keys on high-write tables without considering B-Tree fragmentation.
**Impact:** Write performance degradation due to page splits; indexes that are never used by the query planner.
**Best Practice:** Analyze index usage stats. Use sequential UUIDs or ULIDs for high-write tables to reduce fragmentation. Drop unused indexes. Ensure composite indexes follow the "most selective first" rule unless query patterns dictate otherwise.

#### 4. The JSON Trap
**Mistake:** Storing structured data in `JSONB` columns and querying it without generated columns or functional indexes.
**Impact:** Full table scans on JSON fields; loss of type safety; inability to enforce structure.
**Best Practice:** If you query JSON fields, create generated columns for those fields and index them. Use JSON only for truly dynamic, unstructured payloads that are rarely queried by internal fields.

#### 5. Migration Anxiety and "Soft" Schema
**Mistake:** Avoiding schema changes due to fear of downtime, leading to nullable columns, deprecated fields, and "soft" deletions without cleanup.
**Impact:** Schema bloat, confusion for developers, increased storage costs, and query complexity.
**Best Practice:** Adopt the "Expand/Contract" pattern. Add new columns as nullable, deploy app code to write to both, backfill data, then switch reads. Use tools that support online schema changes. Regularly audit and remove deprecated columns.

#### 6. Hardcoding Cardinality Assumptions
**Mistake:** Designing models assuming 1:1 relationships that evolve into 1:N or M:N as business requirements change.
**Impact:** Schema refactoring becomes necessary; data migration scripts are risky and complex.
**Best Practice:** Design for flexibility where cardinality is uncertain. Use junction tables for relationships that might become many-to-many. Avoid storing arrays of IDs in a column; use proper relational structures.

#### 7. Neglecting Data Lifecycle and Retention
**Mistake:** Modeling data as static entities without considering expiration, archival, or compliance requirements.
**Impact:** Tables grow indefinitely; query performance degrades; compliance violations (GDPR/CCPA) due to inability to purge data.
**Best Practice:** Implement partitioning for time-series data. Define retention policies. Use soft deletes with `deleted_at` timestamps for auditability, but ensure archival processes exist. Design models to support "Right to be Forgotten" operations.

---

### Production Bundle

#### Action Checklist
- [ ] **Audit Access Patterns:** Document top 10 read/write queries and verify schema supports them efficiently.
- [ ] **Enforce Constraints:** Ensure all tables have PK, FK, NOT NULL, and CHECK constraints where applicable.
- [ ] **Review Indexes:** Validate composite indexes align with query WHERE/ORDER BY clauses; remove unused indexes.
- [ ] **Plan Migrations:** Implement expand/contract strategy for all schema changes; test on production-scale data.
- [ ] **Document Model:** Maintain auto-generated ER diagrams and data dictionaries; update with every migration.
- [ ] **Load Test Schema:** Run queries against data volumes matching production to verify performance.
- [ ] **Secure PII:** Ensure sensitive columns are encrypted or tokenized; review access controls.
- [ ] **Define Retention:** Implement partitioning or archival strategies for high-volume tables.

#### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| **High Write, Low Read** (e.g., IoT telemetry) | Append-only tables, partitioning by time, minimal indexes | Maximizes write throughput; partitioning aids retention | Higher storage cost; lower compute cost |
| **Complex Ad-hoc Queries** (e.g., Analytics dashboard) | Star schema or Columnar store (e.g., ClickHouse/BigQuery) | Optimized for aggregation; separates OLAP from OLTP | Higher infra cost; requires ETL pipeline |
| **Rapid Iteration / Unstructured Data** (e.g., Feature flags) | Document store or JSONB with generated columns | Schema flexibility; fast development | Query limitations; potential integrity risks |
| **Strict Compliance / Audit** (e.g., Financial ledger) | Immutable append-only logs, cryptographic hashing, strict FKs | Auditability; tamper-evidence; data integrity | Higher storage; complex query patterns |
| **High Read, Low Write** (e.g., Product catalog) | Heavy denormalization, read replicas, caching | Minimizes latency; reduces DB load | Write complexity; synchronization overhead |

#### Configuration Template

**Drizzle Schema Configuration (`schema.ts`)**
Copy this template to enforce best practices across your project.

```typescript
import { pgTable, uuid, varchar, timestamp, integer, boolean, index, check } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

// Base table configuration for consistency
export const baseColumns = {
  id: uuid('id').defaultRandom().primaryKey(),
  created_at: timestamp('created_at').defaultNow().notNull(),
  updated_at: timestamp('updated_at').defaultNow().notNull(),
};

// Example: Products table with best practices
export const products = pgTable('products', {
  ...baseColumns,
  sku: varchar('sku', { length: 50 }).notNull(),
  name: varchar('name', { length: 255 }).notNull(),
  price_cents: integer('price_cents').notNull(),
  is_active: boolean('is_active').default(true).notNull(),
  category_id: uuid('category_id').notNull(),
}, (table) => {
  return {
    // Business Rules as Constraints
    priceCheck: check('products_price_positive', sql`${table.price_cents} >= 0`),
    skuUnique: index('products_sku_unique').on(table.sku).unique(),
    
    // Access Pattern: Filter by category and active status
    categoryActiveIdx: index('products_category_active_idx')
      .on(table.category_id, table.is_active),
  };
});

// Migration generation script (drizzle.config.ts)
export default {
  schema: "./schema.ts",
  out: "./migrations",
  dialect: "postgresql",
};

Quick Start Guide

1. Initialize Project:

   npm install drizzle-orm drizzle-kit
   npx drizzle-kit init
   

2. Define Schema: Create schema.ts using the template. Define tables with constraints and indexes.
3. Generate Migration:

   npx drizzle-kit generate --name init_schema
   

4. Apply Migration:

   npx drizzle-kit migrate
   

5. Verify: Connect to your database and run \d+ table_name to confirm constraints and indexes are applied. Run a sample query with EXPLAIN ANALYZE to verify index usage.

Conclusion Data modeling is not a one-time design phase; it is a continuous discipline aligned with evolving access patterns and business requirements. By prioritizing access-pattern-driven design, enforcing constraints at the database layer, and managing schema evolution rigorously, engineering teams can build systems that are performant, maintainable, and resilient. The cost of poor modeling compounds over time; the investment in best practices pays dividends in reduced latency, lower technical debt, and accelerated development velocity.