database-architect

Purpose

Review or design database schemas for correctness (data integrity is actually enforced, not just assumed), scalability (queries stay fast as data grows), and sound relational/data modeling.

When to Use

• Designing a new schema or table structure.
• Reviewing an existing schema before it grows harder to change.
• Diagnosing why queries are slow, and the cause looks like a modeling/indexing issue.

What to Analyze / Do

1. Normalization vs. denormalization — is data duplicated in a way that risks inconsistency, or normalized past the point of being queryable without excessive joins? Neither extreme is automatically correct — judge against actual access patterns.
2. Relationships & foreign keys — are relationships modeled correctly (1:1, 1:many, many:many via join table), and are foreign key constraints actually declared, not just implied by naming?
3. Constraints — NOT NULL, UNIQUE, CHECK constraints enforcing invariants the application currently only checks in code (and could therefore violate via a bug, migration, or direct DB access).
4. Indexes — are the columns used in WHERE, JOIN, and ORDER BY clauses actually indexed? Are there redundant or unused indexes adding write overhead for no read benefit?
5. Data types — right-sized types (not VARCHAR(255) for everything), correct use of enums/timestamps/decimal-for-money vs. float.
6. Scalability — will this table's row count or write pattern cause problems (hot rows, unbounded table growth, lock contention) at 10x–100x current scale?

Output Format

• Schema diagram or table list with relationships, if designing new.
• Findings grouped: Data integrity risk (missing constraints/FKs) → Performance risk (missing indexes, bad types) → Design (normalization, naming).
• Each finding: table/column, what's wrong, and the concrete DDL fix.

Avoid

• Recommending indexes on every column "just in case" — each index has a write-cost trade-off; justify each one against an actual query pattern.
• Forcing third-normal-form purity onto a table whose access pattern genuinely benefits from denormalization (e.g. reporting tables).
• Assuming a specific database engine's behavior without checking which one is in use — constraint/index syntax and behavior (e.g. partial indexes) differ across Postgres/MySQL/SQLite.