- Published on
Database Design
- Authors
- Name
- Lucian Oprea
- @LucianDSA_
00:30:00
Data Modeling
⏷ 1. What is normalization, and why is it important in database design?
⏷ 2. Explain the difference between primary keys and foreign keys.
⏷ 3. How would you design a database for a multi-tenant application?
⏷ 4. How do you balance normalization and performance optimization in database design?
⏷ 5. How would you handle evolving schema requirements in a rapidly changing application?
⏷ 6. What are the trade-offs between relational and NoSQL databases for hierarchical data?
Database Normalization and Denormalization
⏷ 7. Explain the different normal forms (1NF, 2NF, 3NF, BCNF) with examples.
⏷ 8. When would you choose denormalization over normalization?
⏷ 9. How would you design a denormalized schema for a reporting system?
⏷ 10. What are the implications of denormalization for data consistency and query complexity?
⏷ 11. How would you design a schema that is partially normalized for both transactional and analytical workloads?
⏷ 12. How do you identify and fix transitive dependencies in a poorly designed schema?
Indexing and Query Optimization
⏷ 13. What are the different types of indexes, and how do they impact performance?
⏷ 14. How would you optimize a query with multiple joins and subqueries?
⏷ 15. When should you use composite indexes in a database?
⏷ 16. How would you design an index strategy for a table with billions of rows and high write throughput?
⏷ 17. What are the trade-offs of using full-text search indexes compared to regular indexes for text-heavy data?
⏷ 18. How would you analyze and resolve index fragmentation issues?
Transactions and Concurrency
⏷ 19. What is ACID, and how does it impact database transactions?
⏷ 20. How would you handle deadlocks in a relational database?
⏷ 21. Explain the differences between pessimistic and optimistic concurrency control.
⏷ 22. How would you design a database schema to minimize locking contention for high-concurrency systems?
⏷ 23. What are the implications of using snapshot isolation in a distributed database?
⏷ 24. How would you ensure atomicity in a multi-step transaction that spans multiple tables?
Database Scaling and Partitioning
⏷ 25. What is database sharding, and when should it be used?
⏷ 26. How would you design a partitioning strategy for time-series data?
⏷ 27. Explain the trade-offs between vertical and horizontal scaling.
⏷ 28. How would you manage cross-shard joins in a sharded database?
⏷ 29. What are the challenges of implementing consistent hashing for partitioning?
⏷ 30. How would you design a schema to optimize read performance in a geo-distributed database?
Data Integrity and Constraints
⏷ 31. What is the role of foreign key constraints in maintaining data integrity?
⏷ 32. How would you enforce unique constraints across multiple columns?
⏷ 33. Explain the difference between check constraints and triggers.
⏷ 34. How would you handle enforcing constraints in a distributed NoSQL database that lacks built-in support?
⏷ 35. What are the performance implications of using cascading deletes in a highly active table?
⏷ 36. How would you implement custom validation rules that cannot be handled by built-in constraints?
NoSQL Database Design
⏷ 37. How would you design a schema in MongoDB for a product catalog?
⏷ 38. What are the trade-offs of using document stores over relational databases?
⏷ 39. Explain how you would design a key-value store for high-speed lookups.
⏷ 40. How do you model many-to-many relationships in a NoSQL database like DynamoDB?
⏷ 41. What are the challenges of maintaining consistency in eventual-consistency NoSQL databases?
⏷ 42. How would you design a scalable schema for a real-time chat application in Cassandra?
Database Security
⏷ 43. How would you implement row-level security in a database?
⏷ 44. What are the best practices for encrypting sensitive data in a database?
⏷ 45. Explain the importance of audit logs in database security.
⏷ 46. How would you design a multi-tenant database with tenant-specific access restrictions?
⏷ 47. What are the trade-offs of using field-level encryption versus database-level encryption?
⏷ 48. How would you detect and prevent SQL injection vulnerabilities in a database design?
Backup, Recovery, and High Availability
⏷ 49. What are the differences between full, incremental, and differential backups?
⏷ 50. How would you design a disaster recovery plan for a database system?
⏷ 51. Explain how replication works in PostgreSQL.
⏷ 52. How would you minimize downtime during a database failover in a globally distributed system?
⏷ 53. What are the challenges of maintaining data consistency during a point-in-time recovery?
⏷ 54. How would you design a schema to ensure zero data loss in a highly available system?