Database Schema Design Best Practices for Startups in 2026

Why Schema Design Matters More Than You Think

You can refactor your API in a weekend. You can rewrite your frontend in a month. But migrating a database schema with millions of rows, foreign key constraints, and zero downtime? That's a multi-sprint project.

The decisions you make in week 1 — table names, column types, relationships, indexes — compound over years. A bad schema doesn't break immediately; it slowly degrades performance, makes features harder to build, and creates data integrity issues that surface months later.

1. Naming Conventions

Pick a convention and be religious about it:

• Tables: plural snake_case (users, order_items, invoice_line_items)
• Columns: snake_case (created_at, first_name, is_active)
• Foreign keys: [singular_table]_id (user_id, order_id)
• Booleans: prefix with is_ or has_ (is_verified, has_subscription)
• Timestamps: always created_at and updated_at on every table

2. Use UUIDs (Carefully)

UUIDs prevent enumeration attacks and make multi-database merges easier. But they're larger (16 bytes vs 4-8 bytes for integers) and random UUIDs fragment B-tree indexes. Use UUIDv7 (time-sortable) if your database supports it — you get the benefits of UUIDs with sequential insert performance.

3. Normalize, Then Selectively Denormalize

Start in 3rd Normal Form (3NF). Every fact stored once, every table has a single purpose. As you scale and identify read-heavy patterns, add strategic denormalization — but document why. Denormalized fields without documentation become mysterious inconsistencies later.

4. Index Strategy

Rules of thumb:

• Index every foreign key column
• Index columns used in WHERE clauses and ORDER BY
• Composite indexes for multi-column queries (order matters!)
• Don't index columns with low cardinality (boolean columns with 90/10 distribution)
• Use partial indexes for filtered queries (WHERE is_active = true)

5. Always Use Migrations

Never modify a production schema by hand. Use a migration tool (Drizzle, Prisma, Knex, or raw SQL files with a runner). Every change should be version-controlled, reversible, and deployable through CI/CD.

6. Plan for Soft Deletes

Add a deleted_at timestamp column instead of actually deleting rows. This gives you an audit trail, makes undo possible, and prevents cascade-delete disasters. Add a partial index on WHERE deleted_at IS NULL for query performance.

7. Avoid These Common Mistakes

• Storing money as floats: Use integer cents or DECIMAL(19,4). Floating point arithmetic creates rounding errors.
• Using ENUM for evolving lists: ENUMs are painful to migrate. Use a reference table or varchar with application-level validation.
• Missing timestamps: Every table should have created_at and updated_at. You'll need them for debugging, analytics, and sync.
• Overusing JSON columns: JSONB is powerful but makes querying, indexing, and validation harder. If you query a field regularly, it should be a column.
• No foreign key constraints: Constraints feel restrictive until they save you from orphaned records and impossible data states.