Database Design

Until now, we focused on writing queries, optimizing performance, and using advanced SQL features.

In this lesson, we step back and focus on Database Design — how to plan tables, relationships, and structure before writing large amounts of SQL.

Good database design makes everything easier. Bad design makes everything painful.

What is Database Design?

Database design is the process of defining:

• Tables and their columns
• Relationships between tables
• Constraints and keys
• Rules for storing data

The goal is to store data accurately, efficiently, and scalably.

Why Database Design Matters

Poor database design can lead to:

• Duplicate data
• Data inconsistency
• Slow queries
• Difficult maintenance

Good design ensures:

• Reliable data
• Better performance
• Easier scaling

Core Design Principles

Strong database designs follow these principles:

• Each table represents one concept
• Avoid storing redundant data
• Use clear primary keys
• Define proper relationships

Identifying Entities

An entity represents a real-world object.

Examples:

• Users
• Employees
• Orders
• Products

Each entity usually becomes a table.

Choosing Primary Keys

Every table should have a primary key.

Best practices:

• Use numeric surrogate keys (INT, AUTO_INCREMENT)
• Avoid meaningful data as primary keys
• Keep primary keys stable

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);
  

Relationships Between Tables

Tables often relate to each other.

Common relationship types:

• One-to-One
• One-to-Many
• Many-to-Many

One-to-Many Example

One department has many employees.

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

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

Many-to-Many Relationships

Many-to-many relationships require a junction table.

Example: Students and Courses.

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);
  

Normalization (Quick Overview)

Normalization is the process of organizing data to reduce duplication.

Key goals:

• Eliminate redundant data
• Ensure data consistency
• Simplify updates

We already studied normalization earlier; design applies those rules in practice.

Denormalization (When Needed)

Sometimes performance requires denormalization.

This means intentionally duplicating data to reduce JOINs.

Use denormalization carefully and deliberately.

Handling Optional Data

Not all data fits neatly into tables.

Strategies:

• Allow NULL values
• Use separate tables
• Use JSON for flexible attributes

Designing for Scalability

A good design considers future growth:

• Use proper indexing
• Avoid hard-coded limits
• Plan for large data volumes

Design once, scale many times.

Common Database Design Mistakes

• Overusing one large table
• Storing comma-separated values
• Missing foreign keys
• Ignoring future requirements

Real-World Design Workflow

1. Understand the business problem
2. Identify entities
3. Define relationships
4. Normalize tables
5. Optimize with indexes

Why Database Design is a Core Skill

Strong database design:

• Reduces bugs
• Improves performance
• Makes systems maintainable

Great SQL developers always think about design first.

What’s Next?

In the next lesson, we will explore Data Warehouse Basics, focusing on analytical databases and reporting-oriented design.