Schema Design

1. What Is It?

Schema design is the process of deciding how to organize data into tables, columns, and relationships to serve the queries an application actually runs. A good schema makes common queries fast and simple, keeps related data consistent, and allows the application to grow without requiring painful rewrites. A bad schema produces slow queries, duplicated data that drifts out of sync, and migrations that lock tables in production.

The core challenge is that schema decisions made early are expensive to change later. Adding a column is cheap; splitting a table that millions of rows inhabit is not.

2. How It Works

Schema design follows a progression from real-world entities to a working relational structure.

Step 1: Identify entities and their attributes

Start with the nouns in your domain. For a SaaS application: users, organizations, projects, tasks. Each entity becomes a table; its properties become columns.

Step 2: Identify relationships

• One-to-many: one organization has many users → FK on the "many" side (users.org_id)
• Many-to-many: a task can have many tags and a tag can apply to many tasks → junction table task_tags(task_id, tag_id)
• One-to-one: a user has one user_profile → FK with a UNIQUE constraint on either side, or merge into one table

Step 3: Normalize to remove duplication

Normalization is the process of structuring tables so each fact is stored once. The three most practical normal forms for SDEs:

• 1NF — each column holds a single, atomic value. No comma-separated lists in a column.
• 2NF — every non-key column depends on the whole primary key, not just part of it. (Matters for composite PKs.)
• 3NF — every non-key column depends directly on the PK, not on another non-key column. If orders.customer_email and orders.customer_name both live on the orders table, customer_name depends on customer_email, not on order_id — a 3NF violation. Move the customer data to a users table.

Step 4: Denormalize deliberately for read performance

Pure normalization optimizes for write correctness. Once you know your query patterns, you sometimes store computed or redundant data to avoid expensive joins:

• tasks.assignee_name cached alongside assignee_id to avoid joining users on every task list fetch
• A projects.task_count counter column updated by triggers or application logic

Denormalization introduces the risk of data drift — the cache getting out of sync with the source. Only denormalize when profiling shows a real bottleneck.

3. What SDEs Actually Need to Know

Column type choices matter more than they appear:

• Use TEXT over VARCHAR(n) in PostgreSQL — PostgreSQL stores them identically; the length constraint in VARCHAR rarely prevents bugs and creates annoying migration work when the limit turns out to be wrong.
• Use TIMESTAMPTZ (timestamp with time zone) over TIMESTAMP — TIMESTAMPTZ stores values in UTC and converts on read; TIMESTAMP stores whatever you give it with no zone tracking, which causes bugs when servers are in different time zones.
• Use BIGINT for IDs if you expect more than 2 billion rows. INT (32-bit) overflows at ~2.1 billion, which is reachable in high-write-volume tables.

Soft deletes vs. hard deletes: Many applications add a deleted_at TIMESTAMPTZ column and filter WHERE deleted_at IS NULL instead of physically deleting rows. This preserves audit history and allows recovery, but every query must include the filter or risk reading "deleted" data. If you use soft deletes, support the filter with a partial index and consider wrapping access in a view.

Audit columns: Almost every application-level table benefits from created_at and updated_at timestamps. Add created_by and updated_by FKs to users when you need user-level audit trails. Set these automatically in triggers or ORM hooks so they're never missing.

Avoid storing arrays as comma-separated strings. "tag1,tag2,tag3" in a TEXT column is a 1NF violation. It makes querying individual tags require string parsing, prevents indexing, and breaks when values contain commas. Use a junction table or the database's native array/JSON type if the relationship is truly unstructured.

Don't use EAV (Entity-Attribute-Value) unless you have no other choice. EAV stores rows like (entity_id, attribute_name, attribute_value) to simulate dynamic columns. Queries become multi-level self-joins, type safety disappears, and indexes become nearly useless. JSON columns are a better option for truly dynamic attributes.

4. Tradeoffs & Decisions

Normalization vs. query simplicity

Fully normalized schemas require many JOINs for common queries. A tasks list that shows assignee_name, project_name, and tag_names might need 4–5 joins. This is fine when indexes are good, but becomes complex and slow at scale.

Typical evolution:

1. Start normalized — correct data, simple writes
2. Add indexes — make joins fast
3. Denormalize specific hot paths once profiling identifies them

Wide tables vs. related tables

It's tempting to keep adding columns to a table to avoid joins. A users table with 60 columns is a sign of this. Problems:

• Columns that are NULL for most rows waste storage and signal a modeling problem
• Unrelated features become entangled in the same row locks on writes
• Migrations become scarier as the table grows

If a subset of columns applies only to some users (e.g., only vendor-role users have payment_account_id), split into a user_vendor_profiles table with a one-to-one relationship.

When to use JSON/JSONB columns

Use JSON columns for:

• Attributes that are genuinely dynamic per-row (configuration blobs, event metadata, external API payloads)
• Third-party data you don't control the shape of

Don't use JSON to avoid schema design. If you find yourself querying metadata->>'email' everywhere, that field should be a first-class column.

5. Interview Cheat Sheet

Key sentences:

• "I start with entities and relationships, normalize to 3NF to eliminate data duplication, then denormalize only where profiling shows a real read performance problem."
• "Schema decisions are expensive to reverse in production — I try to get column types right upfront (BIGINT for IDs, TIMESTAMPTZ, TEXT over VARCHAR)."
• "Many-to-many relationships need a junction table with a composite PK or a surrogate PK plus a unique constraint on (a_id, b_id)."
• "I use TEXT instead of VARCHAR(n) in PostgreSQL because the length limit rarely prevents bugs and creates painful migrations."

Common follow-ups:

Q: When would you denormalize a schema? A: When profiling shows that a join is causing a measurable bottleneck in a frequently-run query, and the write overhead of keeping the denormalized copy in sync is acceptable. Always measure first — most join performance issues are solved by indexes, not denormalization.

Q: What is a junction table and when do you use one? A: A junction table (also called an association or bridge table) resolves a many-to-many relationship. It contains two foreign key columns pointing to the tables it connects. You use it any time an entity on each side can relate to multiple entities on the other side — e.g., a post can have many tags and a tag can belong to many posts.

Q: How do you handle optional one-to-one relationships? A: Two options: add nullable FK columns to the main table (simple, but pollutes the table with NULLs), or create a separate related table with a FK back to the primary table (cleaner, better when the optional data has many of its own columns). I prefer the separate table when the optional data is substantial.