Describe a time you designed a database schema from scratch for a new feature.
sql-mid-003
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
When we built a multi-step onboarding workflow I designed the schema by starting with the core entities — users, onboarding_steps, and user_step_progress — then modelling the relationships. I kept the step definitions normalised in a separate table so product could add new steps without a code deploy. I added a composite unique constraint on (user_id, step_id) to prevent duplicate progress rows, and an enum column for status rather than a boolean to make future states easy to add. I ran the draft schema by the senior engineer and a DBA before writing any migrations, specifically asking about expected query patterns so I could decide which indexes to create upfront. After launch I monitored slow query logs for two weeks and added one additional index I had missed.
Follow-up
When you say you kept step definitions normalised, how did you balance that against the need for fast reads? Did you ever consider caching or denormalisation?