Database Design

Database design is the process of structuring data efficiently so that it is easy to store, retrieve, update, and maintain. A well-designed database reduces redundancy, ensures data accuracy, improves performance, and supports future scalability. Two core pillars of database design are Normalization and Entity-Relationship (ER) modeling.

---

A. Normalization

What is Normalization?

Normalization is the process of organizing data into tables in such a way that:

• Data duplication is minimized
• Data integrity is improved
• Anomalies during INSERT, UPDATE, and DELETE operations are avoided

Normalization is applied through a series of normal forms, each building on the previous one.

---

First Normal Form (1NF)

Rules of 1NF

• Each column must contain atomic (indivisible) values
• No repeating groups or multi-valued attributes
• Each row must be uniquely identifiable (primary key)

---

❌ Not in 1NF (BAD Design)

CREATE TABLE employees_bad (
    employee_id INT,
    name VARCHAR(100),
    phone_numbers VARCHAR(200)
);

Problem

• phone_numbers stores multiple values in one column
• Difficult to search, update, or validate data

---

✅ In 1NF (GOOD Design)

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employee_phones (
    phone_id INT PRIMARY KEY,
    employee_id INT,
    phone_number VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Benefits

• One phone number per row
• Easier validation and indexing
• Supports unlimited phone numbers per employee

---

Second Normal Form (2NF)

Rules of 2NF

• Must already be in 1NF
• No partial dependency
• All non-key columns must depend on the entire primary key

---

❌ Not in 2NF (BAD Design)

CREATE TABLE employee_departments_bad (
    employee_id INT,
    department_id INT,
    department_name VARCHAR(50),
    PRIMARY KEY (employee_id, department_id)
);

Problem

• department_name depends only on department_id
• Causes redundancy and update anomalies

---

✅ In 2NF (GOOD Design)

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employee_departments (
    employee_id INT,
    department_id INT,
    PRIMARY KEY (employee_id, department_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Benefits

• Eliminates partial dependency
• Cleaner separation of concerns
• Easier updates and maintenance

---

Third Normal Form (3NF)

Rules of 3NF

• Must be in 2NF
• No transitive dependencies
• Non-key attributes must depend only on the primary key

---

❌ Not in 3NF (BAD Design)

CREATE TABLE employees_bad (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(50)
);

Problem

• city depends on zip_code, not directly on employee_id
• Causes data inconsistency

---

✅ In 3NF (GOOD Design)

CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(50),
    state VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

Benefits

• Single source of truth
• No repeated city/state values
• Higher data accuracy

---

Why Normalization Matters

• Prevents data inconsistency
• Reduces storage usage
• Simplifies updates
• Improves data integrity
• Makes schema scalable

---

When NOT to Normalize Fully (Denormalization)

Sometimes, databases are partially denormalized to:

• Improve read performance
• Reduce JOIN complexity
• Support analytics workloads

Common in:

• Reporting databases
• Data warehouses
• High-traffic read systems

---

B. Entity-Relationship (ER) Design

ER design visually and logically represents entities, attributes, and relationships in a database.

---

One-to-Many Relationship (1:N)

Example: Department → Employees

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Explanation

• One department can have many employees
• Each employee belongs to one department

Real-World Examples

• Customer → Orders
• Company → Employees

---

Many-to-Many Relationship (M:N)

Example: Students ↔ Courses

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Explanation

• A student can enroll in many courses
• A course can have many students
• Junction table breaks M:N into two 1:N relationships

---

One-to-One Relationship (1:1)

Example: Employee ↔ Login Credentials

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE login_credentials (
    login_id INT PRIMARY KEY,
    employee_id INT UNIQUE,
    username VARCHAR(50),
    password_hash VARCHAR(255),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Explanation

• UNIQUE constraint enforces one-to-one
• Used for security or optional data separation

---

ER Design Best Practices

• Use meaningful table and column names
• Always define primary keys
• Use foreign keys for relationships
• Avoid redundant attributes
• Think in terms of real-world entities

---

Common Database Design Mistakes

• Over-normalization
• Missing primary keys
• Storing multiple values in one column
• No foreign key constraints
• Poor naming conventions

---

Interview Tips (Very Important)

• Explain normalization with examples
• Know difference between 2NF and 3NF
• Be clear about relationships
• Understand when to denormalize
• Draw ER diagrams logically