Database Design: Normalization, Denormalization, and When to Break the Rules

Database Design Principles

Good schema design is a balance between normalization (data integrity) and denormalization (query performance).

Normalization Forms

1NF: Atomic Values

-- Bad: repeating groups
CREATE TABLE orders (
  id INT,
  items TEXT  -- 'apple,banana,cherry' — violates 1NF
);

-- Good: atomic values
CREATE TABLE order_items (
  order_id INT,
  item_name VARCHAR(100),
  quantity INT
);

3NF: No Transitive Dependencies

-- Bad: city determines zip, zip determines state (transitive)
CREATE TABLE users (
  id INT,
  name VARCHAR,
  zip_code VARCHAR(10),
  city VARCHAR(100),  -- determined by zip, not id
  state VARCHAR(2)    -- determined by zip, not id
);

-- Good: extract to zip_codes table
CREATE TABLE zip_codes (
  zip VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100),
  state VARCHAR(2)
);

Strategic Denormalization

Cached Counts

-- Instead of COUNT(*) on every page load:
ALTER TABLE posts ADD COLUMN comment_count INT DEFAULT 0;
ALTER TABLE users ADD COLUMN follower_count INT DEFAULT 0;

-- Keep in sync with triggers
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

JSONB for Flexible Attributes (EAV Alternative)

-- Bad: Entity-Attribute-Value (terrible performance)
CREATE TABLE product_attributes (
  product_id INT,
  attr_name VARCHAR,
  attr_value TEXT  -- Loses type information!
);

-- Good: JSONB column
ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}';

-- Index specific JSONB paths
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
CREATE INDEX idx_products_attrs ON products USING gin(attributes);

-- Query
SELECT * FROM products
WHERE attributes->>'color' = 'red'
  AND (attributes->>'weight')::float < 5.0;

Multi-Tenancy Patterns

Row-Level (Shared Schema)

-- Add tenant_id to all tables
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100),
  plan VARCHAR(50)
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) NOT NULL,
  email VARCHAR(255),
  UNIQUE (tenant_id, email)
);

-- RLS for automatic isolation
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Set in application
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Schema-Per-Tenant

-- Each tenant gets own PostgreSQL schema
CREATE SCHEMA tenant_abc;
CREATE TABLE tenant_abc.users (...);
CREATE TABLE tenant_abc.orders (...);

Audit Trail Pattern

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  record_id TEXT NOT NULL,
  operation CHAR(1) CHECK (operation IN ('I','U','D')),
  old_values JSONB,
  new_values JSONB,
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_changed_at ON audit_log (changed_at DESC);

-- Generic audit trigger
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $
BEGIN
  INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    LEFT(TG_OP, 1),
    CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::JSONB END,
    current_setting('app.user_id', true)::UUID
  );
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

Soft Deletes vs Hard Deletes

-- Soft delete: mark as deleted, keep data
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN GENERATED ALWAYS AS (deleted_at IS NOT NULL) STORED;

CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

-- View for non-deleted records
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;