MySQL & Database Design

Introduction to Database Design

13 min Lesson 1 of 40

Introduction to Database Design

Welcome to the world of database design! In this lesson, we'll explore why database design is one of the most critical aspects of application development and how good design decisions early on can save you from countless headaches later.

Why Database Design Matters

Database design is the foundation of any data-driven application. A well-designed database ensures data integrity, improves performance, simplifies maintenance, and scales with your application's growth.

Key Fact: Studies show that 80% of application performance issues stem from poor database design rather than inefficient code. Getting the design right from the start is crucial.

Good vs Bad Database Design

Let's compare two approaches to storing customer orders:

❌ Bad Design (Everything in One Table):
CREATE TABLE orders_bad (
    id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    product_names TEXT,
    product_prices TEXT,
    total_amount DECIMAL(10,2)
);

Problems:
- Data duplication (customer info repeated for each order)
- Multiple products stored as comma-separated text
- Difficult to query or update individual items
- No data integrity enforcement
- Waste of storage space

✓ Good Design (Normalized Tables):
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

Benefits:
- No data duplication
- Easy to query and update
- Data integrity enforced by foreign keys
- Efficient storage
- Scalable structure

Database Design Lifecycle

Professional database design follows a systematic approach:

1. Requirements Gathering
   - Understand what data needs to be stored
   - Identify business rules and constraints
   - Document user needs and use cases

2. Conceptual Design
   - Create Entity-Relationship Diagrams (ERD)
   - Identify entities and relationships
   - Define attributes for each entity

3. Logical Design
   - Convert ERD to table structures
   - Apply normalization rules
   - Define primary and foreign keys

4. Physical Design
   - Choose specific data types
   - Create indexes for performance
   - Consider storage and partitioning

5. Implementation
   - Write CREATE TABLE statements
   - Implement constraints and triggers
   - Load initial data

6. Testing & Refinement
   - Test with realistic data volumes
   - Optimize slow queries
   - Adjust design based on performance

Requirements Gathering

The first step in database design is understanding what you need to build. Ask these critical questions:

What data needs to be stored? (customers, products, orders, etc.)
What are the relationships? (one customer can have many orders)
What queries will be common? (find all orders for a customer)
What are the business rules? (email must be unique, price cannot be negative)
What is the expected data volume? (100 customers vs 1 million customers)
What are the performance requirements? (response time, concurrent users)

Pro Tip: Spend time with stakeholders and end-users. A 30-minute conversation can prevent weeks of redesign work later.

Conceptual vs Logical vs Physical Design

Understanding these three levels helps you think about design at the right level of abstraction:

Conceptual Design (High-Level, Abstract)
- Focus: What data exists and how it relates
- Tool: Entity-Relationship Diagrams
- Example: "Customer places Order containing Products"
- Technology-independent
- Audience: Business stakeholders

Logical Design (Medium-Level, Structured)
- Focus: How data is organized in tables
- Tool: Table schemas and relationships
- Example: customers table, orders table, order_items table
- Still somewhat technology-independent
- Audience: Database designers and developers

Physical Design (Low-Level, Specific)
- Focus: How data is stored on disk
- Tool: Specific SQL DDL statements
- Example: INT(11), VARCHAR(100), INDEX idx_email
- Technology-specific (MySQL, PostgreSQL, etc.)
- Audience: Database administrators

Common Database Design Mistakes

Learn from these common pitfalls:

1. Storing Multiple Values in One Column
❌ Bad: colors VARCHAR(100) storing "red,blue,green"
✓ Good: Separate table for colors with one row per color

2. Not Using Primary Keys
❌ Bad: Table without unique identifier
✓ Good: Every table has a PRIMARY KEY

3. Using Meaningless Column Names
❌ Bad: col1, col2, field3, data
✓ Good: first_name, email, created_at

4. Not Planning for Growth
❌ Bad: Using TINYINT for user_id (max 255 users)
✓ Good: Using INT or BIGINT for scalability

5. Over-Normalizing
❌ Bad: Creating separate table for every single attribute
✓ Good: Balance normalization with practical performance

6. Ignoring Indexes
❌ Bad: No indexes on frequently queried columns
✓ Good: Strategic indexes on foreign keys and search columns

Critical Mistake: Never store passwords in plain text! Always use proper hashing (bcrypt, Argon2). This is a security fundamental, not just a design issue.

Example: Blog Database Requirements

Let's practice requirements gathering for a simple blog:

Requirements:
- Store blog posts with title, content, author
- Posts can have multiple tags
- Users can comment on posts
- Track when posts are published
- Support multiple authors
- Allow draft posts (not yet published)

Entities Identified:
- Users (authors)
- Posts
- Comments
- Tags

Relationships Identified:
- User writes many Posts (1-to-many)
- Post has many Comments (1-to-many)
- Post has many Tags (many-to-many)
- User writes many Comments (1-to-many)

Business Rules:
- Email must be unique per user
- Published posts must have publish_date set
- Comments must be associated with a post
- Post slug must be unique for URLs

Design Principles to Remember

Data Integrity: Use constraints to enforce business rules
Consistency: Follow naming conventions throughout
Simplicity: Keep it simple; complexity grows naturally
Flexibility: Design for change; requirements evolve
Performance: Consider query patterns early
Documentation: Document assumptions and decisions

Practice Exercise:

Scenario: You're designing a database for a library system. Think about:

What entities would you need? (Books, Members, etc.)
What relationships exist between them?
What are 3 important business rules?
What queries would be common?

Suggested Entities:

Books (title, ISBN, author, published_year, copies_available)
Members (name, email, membership_date, phone)
Loans (book_id, member_id, loan_date, due_date, return_date)
Authors (name, biography)

Key Relationships:

Book written by Author (many-to-one or many-to-many)
Member borrows Books (many-to-many through Loans)
Loan links Member and Book with dates

Summary

In this lesson, you learned:

Database design is critical for application success and performance
Good design prevents data duplication and ensures integrity
Design follows a lifecycle: requirements → conceptual → logical → physical
Requirements gathering identifies entities, relationships, and business rules
Avoid common mistakes like storing multiple values in one column
Design principles: integrity, consistency, simplicity, flexibility

Next Up: In the next lesson, we'll learn how to create Entity-Relationship Diagrams (ERDs) to visualize database structure!