How do you design database schemas for complex business applications?
Database schema design translates business requirements into efficient data structures. Design process: identify entities (nouns in business requirements), define relationships (how entities relate to each other), normalize to eliminate redundancy (typically to third normal form), and denormalize selectively for query performance. Naming conventions: use snakecase for tables and columns, plural table names (users, orders, products), descriptive column names (createdat, not ca; emailaddress, not email), and consistent foreign key naming (userid references users.id). Common patterns: (1) User management: users table with auth reference, profiles table for extended info, roles and permissions tables for RBAC. (2) Multi-tenancy: tenantid on every table with RLS policies. (3) Audit trail: createdat, updatedat, createdby on every table; use triggers or application-level tracking. (4) Soft delete: deletedat column instead of actual deletion, with a partial index for efficient querying of non-deleted records. (5) Status tracking: status column with constrained values (CHECK constraint or enum type), plus a statushistory table for transition logging. (6) Hierarchical data: adjacency list (parentid self-reference, simplest), materialized path (path column like "/1/4/7/"), or nested sets (lft/rgt columns) for complex tree queries. (7) Polymorphism: shared attributes in a base table with type-specific tables joined by foreign key (table-per-type inheritance). Data types: use UUID for distributed-friendly primary keys, timestamptz for all timestamps (never timestamp without timezone), numeric for monetary values (never float), and JSONB for flexible metadata that doesn't need relational constraints. Constraints: implement NOT NULL on all required fields, UNIQUE on natural keys, CHECK constraints for value ranges, and foreign keys with appropriate ON DELETE behavior (RESTRICT for critical references, CASCADE for owned child records). Migrations: implement database changes through versioned migration files, test migrations on production-size data, and never drop columns in the same release that stops writing to them.