Database Design and Normalization

TL;DR

Database design is about structuring your data efficiently and logically to prevent problems. Normalization is a key process that helps you organize your database tables to reduce data redundancy and improve data integrity. Following normalization rules makes your database easier to manage and less prone to errors.

1. The Mental Model

Think of database design like organizing your closet. You wouldn't throw all your clothes into one big pile, right? Instead, you'd separate shirts from pants, and perhaps even organize by color or season. Database design is the same idea, but for information.

2. The Core Material

Database design is the blueprint for your database. It defines how data is stored, accessed, and managed. Normalization is a systematic approach to breaking down large tables into smaller, related tables to eliminate data redundancy and improve data integrity. It's guided by a set of rules called Normal Forms (NF).

Why Normalize?

Photo by Ann H on Pexels

• Reduced Redundancy: Avoids storing the same data multiple times, saving space.
• Improved Data Integrity: Ensures data is consistent and accurate. Updates only need to happen in one place.
• Easier Maintenance: Makes it simpler to add, delete, or modify data without causing inconsistencies.
• Better Performance: Smaller, focused tables can often lead to faster queries.

The Normal Forms

Photo by Steve A Johnson on Pexels

Normalization typically involves moving through several normal forms. We'll focus on the most common ones: 1NF, 2NF, and 3NF.

1NF (First Normal Form)

A table is in 1NF if:
1. Each column contains atomic (indivisible) values. No multi-valued attributes in a single cell.
2. Each row is unique.
3. There's a primary key to uniquely identify each row.

Example: A "Customers" table with a CustomerPhone column containing "555-1234, 555-5678" for one customer is not in 1NF. You'd need separate rows or a separate table for phones.

2NF (Second Normal Form)

A table is in 2NF if:
1. It's already in 1NF.
2. All non-key attributes are fully dependent on the entire primary key. This applies mainly to tables with composite primary keys (keys made of two or more columns). If a non-key attribute depends only on part of the composite key, it violates 2NF.

Example: In a OrderDetails table with a composite primary key (OrderID, ProductID), if ProductName depends only on ProductID (and not OrderID), it's not in 2NF. ProductName should move to a Products table.

3NF (Third Normal Form)

A table is in 3NF if:
1. It's already in 2NF.
2. There are no transitive dependencies. A transitive dependency occurs when a non-key attribute is dependent on another non-key attribute. In simpler terms, no non-key attribute should determine another non-key attribute.

Example: In a Customers table, if ZipCode determines City and State, and City and State are non-key attributes, then City and State are transitively dependent on ZipCode. City and State should move to a separate ZipCodes table.

graph TD
    A["Initial Unnormalized Table"] --> B{"Is it 1NF?"};
    B -- No --> C["Break multi-value attributes into new rows/tables. Add PK."];
    B -- Yes --> D{"Is it 2NF?"};
    C --> B;
    D -- No --> E["Remove partial dependencies. Create new tables for partial keys."];
    D -- Yes --> F{"Is it 3NF?"};
    E --> D;
    F -- No --> G["Remove transitive dependencies. Create new tables for dependent attributes."];
    F -- Yes --> H["Table is in 3NF (or higher)."];
    G --> F;

3. Worked Example

Let's take an unnormalized table for an online course registration system.

Initial CourseEnrollments Table:

Problems:
- StudentName repeats for StudentID 101.
- CourseTitle, InstructorName, InstructorDept repeat for CourseCode CS101.
- InstructorDept depends on InstructorName which depends on CourseCode. (Transitive dependency!)

Step 1: Achieve 1NF
- All values are atomic.
- Let's assume (StudentID, CourseCode) forms a composite primary key, making each row unique.

Step 2: Achieve 2NF
- Is StudentName fully dependent on (StudentID, CourseCode)? No, it only depends on StudentID.
- Is CourseTitle, InstructorName, InstructorDept fully dependent on (StudentID, CourseCode)? No, they depend on CourseCode.

We need to break this down.

Students Table:
| StudentID (PK) | StudentName |
| :------------- | :---------- |
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |

Courses Table (Still not fully normalized for 3NF):
| CourseCode (PK) | CourseTitle | InstructorName | InstructorDept |
| :-------------- | :------------ | :------------- | :------------- |
| CS101 | Intro to CS | Dr. Smith | Computer Sci |
| MA201 | Calculus I | Prof. Jones | Mathematics |
| PH101 | Intro to Phil | Dr. Lee | Philosophy |

Enrollments Table (Linking table):
| StudentID (FK) | CourseCode (FK) | Grade |
| :------------- | :-------------- | :---- |
| 101 | CS101 | A |
| 101 | MA201 | B |
| 102 | CS101 | C |
| 103 | PH101 | A |
Primary Key for Enrollments: (StudentID, CourseCode)

Step 3: Achieve 3NF
Look at the Courses table:
| CourseCode (PK) | CourseTitle | InstructorName | InstructorDept |
| :-------------- | :------------ | :------------- | :------------- |
| CS101 | Intro to CS | Dr. Smith | Computer Sci |
| MA201 | Calculus I | Prof. Jones | Mathematics |
| PH101 | Intro to Phil | Dr. Lee | Philosophy |

Here, InstructorDept depends on InstructorName, which depends on CourseCode. This is a transitive dependency (CourseCode → InstructorName → InstructorDept).
We need to create an Instructors table.

Final Tables in 3NF:

Students Table:
| StudentID (PK) | StudentName |
| :------------- | :---------- |
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |

Instructors Table:
| InstructorID (PK) | InstructorName | InstructorDept |
| :---------------- | :------------- | :------------- |
| 1 | Dr. Smith | Computer Sci |
| 2 | Prof. Jones | Mathematics |
| 3 | Dr. Lee | Philosophy |
(I've added InstructorID as a new primary key for instructors, which is good practice. InstructorName could be a PK if unique, but IDs are safer.)

Courses Table:
| CourseCode (PK) | CourseTitle | InstructorID (FK) |
| :-------------- | :------------ | :---------------- |
| CS101 | Intro to CS | 1 |
| MA201 | Calculus I | 2 |
| PH101 | Intro to Phil | 3 |

Enrollments Table:
| StudentID (FK) | CourseCode (FK) | Grade |
| :------------- | :-------------- | :---- |
| 101 | CS101 | A |
| 101 | MA201 | B |
| 102 | CS101 | C |
| 103 | PH101 | A |

Now, if Dr. Smith changes departments, you only update it in the Instructors table once. If a course title changes, you update it in the Courses table once. No redundant data!

4. Key Takeaways

• Database design is about structuring data logically to ensure efficiency and integrity.
• Normalization is a systematic process to reduce data redundancy and improve consistency.
• 1NF requires atomic values in columns and unique rows, typically with a primary key.
• 2NF ensures all non-key attributes in a composite primary key table depend on the entire key.
• 3NF eliminates transitive dependencies, meaning no non-key attribute determines another non-key attribute.
• Aiming for 3NF is usually sufficient for most real-world applications.
• Keys (primary and foreign) are crucial for linking tables together without repeating data.

Common mistakes to avoid:
- Not normalizing enough: This leads to redundant data, update anomalies, and inconsistent information.
- Over-normalizing: Going beyond 3NF (e.g., BCNF, 4NF) can sometimes make queries overly complex, which might be overkill for smaller projects.
- Using meaningful primary keys: Using a StudentName as a primary key is bad; names can change or not be unique. Use an auto-incrementing ID.
- Ignoring data types: Choosing the wrong data type (e.g.,