Database Design for Modern Applications

# Database Design for Modern Applications A poorly designed schema causes problems that are expensive to fix later — data duplication, slow queries, and inconsistent state. Good design decisions early prevent most of this. ## Normalization Basics Normalization organizes data to reduce duplication — storing a student's course information once and referencing it, rather than repeating course details on every enrollment record. ``` students(id, name, email) courses(id, title, duration) enrollments(id, student_id, course_id, enrolled_at) ``` ## When to Denormalize Strict normalization isn't always right for performance-critical read paths. Sometimes storing a computed or duplicated value (like a cached `total_students` count on a course) avoids expensive joins on every read — a deliberate tradeoff, not a mistake. ## Choosing Relationships - **One-to-many**: one course has many enrollments. - **Many-to-many**: students and courses, resolved through a join table like `enrollments`. - **One-to-one**: rare, usually for splitting large or sensitive data into a separate table. ## Indexing An index speeds up lookups on a column at the cost of slightly slower writes. Index columns you frequently filter or sort by — like `email` for login lookups — but avoid indexing every column blindly. ## Foreign Keys and Constraints Foreign key constraints prevent orphaned records (an enrollment pointing to a deleted course) at the database level, catching bugs that application code alone might miss. ## Conclusion Good schema design balances normalization for consistency against denormalization for performance, guided by how the data is actually queried — not just theoretical correctness.