database-workflow

Database schema design uses normalization principles like 1NF, 2NF, and 3NF to organize data efficiently and reduce redundancy. You should apply these forms sequentially to structure tables, establish relationships, and identify when denormalization is appropriate for performance.

Database migrations should be managed through version control with up and down scripts for every change. This approach ensures you can apply schema modifications predictably and execute rollback plans safely if errors occur during deployment.

Fixing N+1 query problems requires query optimization techniques like eager loading relationships and using EXPLAIN to analyze execution plans. You must identify inefficient ORM patterns and replace them with batched queries or proper joins.

Choose NoSQL over SQL when your data structure is highly variable, requires horizontal scaling, or does not fit relational models. SQL is preferred for structured data requiring strict ACID compliance and complex relational queries.

Testing database logic involves using dedicated test databases, setting up fixtures, and leveraging transaction rollbacks to isolate tests. This ensures each test runs in a clean state without persisting mock data to your actual database.

Active Record simplifies CRUD operations by embedding database logic in entities, while Data Mapper separates persistence from business logic. Choose Active Record for rapid development and Data Mapper for complex domain logic requiring loose coupling.