Database Designer - POWERFUL Tier Skill

Overview

Core Competencies

Schema Design & Analysis

• Normalization Analysis: Automated detection of normalization levels (1NF through BCNF)
• Denormalization Strategy: Smart recommendations for performance optimization
• Data Type Optimization: Identification of inappropriate types and size issues
• Constraint Analysis: Missing foreign keys, unique constraints, and null checks
• Naming Convention Validation: Consistent table and column naming patterns
• ERD Generation: Automatic Mermaid diagram creation from DDL

Index Optimization

• Index Gap Analysis: Identification of missing indexes on foreign keys and query patterns
• Composite Index Strategy: Optimal column ordering for multi-column indexes
• Index Redundancy Detection: Elimination of overlapping and unused indexes
• Performance Impact Modeling: Selectivity estimation and query cost analysis
• Index Type Selection: B-tree, hash, partial, covering, and specialized indexes

Migration Management

• Zero-Downtime Migrations: Expand-contract pattern implementation
• Schema Evolution: Safe column additions, deletions, and type changes
• Data Migration Scripts: Automated data transformation and validation
• Rollback Strategy: Complete reversal capabilities with validation
• Execution Planning: Ordered migration steps with dependency resolution

Database Design Principles

Best Practices

Schema Design

1. Use meaningful names: Clear, consistent naming conventions
2. Choose appropriate data types: Right-sized columns for storage efficiency
3. Define proper constraints: Foreign keys, check constraints, unique indexes
4. Consider future growth: Plan for scale from the beginning
5. Document relationships: Clear foreign key relationships and business rules

Performance Optimization

1. Index strategically: Cover common query patterns without over-indexing
2. Monitor query performance: Regular analysis of slow queries
3. Partition large tables: Improve query performance and maintenance
4. Use appropriate isolation levels: Balance consistency with performance
5. Implement connection pooling: Efficient resource utilization

Security Considerations

1. Principle of least privilege: Grant minimal necessary permissions
2. Encrypt sensitive data: At rest and in transit
3. Audit access patterns: Monitor and log database access
4. Validate inputs: Prevent SQL injection attacks
5. Regular security updates: Keep database software current

Query Generation Patterns

SELECT with JOINs

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Common Table Expressions (CTEs)

-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

Window Functions

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Aggregation Patterns

-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;

-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

Migration Patterns

Up/Down Migration Scripts

migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql

Zero-Downtime Migrations (Expand/Contract)

1. Expand — add the new column/table (nullable, with default)
2. Migrate data — backfill in batches; dual-write from application
3. Transition — application reads from new column; stop writing to old
4. Contract — drop old column in a follow-up migration

Data Backfill Strategies

-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected

Rollback Procedures

• Always test the

  down.sql

  in staging before deploying

  up.sql

  to production
• Keep rollback window short — if the contract step has run, rollback requires a new forward migration
• For irreversible changes (dropping columns with data), take a logical backup first

Performance Optimization

Indexing Strategies

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));

CREATE INDEX idx_locations ON places USING gist(coords);

CREATE INDEX idx_active ON users(email) WHERE active = true;

CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);

EXPLAIN Plan Reading

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

• Seq Scan on large tables — missing index
• Nested Loop with high row estimates — consider hash/merge join or add index
• Buffers shared read much higher than hit — working set exceeds memory

N+1 Query Detection

• Use

  JOIN

  or subquery to fetch in one round-trip
• ORM eager loading (

  select_related

  /

  includes

  /

  with

  )
• DataLoader pattern for GraphQL resolvers

Connection Pooling

(2 * CPU cores) + disk spindles

2 * vCPUs

Read Replicas and Query Routing

• Route all

  SELECT

  queries to replicas; writes to primary
• Account for replication lag (typically <1s for async, 0 for sync)
• Use

  pg_last_wal_replay_lsn()

  to detect lag before reading critical data

Multi-Database Decision Matrix

• PostgreSQL — default choice for new projects; best extensibility and standards compliance
• MySQL — existing MySQL ecosystem; simple read-heavy web applications
• SQLite — mobile apps, CLI tools, unit test databases, IoT/edge
• SQL Server — mandated by enterprise policy; deep .NET/Azure integration

NoSQL Considerations

Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.

Sharding & Replication

Horizontal vs Vertical Partitioning

• Vertical partitioning: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.
• Horizontal partitioning (sharding): Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.

Sharding Strategies

shard = hash(key) % N

Replication Patterns

Cross-References

• sql-database-assistant — query writing, optimization, and debugging for day-to-day SQL work
• database-schema-designer — ERD modeling, normalization analysis, and schema generation
• migration-architect — large-scale migration planning across database engines or major schema overhauls
• senior-backend — application-layer patterns (connection pooling, ORM best practices)
• senior-devops — infrastructure provisioning for database clusters and replicas

Conclusion