How do you approach designing a schema for a new feature from scratch?
Quick Answer
Start from the actual entities and relationships in the business domain (not from what tables you imagine you'll need), model cardinality and constraints explicitly, normalize to a sensible baseline (usually 3NF) before considering any denormalization, and validate the design against the concrete queries the feature will actually need to run — a schema that looks clean on paper but can't efficiently answer the real query patterns isn't done yet.
Detailed Answer
A concrete process to describe
1. Understand the domain and real requirements first. What entities exist, what are their genuine relationships (one-to-many? many-to-many?), and — critically — what are the actual business rules and invariants that must hold (can an order exist without a customer? can a product belong to multiple categories?). Skipping this and jumping straight to CREATE TABLE is the most common mistake, producing a schema that technically works but doesn't reflect the domain's real rules.
2. Sketch the conceptual/ER model before the physical schema. Identify entities, attributes, and relationship cardinality (see the ER-diagram question) — this is where you decide whether something needs a junction table (many-to-many), a foreign key (one-to-many), or genuinely belongs embedded in another entity.
3. Choose keys deliberately. Surrogate vs. natural key per entity (see that question), and think about what other tables will need to reference.
4. Normalize to a sensible baseline (usually 3NF), and add constraints that encode real business rules — NOT NULL, CHECK, foreign keys with deliberate ON DELETE behavior (see that question) — rather than relying purely on application-layer validation, which every future code path would need to remember to replicate.
5. Validate against actual query patterns before calling it done. Write out (or at least sketch) the 3-5 most important queries this feature will need — the schema needs to support them reasonably efficiently, not just look structurally correct. This is the step most likely to reveal a needed index, or a case where a slight denormalization is actually justified for a genuinely hot read path.
6. Consider evolution, not just the current requirement. Is there an obvious, likely-near-term extension (e.g., "will a product need to belong to multiple categories eventually, even if not on day one?") that would be far more painful to retrofit than to accommodate now? This is a judgment call, not a mandate to over-engineer for hypothetical future needs — the goal is avoiding obviously short-sighted choices, not speculative generality.
7. Plan the migration path, even for a brand-new feature — how will this schema be deployed, and if requirements shift shortly after launch (which they often do), how painful would evolving it be?
What interviewers listen for
A methodical process that starts from the domain and validates against real queries — not a candidate who recites normal forms from memory without connecting them to an actual design decision, and not a candidate who jumps straight to schema syntax without first understanding what the data actually represents and how it'll really be used.