Database Design Best Practices

Introduction

Proper database design is fundamental to building efficient, scalable, and maintainable applications. This guide covers essential principles and best practices for MySQL database design.

Normalization

What is Normalization?

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

Normal Forms

First Normal Form (1NF)

Each table cell contains a single value
Each record is unique
No repeating groups

Bad (Not 1NF):

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    products VARCHAR(500)  -- "Apple, Banana, Orange"
);

Good (1NF):

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Second Normal Form (2NF)

Must be in 1NF
All non-key attributes depend on the entire primary key

Bad (Not 2NF):

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Depends only on product_id
    product_price DECIMAL(10,2),  -- Depends only on product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Good (2NF):

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Third Normal Form (3NF)

Must be in 2NF
No transitive dependencies (non-key attributes don’t depend on other non-key attributes)

Bad (Not 3NF):

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),  -- Depends on department_id
    department_location VARCHAR(100)  -- Depends on department_id
);

Good (3NF):

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    department_location VARCHAR(100)
);

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

Naming Conventions

Tables

Use lowercase with underscores
Use plural nouns: users, products, orders
Be descriptive: customer_orders not co

Columns

Use lowercase with underscores
Use singular nouns
Be descriptive: email_address not ea
Use consistent prefixes for related columns

Keys

Primary keys: table_name_id (e.g., user_id, product_id)
Foreign keys: match referenced column name
Indexes: idx_column_name or idx_table_column

Examples

-- Good naming
CREATE TABLE user_profiles (
    user_profile_id INT PRIMARY KEY,
    user_id INT,
    bio TEXT,
    avatar_url VARCHAR(255),
    INDEX idx_user_id (user_id)
);

-- Poor naming
CREATE TABLE up (
    id INT PRIMARY KEY,
    uid INT,
    b TEXT,
    img VARCHAR(255)
);

Data Types

Choose Appropriate Types

-- Good: Right-sized types
age TINYINT UNSIGNED  -- 0-255
price DECIMAL(10, 2)  -- Exact for money
created_at TIMESTAMP  -- Auto-updated

-- Bad: Oversized types
age INT  -- Wastes space
price FLOAT  -- Precision issues
created_at VARCHAR(50)  -- Inefficient

Use UNSIGNED for Non-Negative

-- Use UNSIGNED for IDs, counts, quantities
user_id INT UNSIGNED
quantity SMALLINT UNSIGNED
view_count BIGINT UNSIGNED

Use ENUM for Fixed Sets

-- For small fixed sets
status ENUM('active', 'inactive', 'suspended')
priority ENUM('low', 'medium', 'high')

Indexes

When to Index

Always index:

Primary keys (automatic)
Foreign keys
Columns in WHERE clauses
Columns in JOIN conditions
Columns in ORDER BY

Consider indexing:

Columns in GROUP BY
Columns in DISTINCT queries
Columns with high selectivity

Index Types

Single Column Index:

CREATE INDEX idx_email ON users(email);

Composite Index:

CREATE INDEX idx_name ON users(last_name, first_name);

Unique Index:

CREATE UNIQUE INDEX idx_username ON users(username);

Full-Text Index:

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

Index Best Practices

-- Good: Index foreign keys
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    INDEX idx_user_id (user_id),  -- Index foreign key
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Good: Composite index for common queries
-- If you often query: WHERE country = 'USA' AND city = 'New York'
CREATE INDEX idx_location ON users(country, city);

-- Bad: Too many indexes
-- Don't index every column - impacts INSERT/UPDATE performance

Relationships

One-to-One

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

One-to-Many

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Many-to-Many

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrolled_at TIMESTAMP,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Constraints

Primary Keys

-- Use AUTO_INCREMENT for simple IDs
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- Use composite keys when appropriate
PRIMARY KEY (order_id, product_id)

-- Use UUID for distributed systems
user_id CHAR(36) PRIMARY KEY  -- UUID

Foreign Keys

-- Define referential actions
FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE  -- Delete related records
    ON UPDATE CASCADE  -- Update related records

-- Or
ON DELETE SET NULL  -- Set to NULL instead of deleting
ON DELETE RESTRICT  -- Prevent deletion if referenced

Other Constraints

-- NOT NULL
email VARCHAR(255) NOT NULL

-- UNIQUE
email VARCHAR(255) UNIQUE

-- DEFAULT
status VARCHAR(20) DEFAULT 'active'

-- CHECK (MySQL 8.0+)
age INT CHECK (age >= 18)

Performance Considerations

Avoid SELECT *

-- Bad
SELECT * FROM users WHERE user_id = 1;

-- Good
SELECT user_id, username, email FROM users WHERE user_id = 1;

Use LIMIT

-- Always use LIMIT for large result sets
SELECT * FROM products ORDER BY created_at DESC LIMIT 100;

Optimize JOINs

-- Index columns used in JOINs
CREATE INDEX idx_author_id ON posts(author_id);

-- Use explicit JOIN syntax
-- Good
SELECT * FROM posts p
JOIN authors a ON p.author_id = a.author_id;

-- Avoid (implicit join)
SELECT * FROM posts p, authors a
WHERE p.author_id = a.author_id;

Use EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Analyze the execution plan

Common Patterns

Soft Deletes

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    deleted_at TIMESTAMP NULL,
    INDEX idx_deleted (deleted_at)
);

-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;

Timestamps

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Versioning

CREATE TABLE documents (
    document_id INT PRIMARY KEY,
    title VARCHAR(200),
    version INT DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Audit Trails

CREATE TABLE audit_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50),
    record_id INT,
    action ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_values JSON,
    new_values JSON,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Anti-Patterns to Avoid

1. No Primary Key

-- Bad
CREATE TABLE logs (
    message TEXT,
    created_at TIMESTAMP
);

-- Good
CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP
);

2. Generic Column Names

-- Bad
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    value VARCHAR(255)
);

-- Good
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255)
);

3. Multi-Purpose Columns

-- Bad
CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    contact_info VARCHAR(255)  -- Could be email OR phone
);

-- Good
CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    email VARCHAR(255),
    phone VARCHAR(20)
);

4. Large VARCHAR When Not Needed

-- Bad
age VARCHAR(255)
status VARCHAR(1000)

-- Good
age TINYINT UNSIGNED
status ENUM('active', 'inactive')

Documentation

Comment Tables and Columns

CREATE TABLE users (
    user_id INT PRIMARY KEY COMMENT 'Unique user identifier',
    email VARCHAR(255) COMMENT 'User email address',
    status ENUM('active', 'inactive') COMMENT 'Account status'
) COMMENT='User accounts table';