Database Design & Normalization

Good database design ensures data is organized, consistent, efficient, and scalable.
This involves modeling entities, defining relationships, applying normal forms, and knowing when to denormalize.

1. Data Modeling & ER Diagrams

Data modeling begins with identifying entities, their attributes, and the relationships between them.

Common Entity Examples

• Customers

• Orders

• Products

• Employees

• Departments

• Payments

Relationship Types

Sample ERD Structure (Online Store)

Customers (customer_id PK) |1 |∞ Orders (order_id PK, customer_id FK) |1 |∞ Order_Items (order_id FK, product_id FK) |∞ |1 Products (product_id PK)

2. Normal Forms (1NF → 3NF)

Normalization reduces redundancy and improves data integrity.

1NF – First Normal Form

Requirements:

• No repeating groups

• No multivalue fields

• Each cell contains one value

Bad Example (not 1NF):

✔ Fix: Move products to a separate table.

2NF – Second Normal Form

Requirements:

• Already in 1NF

• No partial dependency on a composite PK

Bad Example (order_items):

(order_id, product_id) → quantity product_name → depends only on product_id (partial dependency)

✔ Fix:

• Keep product_name in products table only.

3NF – Third Normal Form

Requirements:

• Already in 2NF

• No transitive dependencies

Bad Example (employees):

employee_id → department_id → department_name

department_name depends on department_id, not employee_id.

✔ Fix:
Move department_name into a departments table.

3. Denormalization: When & Why

Normalization improves consistency, but sometimes hurts performance.

Reasons to Denormalize

• Reduce JOINs in high-traffic systems

• Improve read performance (e.g., analytics dashboards)

• Cache aggregated values (e.g., total_order_value stored in orders)

Examples of Denormalization

• Storing customer_name directly in orders for faster reporting

• Keeping inventory_count in the product table instead of recalculating

Rule of thumb:
✔ Normalize by default
✔ Denormalize intentionally for performance or simplicity

4. Many-to-Many Relationships (Junction Tables)

A junction (bridge) table breaks M:N into two 1:N relationships.

Example: products ↔ categories

Products (product_id PK) Categories (category_id PK) Product_Categories ( product_id FK, category_id FK, PRIMARY KEY (product_id, category_id) )

This avoids duplication and allows products to belong to many categories.

5. Real-World Schema Design Examples

A. Online Store

• customers

• products

• orders

• order_items (junction table)

• payments

• inventory

• categories + product_categories (junction table)

B. HR System

• employees

• departments

• salaries

• positions

• employee_project_assignments (junction table)

C. Education / LMS

• students

• courses

• enrollment (junction table)

• instructors

• assignments

• submissions

These schemas rely on normalization, foreign keys, and relationship design.

6. Practical Exercises

Exercise 1: Design a database for an online store

Instructions:

• Identify entities

• Define attributes

• Determine relationships

• Build an ER diagram

Expected entities:

• customers

• products

• orders

• order_items

• payments

• categories

• product_categories

I can generate a full ERD if you'd like.

Exercise 2: Normalize a messy spreadsheet

Given Messy Table

Problems:

• Multiple values per cell

• Mixed customer & order data

• Repeating product information

Normalized Version

customers
(customer_id PK, name, email)

orders
(order_id PK, customer_id FK)

products
(product_id PK, product_name, price)

order_items
(order_id FK, product_id FK, quantity)

A complete ERD diagram for the store database