All Guides02-database-design-sql
Database Design & SQL
Relational data modeling, queries, transactions, and indexing patterns for backend services.
backend-engineerdatabasesqltransactionsindexing
Database Design & SQL
Backend systems rely on relational models and SQL for core data integrity and query efficiency.
Key concepts
- Schema design: primary keys, foreign keys, normalization, and sensible denormalization.
- CRUD mapping: insert, update, delete, and select operations.
- Transactions: ACID properties, isolation levels, and deadlock handling.
- Indexing: when to add single-column and composite indexes, and how it impacts writes.
- Query tuning: explain plans, joins, window functions, and pagination.
Engineering focus
Model data for correctness and performance, and verify query behavior through explain plans before deployment.
Interview Questions
- What is the difference between normalization and denormalization?
- Why are foreign keys important in a relational schema?
- Describe the ACID properties of a transaction.
- How does an index affect query performance and write performance?
- When should you use a window function instead of a group by query?