Database Design FAQ
Q: Should I always normalize to 3NF, or is that overkill sometimes?
A: 3NF is a strong, reasonable default for transactional (OLTP) systems where data integrity and avoiding update anomalies matter most. It's not universal — analytical/reporting systems commonly use intentionally denormalized structures (Star Schema, see Overview) because query performance for large aggregations matters more than update-anomaly risk in a system that's mostly read, rarely updated after initial load. The real skill is recognizing which system you're designing for, not applying 3NF uniformly everywhere by default.
Q: UUID or auto-incrementing integer for primary keys — which should I use?
A: Auto-incrementing integers are smaller (less storage/index overhead), sequential (better for index locality on inserts), and simpler for a single-database-instance system. UUIDs don't require a central coordinator to guarantee uniqueness, which matters specifically once you're generating IDs across multiple, independent systems or shards (see Advanced/Real World's Instagram example) where a simple auto-increment would produce collisions. If you're not sharding or generating IDs across independent systems, auto-incrementing integers are usually the simpler, sufficient default — reach for UUIDs when you have the specific distributed-generation problem that justifies them, not by default.
Q: How many indexes is "too many" on a single table?
A: There's no fixed universal number — the real question is whether each index is justified by an actual, real query pattern (see Fundamentals' cost tradeoff). A write-heavy table with ten indexes that are all genuinely used by real, frequent queries can be correctly designed; a read-heavy table with three indexes where one is never actually used by any real query is over-indexed. Audit index usage against actual query patterns (most database engines have a way to check index usage statistics) rather than reasoning from an assumed threshold.
Q: Is it ever okay to skip foreign key constraints for performance reasons?
A: This is a real, debated tradeoff at very high write-throughput scale — foreign key checks do add real overhead to every write. But skipping them means giving up the database's own referential-integrity guarantee (see Fundamentals) and pushing that responsibility entirely onto application code, which is a real, meaningful risk (see Fundamentals' point about application code having bugs or being bypassed). This should be a deliberate, measured decision at genuine scale where the overhead is confirmed to matter, not a default optimization applied preemptively "for performance," which is a common overcorrection based on theoretical rather than measured cost.
Q: What's the difference between a database design interview and a system design interview — do I need to prepare separately?
A: Related but distinct emphases — a database design interview focuses specifically on schema structure, normalization decisions, indexing, and query patterns for a given domain. A system design interview is broader, including database design as one component alongside application architecture, caching, scaling, and infrastructure decisions. Strong database design skill (this page's content) is necessary for both, but system design interviews also require the broader architectural reasoning covered in this site's Architecture Patterns technology — worth preparing both if system design interviews are part of your target role's process.
Q: Do NoSQL databases make schema design unnecessary?
A: No — "schemaless" describes the database engine not enforcing a rigid structure, not that design decisions disappear. You still need to decide document/collection structure, what data to embed versus reference, indexing strategy, and consistency tradeoffs (see Overview's CAP theorem section) — these are still real design decisions, just made without the database enforcing them for you the way a relational schema's constraints do. Arguably, schema design discipline matters more with NoSQL in some ways, precisely because the database won't catch a poorly-considered structure for you at write time the way a relational schema's constraints would.