Database Schema Design, Normalization, & Relationships

Interview Definition:

"Normalization is just the process of organizing your database tables so you don't repeat the same data in multiple places. It prevents issues where you have to remember to update the same information in five different spots."

1. Anomalies (Why we need Normalization)

Consider an unnormalized table tracking Employees and their Departments:

| EmpID | EmpName | DeptName | DeptManager |
|-------|---------|----------|-------------|
| 1     | Alice   | IT       | Bob         |
| 2     | Charlie | IT       | Bob         |

Update Anomaly: If Bob is replaced by Eve as IT manager, you must update multiple rows. If one row is missed, the database is inconsistent.
Insert Anomaly: You cannot add a new Department (e.g., HR) until you hire an Employee for it, because EmpID is the primary key and cannot be null.
Delete Anomaly: If Charlie is the only person in Marketing and he resigns, deleting his row accidentally deletes all information about the Marketing department.

2. Normal Forms (The Steps)

First Normal Form (1NF)

Rule: Columns must contain atomic (indivisible) values. No repeating groups, arrays, or comma-separated lists.

Violating 1NF:

| OrderID | Items           |
|---------|-----------------|
| 100     | Apple, Banana   |

Fix (1NF):

| OrderID | Item   |
|---------|--------|
| 100     | Apple  |
| 100     | Banana |

Second Normal Form (2NF)

Rule: Must be in 1NF AND have no partial dependencies. Every non-primary-key attribute must depend on the entire primary key. (This only applies if the primary key is a composite of multiple columns).

Violating 2NF: (Primary Key is StudentID + CourseID)

| StudentID | CourseID | StudentName | CourseFee |
|-----------|----------|-------------|-----------|
| 1         | CS101    | Alice       | $500      |

Issue: StudentName depends only on StudentID. CourseFee depends only on CourseID. Fix (2NF): Break into 3 tables: Students(StudentID, StudentName), Courses(CourseID, CourseFee), Enrollments(StudentID, CourseID).

Third Normal Form (3NF)

Rule: Must be in 2NF AND have no transitive dependencies. Non-key attributes cannot depend on other non-key attributes. ("Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key").

Violating 3NF:

| EmpID | Name  | ZipCode | City     |
|-------|-------|---------|----------|
| 1     | Alice | 10001   | New York |

Issue: City depends on ZipCode, and ZipCode depends on EmpID. Fix (3NF): Separate into Employees(EmpID, Name, ZipCode) and Locations(ZipCode, City).

Boyce-Codd Normal Form (BCNF)

A slightly stronger version of 3NF. Rule: For any dependency A -> B (A determines B), A must be a superkey (a unique identifier). It addresses edge cases where 3NF fails due to overlapping composite keys.

3. Denormalization (When to break the rules)

While 3NF is the theoretical ideal, in highly scaled read-heavy systems, joining 5 tables to serve a single user request can be too slow.

Denormalization is the intentional introduction of redundancy to optimize read performance.

Example: In a Twitter clone, calculating the follower_count by running SELECT COUNT(*) FROM follows WHERE user_id = X every time someone loads a profile is too slow.
Denormalized Solution: Add a follower_count integer column directly to the users table. You trade write speed (you must update this count on every follow/unfollow) for instant read speed.

4. Entity Relationships

One-to-One (1:1)

Example: User and UserProfile.
Implementation: Put a user_id in the Profile table as a Foreign Key, and make it UNIQUE.

One-to-Many (1:N)

Example: Department and Employee.
Implementation: Put department_id as a Foreign Key in the Employee table.

Many-to-Many (M:N)

Example: Student and Course.
Implementation: Requires a Junction Table (or Associative Entity).

Table: Students (student_id)
Table: Courses (course_id)
Junction Table: Enrollments (student_id, course_id)

04-normalization.md