Top 4 Schema design Interview Questions
Browse 4 real-world technical and behavioral interview questions about Schema design. Review scenarios, edge cases, and architectural best practices.
How do you design the tools an agent calls?
Give each tool one narrow purpose with a self-describing schema, return failures as structured results the model can act on rather than raising, take an idempotency key on anything that mutates, and put validation and authorisation in the executor because arguments produced by a model are untrusted input.
We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.
Give every entity a surrogate primary key but keep a unique constraint on the business identifier, and model a negotiated price as a row with a validity period rather than a mutable column - then copy the agreed price onto the order line so history cannot be rewritten.
Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?
Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.
Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?
Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember. Model the lifecycle explicitly instead.