Database Design & Normalization

IntermediateDatabases
MySQLERDnormalization

Objective

Design relational databases using normalization principles and ERDs.

Tools & Technologies

  • MySQL
  • ERD
  • normalization

Key Commands

CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, ...);
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
EXPLAIN SELECT ...;

Lab Steps

01
ER Diagrams

Design entity-relationship diagrams before writing DDL.

02
Normal Forms

Apply 1NF, 2NF, 3NF normalization to eliminate data redundancy.

03
Indexes

Create appropriate indexes to optimize query performance.

04
Constraints

Enforce data integrity with PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL.

Challenges Encountered

  • Over-normalization can hurt performance — denormalize where needed
  • Indexes speed reads but slow writes

Key Takeaways

  • Design for queries — know your access patterns before designing schema
  • Composite indexes must match query column order