Chapter 4: Table Design

1. Data Types

Choosing the right data type is the foundation of good table design. It affects storage efficiency, query performance, and data integrity.

Integer Types

Floating Point & Decimal Types

String Types

Date & Time Types

Other Common Types

2. Constraints

Constraints enforce rules on table data to maintain accuracy and reliability. They are defined when creating or altering a table.

PRIMARY KEY

Uniquely identifies each row. Combines NOT NULL and UNIQUE. A table can have only one primary key.

-- Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

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

NOT NULL

Prevents a column from storing NULL values.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

UNIQUE

Ensures all values in a column (or combination of columns) are distinct.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL,
    UNIQUE KEY uk_username (username)
);

DEFAULT

Sets a default value when no value is provided during insertion.

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CHECK

Validates that values meet a specified condition. Supported in MySQL 8.0.16+.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    discount_pct DECIMAL(5,2) DEFAULT 0.00,
    CHECK (discount_pct >= 0 AND discount_pct <= 100)
);

FOREIGN KEY

Links two tables together by referencing a primary key or unique key in another table.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

3. Complete CREATE TABLE Example

Let's design a simple e-commerce database schema with three related tables: products, orders, and order items.

Products Table

CREATE TABLE products (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    category VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Orders Table

CREATE TABLE orders (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    shipping_address VARCHAR(500) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Order Items Table

CREATE TABLE order_items (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE KEY uk_order_product (order_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. ALTER TABLE

As your application evolves, you will often need to modify existing table structures. ALTER TABLE supports a wide range of changes.

Add Column

-- Add a single column
ALTER TABLE products ADD COLUMN brand VARCHAR(100) AFTER name;

-- Add multiple columns
ALTER TABLE users
    ADD COLUMN phone VARCHAR(20) AFTER email,
    ADD COLUMN avatar_url VARCHAR(500) AFTER phone;

Modify Column

-- Change column type and constraints
ALTER TABLE products MODIFY COLUMN description MEDIUMTEXT;

-- Rename and change a column
ALTER TABLE users CHANGE COLUMN phone phone_number VARCHAR(30) NOT NULL DEFAULT '';

Drop Column

ALTER TABLE users DROP COLUMN avatar_url;

Add Index

-- Regular index
ALTER TABLE products ADD INDEX idx_brand (brand);

-- Composite index
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- Unique index
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone_number);

-- Full-text index (for text search)
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

Add Foreign Key

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

Rename Table

-- Rename a single table
ALTER TABLE products RENAME TO goods;

-- Or use RENAME TABLE (can rename multiple)
RENAME TABLE goods TO products, order_items TO line_items;

5. Design Best Practices

Naming Conventions

• Use snake_case for table and column names: user_profiles, created_at
• Use plural nouns for table names: users, products, order_items
• Prefix index names: idx_ for regular, uk_ for unique, fk_ for foreign keys
• Avoid reserved words as identifiers (e.g., order, group, select)

Always Use a Primary Key

• Every table should have a primary key — prefer INT UNSIGNED AUTO_INCREMENT or BIGINT UNSIGNED AUTO_INCREMENT
• Surrogate keys (auto-increment IDs) are simpler and more efficient than natural keys for most cases
• Consider UUID if you need globally unique IDs across distributed systems, but be aware of index fragmentation

Choose Proper Data Types

• Use the smallest type that fits your data — TINYINT for status codes, not INT
• Use DECIMAL for money — never FLOAT or DOUBLE
• Use TIMESTAMP for event times (timezone-aware), DATETIME for calendar dates
• Set VARCHAR lengths thoughtfully — VARCHAR(50) for names, VARCHAR(255) for URLs

Normalization

Database normalization reduces redundancy and improves data integrity. For most applications, aim for Third Normal Form (3NF):

6. Chapter Summary