Database Schema Design Decisions That Haunt You Forever (or Save You Forever)

Database schema decisions fall into two categories: those you can change later (add a column, rename a table) and those that become permanent architecture (primary key strategy, relationship modeling, data lifecycle patterns). The second category deserves careful thought upfront because migrating later is expensive, risky, and sometimes impossible without downtime.

Here are the schema decisions that I have seen teams struggle with after the fact — and how to get them right from the start.

Decision 1: Primary Key Strategy

The choice between auto-increment integers, UUIDs, and other ID strategies affects performance, API design, and data portability forever.

Auto-increment integers: small (4-8 bytes), fast to index, sequential (good for B-tree performance), and human-readable. But: they expose entity counts (user ID 50000 tells competitors how many users you have), they create conflicts in distributed systems (two servers both generating ID 1001), and they make data migration between systems difficult.

UUIDs (v4 random): globally unique, no coordination needed between systems, do not expose entity counts. But: large (16 bytes), random order (poor B-tree locality, slower index writes), and ugly in URLs.

ULIDs or UUIDs (v7 time-ordered): globally unique AND time-ordered. Best of both worlds for most applications. Good B-tree performance (sequential writes), no coordination needed, and encode creation time. This is what I recommend for new projects in 2026.

What I do: ULID or UUID v7 as the primary key stored as a string (in SQLite) or native UUID type (in PostgreSQL). The slight storage overhead is worth the benefits of global uniqueness and no information leakage.

Decision 2: Soft Delete vs Hard Delete

When a user "deletes" something, do you actually remove the row or set a deletedAt timestamp?

Soft delete advantages: recovery is trivial (just clear deletedAt), audit trails are preserved, foreign key constraints are not violated (related data still references a valid row), and legal/compliance requirements for data retention are met.

Soft delete disadvantages: every query needs a WHERE deletedAt IS NULL filter (easy to forget, leading to showing "deleted" data), storage grows indefinitely (rows never leave), unique constraints are complicated (a "deleted" user's email is still in the unique index), and GDPR right-to-erasure might require actual deletion anyway.

My approach: soft delete for business data (tools, posts, reviews — might need recovery). Hard delete for user data when explicitly requested (GDPR compliance). Implement soft delete at the ORM level (Prisma middleware) so queries automatically exclude deleted records.

Decision 3: JSON vs Normalized Tables

When data has variable structure (different tools have different metadata, tags can have arbitrary properties), you choose between JSON columns and normalized relational tables.

JSON columns (JSONB in PostgreSQL): flexible (any structure), no migration needed for new fields, and good for data that is always read and written together. But: no referential integrity, harder to query (JSON path syntax), no foreign keys, and harder to index specific sub-fields.

Normalized tables: strong typing, referential integrity, easy to query and filter, and standard indexing. But: schema changes require migrations, and many-to-many relationships need junction tables.

My rule: use JSON for truly unstructured or rarely-queried metadata (tool-specific settings, user preferences, display configuration). Use normalized tables for anything you will filter, sort, aggregate, or join on.

At AIDToolStack: tool tags are a normalized many-to-many relationship (we filter and display by tag). Tool metadata (color scheme, display preferences, arbitrary properties) is a JSON column (we only read it, never query by it).

Decision 4: Timestamps and Time Handling

Every table should have createdAt and updatedAt timestamps. This is non-negotiable for debugging, auditing, and data analysis.

Store timestamps in UTC. Always. No exceptions. Convert to local time in the presentation layer. Storing local time creates ambiguity (which timezone? does it observe DST?) that makes time-based queries unreliable.

Use datetime (with timezone) types in the database, not Unix timestamps. Datetime types are human-readable in database tools, support native date functions, and handle timezone conversion correctly.

Additional timestamp consideration: if your application has "published" content (blog posts, tools), add a publishedAt field separate from createdAt. Content might be created as a draft and published later. The publication date is business data; the creation date is system data.

Decision 5: Enum vs Lookup Table

For fields with a fixed set of values (status: draft/published/archived, role: user/admin/moderator), use database enums or lookup tables?

Database enums: strict validation at the database level, good documentation of allowed values, and type-safe in ORMs. But: adding a new value requires a migration (ALTER TYPE ... ADD VALUE in PostgreSQL), and removing a value is complex.

String fields with application-level validation: flexible (any value), no migration for changes, but no database-level enforcement. Typos can introduce invalid values.

Lookup tables: a separate table with an ID and name for each value. Most flexible, supports additional metadata per value (description, display order), but adds JOIN complexity for simple reads.

My approach for most cases: string field in the database with a TypeScript enum in the application code and Zod validation at API boundaries. New values can be added without database migration. The application enforces validity; the database stores whatever passes validation.

For critical fields where invalid values would be catastrophic (payment status, permission levels): use database-level constraints (CHECK constraint or enum type).

Decision 6: One-to-Many vs Many-to-Many

Getting relationship cardinality right is important because changing it later requires data migration:

Question: can a tool belong to multiple categories, or only one? If one: use a categoryId foreign key on the tool table. If multiple: use a junction table (tool_categories with toolId and categoryId).

Design for the future cardinality: if there is any chance a relationship will become many-to-many later, model it as many-to-many from the start. Adding a junction table later requires: creating the table, migrating existing foreign key data into it, updating all queries, and handling the transition period.

At AIDToolStack: tools have one primary category (foreign key) but multiple tags (many-to-many junction table). We knew from the start that tools could have multiple tags but would only have one primary category.

Decision 7: Handling Historical Data

Data changes over time: prices increase, ratings change, tools update their features. Do you need to preserve history?

If yes: create a separate history/audit table that records changes with timestamps. The main table always has the current state; the history table has every past state.

If no: just update the field in place. Simpler, but you lose the ability to answer "what was the price last month?"

Our approach: we maintain price history for tools (separate price_history table with price, currency, and recorded_at). We do not maintain history for tool descriptions or metadata (current state is sufficient).

Decision 8: Indexing Strategy

Indexes are not a schema design decision in theory — you can add them later. In practice, missing indexes on production databases with millions of rows require careful migration planning (index creation can lock tables).

Design indexes alongside the schema: for every table, immediately create indexes on: foreign keys (required for JOIN performance), fields used in WHERE clauses (category, status, isPublished), fields used in ORDER BY (createdAt, rating), and unique constraints (slug, email).

Composite indexes for common query patterns: if you always query tools WHERE categoryId = X AND isPublished = true ORDER BY rating DESC, create a composite index on (categoryId, isPublished, rating).

The Migration Reality

Schema decisions that are easy to change later: adding nullable columns, adding new tables, adding indexes, and renaming columns (with care).

Schema decisions that are painful to change: primary key type (requires rewriting every reference), relationship cardinality (requires data migration), data type of high-traffic columns (requires table rewrite), and partitioning strategy (requires complete table rebuild).

Get the painful decisions right from the start. Be relaxed about the easy decisions — you can always add a column next week.