Database Design Fundamentals

Why Database Design Matters

Database design is the process of structuring a database to support efficient data storage, integrity, and scalability. A good design avoids redundancy, ensures consistency, and enhances query performance.

Normalization

Normalization organizes data into multiple related tables to minimize redundancy and dependency.

• 1NF: Eliminate repeating groups.
• 2NF: Remove partial dependencies.
• 3NF: Remove transitive dependencies.
• BCNF: Stronger form of 3NF.

-- Normalized design
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

ER Diagrams

Entity-Relationship diagrams visualize entities, attributes, and relationships.

Indexing

Indexes improve query performance but require careful design:

• B-tree indexes: Default, good for ranges.
• Hash indexes: Equality lookups.
• Composite indexes: Multi-column filters.

-- Composite index
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date DESC);

Practice Quiz