MySQL Practical Guide: Normal Forms, Table Relationships, and Design Tips

Database Design Principles

Good database design reduces data redundancy, ensures data consistency and integrity, improves query performance, and simplifies system maintenance and expansion.

Design process: Requirement analysis → Conceptual design → Logical design → Physical design → Implementation & maintenance.

Three Major Normal Forms

1.1 First Normal Form (1NF) – Atomicity

Requirement: each field must hold an indivisible atomic value.

-- Wrong design
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    contact_info VARCHAR(100) -- contains phone and email
);

-- Correct design
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(50)
);

2.2 Second Normal Form (2NF) – Eliminate Partial Dependency

Requirement: on the basis of 1NF, remove non‑key attributes that depend only on part of a composite primary key.

-- Wrong design (orders table)
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    product_name VARCHAR(50),
    customer_name VARCHAR(50),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);
-- Issue: product_name depends only on product_id

-- Correct design
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

2.3 Third Normal Form (3NF) – Eliminate Transitive Dependency

Requirement: on the basis of 2NF, remove transitive dependencies between non‑key attributes.

-- Wrong design
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50),
    dept_location VARCHAR(100)
);
-- Issue: dept_name and dept_location depend on dept_id, not directly on emp_id

-- Correct design
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    dept_location VARCHAR(100)
);

Primary Key, Foreign Key, Index

3.1 Primary Key

-- Single‑field primary key
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

3.2 Foreign Key

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE   -- cascade delete
        ON UPDATE CASCADE   -- cascade update
);
-- Other foreign‑key options:
-- ON DELETE RESTRICT (default, prevents delete)
-- ON DELETE SET NULL (sets to NULL)
-- ON DELETE NO ACTION (no operation)

3.3 Index

-- Ordinary index
CREATE INDEX idx_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Composite index
CREATE INDEX idx_name_dept ON employees(last_name, department_id);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_email ON users;

Table Relationship Design

4.1 One‑to‑One Relationship

Scenario: user and user profile (extended info)

-- Option 1: shared primary key
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    real_name VARCHAR(50),
    birthdate DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Option 2: unique foreign key
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);
CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE, -- ensures one‑to‑one
    real_name VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

4.2 One‑to‑Many Relationship

Scenario: department and employees, user and orders

-- Department table (one side)
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

-- Employee table (many side)
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

4.3 Many‑to‑Many Relationship

Scenario: students and courses, users and roles

-- Student table
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL
);

-- Course table
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credit INT
);

-- Junction table
CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade DECIMAL(4,2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE KEY unique_enrollment (student_id, course_id) -- prevent duplicate enrollment
);

ER Diagram Basics

Rectangle : entity (table)

Diamond : relationship

Ellipse : attribute

Line : connects entities and relationships

Online Store System Case Study

5.2.1 Requirement Analysis

User management

Product management

Order management

Category management

Shopping cart functionality

Database Design

-- 1. users table
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
);

-- 2. categories table
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    parent_id INT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

-- 3. products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    description TEXT,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT INDEX idx_search (product_name, description)
);

-- 4. orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',
    payment_method VARCHAR(20),
    shipping_address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- 5. order_items table
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) AS (quantity * unit_price), -- generated column
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order (order_id)
);

-- 6. shopping_carts table
CREATE TABLE shopping_carts (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 7. cart_items table
CREATE TABLE cart_items (
    cart_item_id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES shopping_carts(cart_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    UNIQUE KEY unique_cart_product (cart_id, product_id)
);

-- 8. user_addresses table (one‑to‑many example)
CREATE TABLE user_addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    recipient_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail_address VARCHAR(200),
    is_default BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
);

Complex Query Examples

-- 1. User order details
SELECT u.username, o.order_number, o.total_amount, o.status, o.created_at,
       oi.product_id, p.product_name, oi.quantity, oi.unit_price, oi.subtotal
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE u.user_id = 1
ORDER BY o.created_at DESC;

-- 2. Top‑selling products (sales top 10)
SELECT p.product_id, p.product_name,
       SUM(oi.quantity) AS total_sold,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status IN ('shipped','delivered')
GROUP BY p.product_id, p.product_name
ORDER BY total_sold DESC
LIMIT 10;

-- 3. User shopping cart contents
SELECT u.username, p.product_name, ci.quantity, p.price,
       (ci.quantity * p.price) AS item_total
FROM users u
JOIN shopping_carts sc ON u.user_id = sc.user_id
JOIN cart_items ci ON sc.cart_id = ci.cart_id
JOIN products p ON ci.product_id = p.product_id
WHERE u.user_id = 1;

Design Recommendations

Naming Conventions

Table names: lowercase plural (users, orders)

Column names: lowercase snake_case (user_id, created_at)

Primary key: id or table_id

Field Selection

Appropriate data types (INT, VARCHAR, DECIMAL, TIMESTAMP, etc.)

Set NOT NULL constraints where applicable

Use ENUM instead of string literals for fixed sets

Index Optimization

Primary keys automatically create indexes

Foreign keys should have indexes

Create indexes on frequently queried columns

Avoid excessive indexing

Performance Considerations

-- Analyze query with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Periodically optimize tables
OPTIMIZE TABLE users;

-- Analyze table statistics
ANALYZE TABLE users;