数据库设计教程 — 数据建模实战

编程教程 管理员 2026年06月23日 148 次阅读 1639 字 约 6 分钟
适用人群：后端开发者、全栈开发者
学习时长：约1-2天
数据库：MySQL/PostgreSQL
重要程度：★★★★☆（后端核心技能）

一、数据库设计原则

原则说明
范式化减少数据冗余（1NF/2NF/3NF）
适当反范式化为性能适当冗余
命名规范表名小写下划线，字段名小写下划线
主键设计自增ID或UUID
索引设计为常用查询字段建索引

二、命名规范

-- 表名：小写下划线，复数形式
users
user_profiles
order_items

-- 字段名：小写下划线
user_name
created_at
is_deleted

-- 主键：id
id

-- 外键：表名单数_id
user_id
category_id

-- 时间字段
created_at
updated_at
deleted_at（软删除）

-- 布尔字段
is_active
is_deleted
has_paid

三、常用表结构

3.1 用户表

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    phone VARCHAR(20),
    role ENUM('admin', 'editor', 'user') DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    email_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.2 文章表

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    content TEXT NOT NULL,
    excerpt VARCHAR(500),
    cover_image VARCHAR(255),
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    views INT UNSIGNED DEFAULT 0,
    likes INT UNSIGNED DEFAULT 0,
    author_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_author (author_id),
    INDEX idx_category (category_id),
    INDEX idx_published_at (published_at),
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.3 订单表

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    payable_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    payment_method VARCHAR(20),
    payment_no VARCHAR(64),
    address_snapshot JSON NOT NULL,
    remark VARCHAR(500),
    paid_at TIMESTAMP NULL,
    shipped_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_order_no (order_no),
    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;

3.4 订单商品表

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    product_image VARCHAR(255),
    sku_id BIGINT UNSIGNED,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.5 商品表

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    original_price DECIMAL(10, 2),
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    sales INT UNSIGNED DEFAULT 0,
    category_id BIGINT UNSIGNED,
    brand_id BIGINT UNSIGNED,
    cover_image VARCHAR(255),
    images JSON,
    specs JSON,
    status ENUM('on_sale', 'off_sale', 'sold_out') DEFAULT 'on_sale',
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_slug (slug),
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_status (status),
    INDEX idx_price (price),
    INDEX idx_sales (sales),
    FULLTEXT INDEX ft_name_desc (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.6 分类表

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    icon VARCHAR(255),
    description VARCHAR(200),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_parent (parent_id),
    INDEX idx_slug (slug),
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.7 标签表

CREATE TABLE tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 文章标签关联表（多对多）
CREATE TABLE post_tags (
    post_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.8 评论表

CREATE TABLE comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED,
    parent_id BIGINT UNSIGNED,
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    INDEX idx_post_status (post_id, status),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

四、索引设计原则

-- 1. 为WHERE条件字段建索引
SELECT * FROM users WHERE email = 'test@example.com';
-- 索引：INDEX idx_email (email)

-- 2. 为JOIN字段建索引
SELECT * FROM posts p JOIN users u ON p.author_id = u.id;
-- 索引：INDEX idx_author (author_id)

-- 3. 为ORDER BY字段建索引
SELECT * FROM posts ORDER BY published_at DESC;
-- 索引：INDEX idx_published_at (published_at)

-- 4. 联合索引遵循最左前缀
SELECT * FROM posts WHERE status = 'published' AND category_id = 1;
-- 索引：INDEX idx_status_category (status, category_id)

-- 5. 避免过度索引
-- 每个索引都会占用存储空间，影响写入性能

五、常见设计模式

5.1 软删除

-- 使用deleted_at字段实现软删除
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- 查询时过滤已删除数据
SELECT * FROM users WHERE deleted_at IS NULL;

-- 删除操作
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- 恢复
UPDATE users SET deleted_at = NULL WHERE id = 1;

5.2 树形结构

-- 方式1：邻接表（parent_id）
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT DEFAULT 0
);

-- 查询子分类
SELECT * FROM categories WHERE parent_id = 1;

-- 方式2：路径枚举
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    path VARCHAR(255)  -- 如：/1/2/3/
);

-- 查询所有子分类
SELECT * FROM categories WHERE path LIKE '/1/%';

5.3 多态关联

-- 评论可以属于文章或商品
CREATE TABLE comments (
    id INT PRIMARY KEY,
    commentable_type VARCHAR(50),  -- 'post' 或 'product'
    commentable_id INT,
    content TEXT,
    INDEX idx_commentable (commentable_type, commentable_id)
);

-- 查询文章评论
SELECT * FROM comments 
WHERE commentable_type = 'post' AND commentable_id = 123;

六、性能优化

-- 1. 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM posts WHERE status = 'published';

-- 2. 避免SELECT *
SELECT id, title, slug FROM posts WHERE status = 'published';

-- 3. 分页优化
-- 慢：OFFSET大的时候
SELECT * FROM posts LIMIT 10 OFFSET 10000;
-- 快：使用游标分页
SELECT * FROM posts WHERE id > 10000 LIMIT 10;

-- 4. 批量插入
INSERT INTO users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

-- 5. 使用覆盖索引
-- 索引包含查询的所有字段，不需要回表
SELECT username, email FROM users WHERE username = '张三';

学习建议

  1. 先理解范式化，掌握1NF/2NF/3NF
  2. 学会命名规范，代码整洁的基础
  3. 掌握索引设计，性能优化的关键
  4. 理解常见设计模式，软删除、树形结构、多态关联
  5. 实际项目中练习，从简单表结构开始

下一步学习

返回首页