Database Design

Good database design is key to system success. This chapter introduces database design principles and best practices.

Design Principles

1. Normalization

First Normal Form (1NF): Atomic values

-- Not 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    phones VARCHAR(200)  -- '13800138000,13900139000'
);

-- 1NF compliant
CREATE TABLE users (
    id INT,
    name VARCHAR(50)
);
CREATE TABLE user_phones (
    user_id INT,
    phone VARCHAR(20)
);

Second Normal Form (2NF): Eliminate partial dependencies Third Normal Form (3NF): Eliminate transitive dependencies

2. Naming Conventions

-- Table names: plural, lowercase, underscores
users, orders, order_items

-- Column names: lowercase, underscores
user_id, created_at, total_amount

-- Index names: idx_table_column
idx_users_email, idx_orders_user_id

3. Data Type Selection

-- Use appropriate data types
id INT UNSIGNED AUTO_INCREMENT,
price DECIMAL(10,2),  -- Not FLOAT
status ENUM('active', 'inactive'),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ER Diagram

Entity Relationships

User (1) ←→ (N) Order (1) ←→ (N) Order Item (N) ←→ (1) Product

Example: E-commerce System

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped', 'delivered'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Best Practices

1. Use Primary Keys: Every table should have a primary key
2. Foreign Key Constraints: Ensure referential integrity
3. Index Optimization: Create indexes for frequently queried columns
4. Timestamps: Add created_at and updated_at
5. Soft Delete: Use deleted_at instead of actual deletion
6. Version Control: Use version field for optimistic locking

Performance Considerations

1. Follow normalization principles
2. Appropriate denormalization
3. Table partitioning
4. Read-write separation
5. Caching strategy

Summary

• Follow normalization principles
• Use appropriate data types
• Establish correct relationships
• Add necessary constraints and indexes
• Consider performance and scalability

Next Step: Learn PRACTICE Projects