database-patterns

# Database Patterns ## Overview Database design and access patterns for relational and NoSQL databases. ## Schema Design ### Normalization Levels | Level | Description | Use Case | |-------|-------------|----------| | 1NF | Atomic values, no repeating groups | Base requirement | | 2NF | No partial dependencies | Most applications | | 3NF | No transitive dependencies | OLTP systems | | Denormalized | Redundant data for reads | Read-heavy, analytics | ### Common Table Patterns ```sql -- Users table CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, status VARCHAR(20) DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Soft delete pattern ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL; CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL; -- Audit columns ALTER TABLE users ADD COLUMN created_by UUID REFERENCES users(id); ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id); ``` ### Relationships ```sql -- One-to-Many CREATE TABLE orders ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id), total DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_orders_user ON orders(user_id); -- Many-to-Many CREATE TABLE order_products ( order_id UUID REFERE...