Kenhuang Academy
  • English

      • 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