How do you approach designing a database schema for a new application?

Start by gathering clear requirements, listing the core entities and their relationships, and identifying read and write patterns. Sketch an entity-relationship model and decide which attributes belong to which entities based on ownership and access patterns. For example, if you are building an e-commerce app, model customers, orders, products, and order items, and document whether product details are read often versus updated frequently. Use that insight to choose between embedding related data or keeping it in separate tables to avoid update anomalies. As a tip, keep early designs simple and iterate after measuring usage. Avoid premature denormalization before you understand your access patterns and scaling needs.

Can you explain normalization and when you would denormalize?

Describe normalization as a set of rules to reduce redundancy and ensure data integrity, typically progressing through normal forms like 1NF, 2NF, and 3NF. Explain that normalization helps prevent anomalies and makes updates predictable, but it can increase the number of joins for common queries. Give a practical example: normalize customer addresses into a separate table to avoid repeating address data across orders, then denormalize by copying the address into the order record if you need historical immutability or to reduce expensive joins on high-traffic queries. Use denormalization when read performance is critical and you accept increased complexity for writes and eventual consistency. A good rule is to normalize until your joins become a measurable bottleneck, then denormalize targeted pieces. Avoid denormalizing broadly without benchmarks, because it increases the chance of inconsistent data and harder migrations.

How do you choose primary keys and foreign keys?

Choose primary keys that uniquely identify a row and are stable over time, such as natural keys when truly immutable or surrogate keys like integers or UUIDs when natural keys are likely to change. Use foreign keys to enforce referential integrity and to make relationships explicit in relational databases. For example, use an auto-increment integer for a high-throughput table where joins are frequent, and consider UUIDs for distributed systems where generation must happen outside a central database. When you use UUIDs, be mindful of index fragmentation and consider sequential UUID variants if the database supports them. Always document the reasoning behind key choices and add foreign key constraints where appropriate to prevent orphaned records. Avoid choosing keys that might need to change, such as email addresses, and avoid overusing composite keys unless they match your access patterns.

What indexing strategies do you recommend and what are common pitfalls?

Explain that indexes speed up reads at the cost of slower writes and additional storage, so you should index columns used in WHERE clauses, JOIN keys, and ORDER BY clauses selectively. Choose between single-column, composite, and covering indexes based on query patterns and selectivity. Give an example: if queries frequently filter by (user_id, created_at), create a composite index on those columns in that order to support range queries and sorts, and create covering indexes when you can include needed columns to avoid lookups. Watch out for low-selectivity indexes like boolean flags, which often do not help and can add maintenance overhead. Monitor index usage with database-native tools and remove indexes that are rarely used. Avoid creating many overlapping indexes, because they slow down inserts and increase storage costs.

How do you design a schema for a multi-tenant application?

Discuss the three common approaches: separate database per tenant, separate schema per tenant, and shared schema with tenant identifiers, and map trade-offs between isolation, operational cost, and scalability. Choose the approach based on tenant size, security requirements, and your operational capacity for backups and upgrades. For example, small tenants can share a single schema with a tenant_id column to save resources, while large tenants or those with strict compliance needs may get isolated databases to simplify data residency and performance tuning. Implement row-level security and strict tenant scoping in queries when you use a shared schema to reduce risk of data leaks. Plan for migrations and onboarding processes that differ by approach, and automate tenant provisioning and monitoring. Avoid assuming one model fits all tenants, and prepare to move larger customers to isolated environments if needed.

How would you design for scalability: partitioning, sharding, and replication?

Start by distinguishing vertical scaling from horizontal scaling, and explain that replication helps read scalability and high availability while partitioning or sharding splits data across nodes for write scalability. Describe when to use each: replication for read-heavy workloads, partitioning to manage large tables, and sharding when a single database cannot handle the write volume. Give a concrete scenario: partition a large events table by event_date to speed up queries and maintenance tasks, and shard user data by user_id hash when write throughput requires distributing load across multiple database servers. Use asynchronous replication for reads and failover, but plan for replication lag and the resulting consistency trade-offs. Always measure where the bottleneck actually is before introducing distributed complexity. Avoid inventing a sharding strategy without considering rebalancing, cross-shard joins, and operational overhead.

How do you handle schema migrations in production?

Describe a migration workflow that includes developing migrations in version control, applying changes in small steps, and supporting backward-compatible reads and writes where possible. Use blue-green or rolling deployments for application changes, and prefer additive migrations before destructive ones to reduce downtime risk. Provide an example: when renaming a column, add the new column, backfill data in a safe window, update the application to write to both columns, then remove the old column after you verify stability. Test migrations on staging with production-like data sizes and have a rollback plan that includes fast schema fixes and data restoration steps. Automate migration runs in CI/CD, and include migration health checks in your deployment pipeline. Avoid running long-running DDL statements during peak traffic without maintenance windows, because they can lock tables and cause outages.

How would you model many-to-many relationships and when would you avoid them?

Explain that many-to-many relationships are typically modeled with a junction table that contains foreign keys to the related entities and any relationship-specific attributes. Normalize the junction table, and add composite primary keys or surrogate keys depending on access patterns and the need for referencing the relationship itself. For example, model a user_roles table for mapping users to roles, and include columns like assigned_at or granted_by if you need metadata about the assignment. Avoid true many-to-many representations when your application only ever needs aggregated counts or when a denormalized approach like a JSON array of IDs would simplify reads for a specific workload. Consider indexing the junction table on the most common lookup path to speed up membership checks. Avoid creating extra many-to-many tables without considering whether the relationship will grow large and require partitioning or archiving strategies.

How do you design for temporal data and audit logs?

Start by deciding whether you need immutable historical records or soft deletes and versioning for business entities, and choose a pattern like append-only audit tables, temporal tables, or event sourcing accordingly. Consider the retention policy, query patterns for historical views, and storage cost when choosing a strategy. For example, implement an orders_audit table that records every state change with a timestamp and actor, or use system-versioned temporal tables if your database supports them to query an entity as of a specific time. Plan for indexing time-based queries and for rolling up old audit entries into cheaper storage if retention is long. Make sure to encrypt and secure audit data because it may contain sensitive information. Avoid keeping indefinite high-granularity logs in hot storage without a plan for pruning or archiving.

How would you design a schema for analytics or reporting use cases?

Explain that analytics schemas often differ from OLTP schemas and may use denormalized star or snowflake schemas optimized for read-heavy aggregation queries. Design fact tables for events or transactions and dimension tables for entities that describe those facts, and choose columnar or data-warehouse storage for large-scale analytics workloads. Give a concrete technique: store daily sales as a fact table keyed by date and product_id and keep dimensions for product and store attributes to make aggregations fast and predictable. Use materialized views or pre-aggregations for expensive queries and schedule ETL jobs to keep reporting data current. If reporting needs are minimal, avoid introducing a full data warehouse prematurely and instead add targeted aggregated tables in your OLTP database. Avoid mixing heavy analytical queries on the same database instance that serves low-latency transactional traffic without resource isolation.

Tell me about a time you disagreed with a teammate's database design choice.

Situation: On a project to build an order management system, a teammate proposed embedding order line items as a JSON blob inside the orders table to simplify writes and speed up reads. Task: I had to evaluate whether that approach would cause long-term pain for reporting, updates, and indexing needs while keeping the project on schedule. Action: I researched the query patterns, prepared a short comparison showing trade-offs for JSON versus normalized line item tables, and ran a small prototype measuring query performance and update complexity. I presented the findings, recommended a normalized design with selective denormalization for read-heavy endpoints, and suggested a phased approach to implement and test changes. Result: The team agreed to the phased approach, which allowed us to meet the deadline while keeping reporting paths efficient, and the prototype prevented costly refactors later. The outcome improved maintainability and helped the team adopt a pattern for future designs.

Describe a time when a schema change caused production issues and how you resolved it.

Situation: During a migration to add a NOT NULL constraint on a critical column, we encountered a high rate of failures in the deployment pipeline and some user-facing errors. Task: I needed to stop the outage, stabilize the system, and create a repeatable migration path that would not disrupt users. Action: I rolled back the deployment to stop the immediate failures, analyzed failed writes to identify missing values, and implemented a backfill script that populated defaults for existing rows during low traffic hours. I then applied the constraint in a staged manner: add a default, backfill, update the application to write defaults, and finally enforce the NOT NULL constraint. Result: The rollout succeeded without further customer impact, and we reduced migration risk by documenting the staged process as a migration checklist. The team gained a reliable pattern that decreased similar incidents in subsequent releases.

Give an example of mentoring someone on database design choices.

Situation: A junior engineer was designing a relational schema for a new inventory feature and was unsure how to normalize product variants and stock locations. Task: I needed to help them produce a clear, maintainable model while building their design skills for future work. Action: I reviewed their initial model, asked guiding questions about access patterns and update frequency, and sketched an alternative that separated variants into their own table and linked inventory by location. I walked through the reasoning, common queries, and indexing suggestions, and then paired with them to implement and test the model in a staging environment. Result: The engineer delivered a well-documented schema that addressed scalability and query needs, and they reported increased confidence in their design decisions. The pairing session reduced rework and set a pattern for collaborative schema reviews on the team.

database design Interview Questions: Complete Guide

Database design interview questions often test both your theoretical knowledge and your practical trade-offs when building schemas for real systems. Expect whiteboard or take-home exercises, system design discussions, and follow-up questions about performance and migrations. You can prepare by practicing modeling common domains, explaining choices clearly, and reviewing indexing and normalization trade-offs.

Common Interview Questions

Behavioral Questions (STAR Method)

STAR Method: Structure your answers using Situation, Task, Action, and Result to tell compelling stories about your experience.

Questions to Ask the Interviewer

Show your interest by asking thoughtful questions

•What are the most common query patterns and performance pain points the team sees with the current schema?
•How does the team approach schema migrations and rollbacks in production environments?
•Can you describe the data retention and archival policies for large tables in this system?
•What monitoring and observability tools do you use to track database performance and index usage?
•How do you balance transactional consistency and availability for user-facing features here?

Interview Preparation Tips

1
Practice modeling a few common domains like e-commerce and social graphs on a whiteboard, and explain your trade-offs aloud to build clarity and confidence. Review real queries for those models to justify your indexing and normalization choices.
2
Read about the specific database technologies listed in the job description and prepare one or two examples of when you would choose their features over alternatives. Focus on practical trade-offs like isolation levels, replication modes, and indexing quirks.
3
When answering, state your assumptions upfront, sketch the high-level design, then drill into details like keys, indexes, and migrations; interviewers appreciate clear reasoning over perfect designs. If you do not know a specific implementation detail, explain how you would find and validate the answer.
4
Bring a few concise notes of past designs or whiteboard sketches you can reference, and be ready to discuss measurable outcomes like query latencies improved or migration times reduced. Use those examples to show you can balance design quality with delivery constraints.

database design Interview Questions: Complete Guide

Michael Rodriguez

Common Interview Questions

Behavioral Questions (STAR Method)

Questions to Ask the Interviewer

Interview Preparation Tips

Related guides

system design Interview Questions: Complete Guide

database administrator Interview Questions: Complete Guide

design engineer Interview Questions: Complete Guide

design system designer Interview Questions: Complete Guide

database design Interview Questions: Complete Guide

Michael Rodriguez

Common Interview Questions

Q1How do you approach designing a database schema for a new application?

Q2Can you explain normalization and when you would denormalize?

Q3How do you choose primary keys and foreign keys?

Q4What indexing strategies do you recommend and what are common pitfalls?

Q5How do you design a schema for a multi-tenant application?

Q6How would you design for scalability: partitioning, sharding, and replication?

Q7How do you handle schema migrations in production?

Q8How would you model many-to-many relationships and when would you avoid them?

Q9How do you design for temporal data and audit logs?

Q10How would you design a schema for analytics or reporting use cases?

Behavioral Questions (STAR Method)

B1Tell me about a time you disagreed with a teammate's database design choice.

B2Describe a time when a schema change caused production issues and how you resolved it.

B3Give an example of mentoring someone on database design choices.

Questions to Ask the Interviewer

Interview Preparation Tips

Related guides

system design Interview Questions: Complete Guide

database administrator Interview Questions: Complete Guide

design engineer Interview Questions: Complete Guide

design system designer Interview Questions: Complete Guide