数据库详细设计与实战

一、数据库设计流程

1.1 设计原则

从页面到数据库的设计思路：

code

数据库设计流程：
│
├── 1⃣ 分析页面需求
│   ├── 识别页面元素
│   ├── 确定数据字段
│   └── 明确字段类型
│
├── 2⃣ 创建数据表
│   ├── 定义表名
│   ├── 设计字段
│   └── 设置约束
│
├── 3⃣ 建立关联关系
│   ├── 确定关联类型
│   ├── 添加外键
│   └── 创建关联表
│
└── 4⃣ 优化调整
    ├── 考虑性能
    ├── 考虑扩展性
    └── 评审确认

1.2 设计工具

推荐工具：

ShowDoc：在线文档工具，支持数据字典
dbdiagram.io：ER 图设计工具
Excel：简单的表格设计
Typora：本地文档工具

二、用户表设计（users）

2.1 需求分析

页面需求：

用户昵称显示
用户类型标识（普通用户、会员、高级会员）
会员过期时间
用户状态（是否禁用）
联系方式（手机号、邮箱）
第三方登录（微信 unionId、openId）

2.2 表结构设计

用户表（users）：

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
nickname	string	否	-	用户昵称
type	number	否	0	用户类型：0-普通用户，1-会员，2-高级会员
expire	datetime	是	null	会员过期时间（null 表示不过期）
status	number	否	0	用户状态：0-正常，1-禁用
phone	number	是	null	手机号
email	string	是	null	邮箱
unionId	string	是	null	微信开放平台 unionId
openId	string	是	null	微信 openId
created_at	datetime	否	当前时间	创建时间
updated_at	datetime	否	当前时间	更新时间

2.3 完整建表语句

sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  nickname VARCHAR(50) NOT NULL COMMENT '用户昵称',
  type TINYINT NOT NULL DEFAULT 0 COMMENT '用户类型：0-普通用户，1-会员，2-高级会员',
  expire DATETIME DEFAULT NULL COMMENT '会员过期时间',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '用户状态：0-正常，1-禁用',
  phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  union_id VARCHAR(100) DEFAULT NULL COMMENT '微信开放平台 unionId',
  open_id VARCHAR(100) DEFAULT NULL COMMENT '微信 openId',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  INDEX idx_phone (phone),
  INDEX idx_email (email),
  INDEX idx_union_id (union_id),
  INDEX idx_open_id (open_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2.4 字段说明

type 字段值说明：

code

用户类型（type）：
│
├── 0：普通用户
│   └── 无会员权益
│
├── 1：会员
│   ├── 需要设置 expire 过期时间
│   └── 享受会员权益
│
└── 2：高级会员
    ├── 需要设置 expire 过期时间
    └── 享受高级会员权益

expire 字段逻辑：

code

过期时间（expire）逻辑：
│
├── null：永不过期
│   └── 适用于普通用户
│
└── 具体日期
    ├── 检查是否过期
    └── 过期后降级为普通用户

status 字段说明：

code

用户状态（status）：
│
├── 0：正常
│   └── 可以正常登录
│
└── 1：禁用
    └── 无法登录，需联系管理员

三、首页资源表设计（home_resources）

3.1 需求分析

页面需求：

首页头图展示
项目列表展示
不同页面（首页、学习页）的资源区分
不同类型（头像、图片、项目）的资源区分

3.2 表结构设计

首页资源表（home_resources）：

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
title	string	是	null	标题
subtitle	string	是	null	副标题
url	string	是	null	链接地址
image	string	是	null	图片地址
desc	string	是	null	描述信息
module	string	否	'home'	所属模块：home-首页，study-学习页
type	string	否	'image'	资源类型：banner-头图，image-图片，project-项目
icon	string	是	null	图标
sort_order	number	否	0	排序
status	number	否	1	状态：0-禁用，1-启用
created_at	datetime	否	当前时间	创建时间

3.3 完整建表语句

sql

CREATE TABLE home_resources (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '资源ID',
  title VARCHAR(200) DEFAULT NULL COMMENT '标题',
  subtitle VARCHAR(200) DEFAULT NULL COMMENT '副标题',
  url VARCHAR(500) DEFAULT NULL COMMENT '链接地址',
  image VARCHAR(500) DEFAULT NULL COMMENT '图片地址',
  `desc` TEXT DEFAULT NULL COMMENT '描述信息',
  module VARCHAR(50) NOT NULL DEFAULT 'home' COMMENT '所属模块：home-首页，study-学习页',
  type VARCHAR(50) NOT NULL DEFAULT 'image' COMMENT '资源类型：banner-头图，image-图片，project-项目',
  icon VARCHAR(200) DEFAULT NULL COMMENT '图标',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  INDEX idx_module (module),
  INDEX idx_type (type),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='首页资源表';

3.4 字段说明

module 字段值说明：

code

所属模块（module）：
│
├── home：首页/产品页
│   └── 首页展示的资源
│
└── study：学习页
    └── 学习页展示的资源

type 字段值说明：

code

资源类型（type）：
│
├── banner：头图
│   └── 页面顶部的轮播图
│
├── image：图片
│   └── 普通图片资源
│
└── project：项目
    └── 项目展示资源

3.5 查询示例

查询首页头图：

sql

SELECT 
  id, title, subtitle, url, image
FROM home_resources
WHERE module = 'home' 
  AND type = 'banner'
  AND status = 1
ORDER BY sort_order ASC;

查询首页项目列表：

sql

SELECT 
  id, title, subtitle, url, image, icon
FROM home_resources
WHERE module = 'home' 
  AND type = 'project'
  AND status = 1
ORDER BY sort_order ASC;

4.1 需求分析

页面需求：

课程列表展示
课程详情展示
多种分类（推荐、每日一课、精品微课、体系课、学习计划、专栏）
价格信息（原价、活动价）
课程状态（上架、下架）
排序和统计

4.2 表结构设计

课程信息表（courses）：

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
title	string	否	-	课程标题
description	string	是	null	课程描述
cover_image	string	是	null	封面图片
author_id	number	是	null	作者ID（关联 users 表）
original_price	decimal	否	0	原价
activity_price	decimal	是	null	活动价
is_published	number	否	0	是否上架：0-未上架，1-已上架
study_count	number	否	0	学习人数
sort_order	number	否	0	排序
detail	string	是	null	详情页 markdown 文件名
type	string	否	-	课程类型：recommend-推荐，daily-每日一课，boutique-精品微课，system-体系课，plan-学习计划，column-专栏
created_at	datetime	否	当前时间	创建时间
updated_at	datetime	否	当前时间	更新时间

4.3 完整建表语句

sql

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
  title VARCHAR(200) NOT NULL COMMENT '课程标题',
  description TEXT DEFAULT NULL COMMENT '课程描述',
  cover_image VARCHAR(500) DEFAULT NULL COMMENT '封面图片',
  author_id INT DEFAULT NULL COMMENT '作者ID',
  original_price DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '原价',
  activity_price DECIMAL(10, 2) DEFAULT NULL COMMENT '活动价',
  is_published TINYINT NOT NULL DEFAULT 0 COMMENT '是否上架：0-未上架，1-已上架',
  study_count INT NOT NULL DEFAULT 0 COMMENT '学习人数',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  detail VARCHAR(200) DEFAULT NULL COMMENT '详情页 markdown 文件名',
  type VARCHAR(50) NOT NULL COMMENT '课程类型',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  FOREIGN KEY (author_id) REFERENCES users(id),
  INDEX idx_author (author_id),
  INDEX idx_type (type),
  INDEX idx_published (is_published),
  INDEX idx_study_count (study_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表';

4.4 字段说明

type 字段值说明：

code

课程类型（type）：
│
├── recommend：推荐
│   └── 首页推荐课程
│
├── daily：每日一课
│   └── 每日更新的课程
│
├── boutique：精品微课
│   └── 精品短课程
│
├── system：体系课
│   └── 系统性课程
│
├── plan：学习计划
│   └── 学习计划课程
│
└── column：专栏
    └── 专栏课程

价格逻辑：

code

价格字段逻辑：
│
├── activity_price 不为 null
│   └── 显示活动价，划掉原价
│
└── activity_price 为 null
    └── 只显示原价

is_published 字段说明：

code

上架状态（is_published）：
│
├── 0：未上架
│   └── 创建后默认状态，方便编辑
│
└── 1：已上架
    └── 对用户可见

4.5 查询示例

查询推荐课程：

sql

SELECT 
  id, title, cover_image, original_price, activity_price, study_count
FROM courses
WHERE type = 'recommend'
  AND is_published = 1
ORDER BY sort_order ASC
LIMIT 10;

查询课程详情：

sql

SELECT 
  c.id, c.title, c.description, c.cover_image,
  u.nickname as author_name,
  c.original_price, c.activity_price, c.study_count, c.detail
FROM courses c
LEFT JOIN users u ON c.author_id = u.id
WHERE c.id = ? AND c.is_published = 1;

五、课程章节表设计（course_contents）

5.1 需求分析

页面需求：

课程章节列表
章节类型（标题、图文、视频、链接）
章节权限控制
章节排序

5.2 表结构设计

课程章节表（course_contents）：

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
course_id	number	否	-	课程ID（关联 courses 表）
title	string	否	-	章节标题
type	string	是	null	章节类型：null-标题，text-图文，video-视频，link-链接
content	string	是	null	章节内容/链接地址
sort_order	number	否	0	排序
parent_id	number	是	null	上级章节ID（用于层级结构）
status	number	否	0	开放状态：0-未开放，1-已开放
author_id	number	是	null	作者ID（关联 users 表）
created_at	datetime	否	当前时间	创建时间

5.3 完整建表语句

sql

CREATE TABLE course_contents (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '章节ID',
  course_id INT NOT NULL COMMENT '课程ID',
  title VARCHAR(200) NOT NULL COMMENT '章节标题',
  type VARCHAR(50) DEFAULT NULL COMMENT '章节类型：null-标题，text-图文，video-视频，link-链接',
  content TEXT DEFAULT NULL COMMENT '章节内容/链接地址',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  parent_id INT DEFAULT NULL COMMENT '上级章节ID',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '开放状态：0-未开放，1-已开放',
  author_id INT DEFAULT NULL COMMENT '作者ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (parent_id) REFERENCES course_contents(id),
  FOREIGN KEY (author_id) REFERENCES users(id),
  INDEX idx_course (course_id),
  INDEX idx_parent (parent_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程章节表';

5.4 字段说明

type 字段值说明：

code

章节类型（type）：
│
├── null：标题
│   └── 章节目录标题
│
├── text：图文
│   └── 图文内容
│
├── video：视频
│   └── 视频内容，content 存放视频链接
│
└── link：链接
    └── 外部链接，content 存放链接地址

parent_id 字段说明：

code

层级结构（parent_id）：
│
├── null：顶级章节
│   └── 一级目录
│
└── 具体ID：子章节
    └── 二级目录/内容

status 字段说明：

code

开放状态（status）：
│
├── 0：未开放
│   └── 锁定状态，需要权限
│
└── 1：已开放
    └── 所有人可查看

5.5 查询示例

查询课程章节列表：

sql

SELECT 
  id, title, type, content, status, parent_id
FROM course_contents
WHERE course_id = ?
ORDER BY sort_order ASC;

构建章节树形结构：

sql

-- 一级章节
SELECT 
  id, title, type, status
FROM course_contents
WHERE course_id = ? AND parent_id IS NULL
ORDER BY sort_order ASC;

-- 二级章节
SELECT 
  id, title, type, content, status
FROM course_contents
WHERE parent_id = ?
ORDER BY sort_order ASC;

六、字典表设计

6.1 标签字典表（dict_tags）

用途：存储课程标签信息

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
name	string	否	-	标签名称
category	string	是	null	所属分类（前端、后端、服务端等）
sort_order	number	否	0	排序
status	number	否	1	状态：0-禁用，1-启用

建表语句：

sql

CREATE TABLE dict_tags (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
  name VARCHAR(50) NOT NULL COMMENT '标签名称',
  category VARCHAR(50) DEFAULT NULL COMMENT '所属分类',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
  
  INDEX idx_category (category),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签字典表';

-- 插入示例数据
INSERT INTO dict_tags (name, category) VALUES
  ('Vue', '前端'),
  ('React', '前端'),
  ('Angular', '前端'),
  ('Node.js', '后端'),
  ('Python', '后端'),
  ('MySQL', '数据库'),
  ('MongoDB', '数据库');

6.2 课程分类字典表（dict_course_types）

用途：存储课程分类信息

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
name	string	否	-	分类名称
code	string	否	-	分类代码
sort_order	number	否	0	排序
status	number	否	1	状态：0-禁用，1-启用

建表语句：

sql

CREATE TABLE dict_course_types (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
  name VARCHAR(50) NOT NULL COMMENT '分类名称',
  code VARCHAR(50) NOT NULL COMMENT '分类代码',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
  
  UNIQUE KEY uk_code (code),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程分类字典表';

-- 插入示例数据
INSERT INTO dict_course_types (name, code) VALUES
  ('推荐', 'recommend'),
  ('每日一课', 'daily'),
  ('精品微课', 'boutique'),
  ('体系课', 'system'),
  ('学习计划', 'plan'),
  ('专栏', 'column');

七、标签关联表设计（course_tags）

7.1 表结构设计

课程标签关联表（course_tags）：

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
course_id	number	否	-	课程ID（关联 courses 表）
tag_id	number	否	-	标签ID（关联 dict_tags 表）
created_at	datetime	否	当前时间	创建时间

7.2 完整建表语句

sql

CREATE TABLE course_tags (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  course_id INT NOT NULL COMMENT '课程ID',
  tag_id INT NOT NULL COMMENT '标签ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (tag_id) REFERENCES dict_tags(id),
  UNIQUE KEY uk_course_tag (course_id, tag_id),
  INDEX idx_course (course_id),
  INDEX idx_tag (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程标签关联表';

7.3 查询示例

查询课程的所有标签：

sql

SELECT 
  t.id, t.name, t.category
FROM dict_tags t
INNER JOIN course_tags ct ON t.id = ct.tag_id
WHERE ct.course_id = ?;

查询某标签下的所有课程：

sql

SELECT 
  c.id, c.title, c.cover_image, c.study_count
FROM courses c
INNER JOIN course_tags ct ON c.id = ct.course_id
WHERE ct.tag_id = ? AND c.is_published = 1
ORDER BY c.study_count DESC;

八、评论表设计

8.1 评论信息表（comments）

用途：存储所有评论信息

字段名	类型	是否为空	默认值	说明
id	number	否	自增	主键
user_id	number	否	-	用户ID（关联 users 表）
content	text	否	-	评论内容
parent_id	number	是	null	父评论ID（用于回复）
like_count	number	否	0	点赞数
is_best	number	否	0	是否最佳评论：0-否，1-是
status	number	否	1	状态：0-隐藏，1-显示
created_at	datetime	否	当前时间	创建时间

建表语句：

sql

CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
  user_id INT NOT NULL COMMENT '用户ID',
  content TEXT NOT NULL COMMENT '评论内容',
  parent_id INT DEFAULT NULL COMMENT '父评论ID',
  like_count INT NOT NULL DEFAULT 0 COMMENT '点赞数',
  is_best TINYINT NOT NULL DEFAULT 0 COMMENT '是否最佳评论：0-否，1-是',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-隐藏，1-显示',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (parent_id) REFERENCES comments(id),
  INDEX idx_user (user_id),
  INDEX idx_parent (parent_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论信息表';

8.2 课程评论关联表（course_comments）

用途：关联课程和评论（多对多关系）

字段名	类型	是否为空	默认值	说明
course_id	number	否	-	课程ID
comment_id	number	否	-	评论ID
created_at	datetime	否	当前时间	创建时间

建表语句：

sql

CREATE TABLE course_comments (
  course_id INT NOT NULL COMMENT '课程ID',
  comment_id INT NOT NULL COMMENT '评论ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  PRIMARY KEY (course_id, comment_id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (comment_id) REFERENCES comments(id),
  INDEX idx_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程评论关联表';

8.3 章节评论关联表（content_comments）

用途：关联章节和评论（多对多关系）

字段名	类型	是否为空	默认值	说明
content_id	number	否	-	章节ID
comment_id	number	否	-	评论ID
created_at	datetime	否	当前时间	创建时间

建表语句：

sql

CREATE TABLE content_comments (
  content_id INT NOT NULL COMMENT '章节ID',
  comment_id INT NOT NULL COMMENT '评论ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  PRIMARY KEY (content_id, comment_id),
  FOREIGN KEY (content_id) REFERENCES course_contents(id),
  FOREIGN KEY (comment_id) REFERENCES comments(id),
  INDEX idx_content (content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='章节评论关联表';

8.4 查询示例

查询课程评论列表：

sql

SELECT 
  c.id, c.content, c.like_count, c.is_best, c.created_at,
  u.nickname, u.avatar
FROM comments c
INNER JOIN course_comments cc ON c.id = cc.comment_id
INNER JOIN users u ON c.user_id = u.id
WHERE cc.course_id = ? AND c.status = 1
ORDER BY c.is_best DESC, c.like_count DESC, c.created_at DESC;

查询章节评论/问答列表：

sql

SELECT 
  c.id, c.content, c.parent_id, c.like_count, c.created_at,
  u.nickname, u.avatar
FROM comments c
INNER JOIN content_comments cc ON c.id = cc.comment_id
INNER JOIN users u ON c.user_id = u.id
WHERE cc.content_id = ? AND c.status = 1
ORDER BY c.created_at DESC;

九、数据库表关系总结

9.1 完整 ER 图

code

┌─────────────┐                    ┌─────────────┐
│ home_       │                    │    users    │
│ resources   │                    ├─────────────┤
├─────────────┤                    │ id (PK)     │
│ id (PK)     │                    │ nickname    │
│ title       │                    │ type        │
│ module      │                    │ expire      │
│ type        │                    │ status      │
└─────────────┘                    └──────┬──────┘
                                          │
                  ┌───────────────────────┼───────────────────────┐
                  │                       │                       │
                  ▼ 1:N                   ▼ 1:N                   ▼ 1:N
         ┌────────────────┐      ┌────────────────┐      ┌────────────────┐
         │    courses     │      │    comments    │      │ course_        │
         ├────────────────┤      ├────────────────┤      │ contents       │
         │ id (PK)        │      │ id (PK)        │      ├────────────────┤
         │ title          │      │ user_id (FK)   │      │ id (PK)        │
         │ author_id (FK) │      │ content        │      │ course_id (FK) │
         │ type           │      │ like_count     │      │ title          │
         │ study_count    │      │ is_best        │      │ type           │
         └───────┬────────┘      └───────┬────────┘      │ status         │
                 │                       │               └────────┬───────┘
      ┌──────────┼──────────┐            │                        │
      │          │          │            │                        │
      ▼ M:N      ▼ 1:N      ▼ M:N        │                        │
┌──────────┐ ┌──────────┐ ┌──────────┐   │                        │
│ course_  │ │ course_  │ │ course_  │   │                        │
│ tags     │ │ contents │ │ comments │   │                        │
├──────────┤ ├──────────┤ ├──────────┤   │                        │
│ course_id│ │ id (PK)  │ │ course_id│◄──┘                        │
│ tag_id   │ │ course_id│ │ comment_id│                          │
└────┬─────┘ └──────────┘ └──────────┘                            │
     │                                                             │
     │ N:1                                                         │
     ▼                                                             │
┌──────────┐                                                      │
│ dict_    │                                                      │
│ tags     │                                                      │
├──────────┤                                                      │
│ id (PK)  │                                                      │
│ name     │                                                      │
│ category │                                                      │
└──────────┘                                                      │
                                                                  │
                 ┌────────────────────────────────────────────────┘
                 │
                 ▼ M:N
         ┌────────────────┐
         │ content_       │
         │ comments       │
         ├────────────────┤
         │ content_id (FK)│
         │ comment_id (FK)│
         └────────────────┘

9.2 关系说明

code

表关系总结：
│
├── users（用户）
│   ├── 1:N → courses（课程，作为作者）
│   ├── 1:N → comments（评论）
│   └── 1:N → course_contents（章节，作为作者）
│
├── courses（课程）
│   ├── 1:N → course_contents（章节）
│   ├── 1:N → course_tags（标签关联）
│   ├── M:N → comments（评论，通过 course_comments）
│   └── M:N → tags（标签，通过 course_tags）
│
├── course_contents（章节）
│   ├── N:1 → courses（所属课程）
│   ├── 1:N → course_contents（子章节）
│   └── M:N → comments（评论，通过 content_comments）
│
├── comments（评论）
│   ├── N:1 → users（评论者）
│   └── 1:N → comments（子评论/回复）
│
└── 独立实体
    ├── home_resources（首页资源）
    ├── dict_tags（标签字典）
    └── dict_course_types（课程分类字典）

十、数据库设计最佳实践

10.1 设计原则

code

数据库设计原则：
│
├── 1⃣ 字段命名规范
│   ├── 小写 + 下划线
│   ├── 见名知意
│   └── 统一风格
│
├── 2⃣ 字段类型选择
│   ├── number：数字类型
│   ├── string：字符串类型
│   ├── decimal：金额类型
│   ├── datetime：时间类型
│   └── text：长文本类型
│
├── 3⃣ 默认值设置
│   ├── 状态字段：默认 0
│   ├── 排序字段：默认 0
│   ├── 时间字段：自动填充
│   └── 可选字段：默认 null
│
├── 4⃣ 索引设计
│   ├── 主键自动创建索引
│   ├── 外键建议创建索引
│   ├── 常用查询字段创建索引
│   └── 组合索引遵循最左前缀
│
└── 5⃣ 关联关系
    ├── 一对一：外键 + UNIQUE
    ├── 一对多：外键在"多"方
    └── 多对多：中间表

10.2 字段设计技巧

1. 状态字段设计

sql

-- 状态字段设计
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-禁用，1-启用'
is_published TINYINT NOT NULL DEFAULT 0 COMMENT '是否上架：0-未上架，1-已上架'

-- 优点：
-- 1. 使用数字类型，节省存储空间
-- 2. 默认值为 0，表示初始状态
-- 3. 通过注释说明状态含义

2. 价格字段设计

sql

-- 价格字段设计
original_price DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '原价'
activity_price DECIMAL(10, 2) DEFAULT NULL COMMENT '活动价'

-- 优点：
-- 1. 使用 DECIMAL 确保精度
-- 2. activity_price 为 null 表示无活动价
-- 3. 前端根据 null 判断是否显示活动价

3. 时间字段设计

sql

-- 时间字段设计
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

-- 优点：
-- 1. 自动填充，无需手动维护
-- 2. created_at 创建时自动填充
-- 3. updated_at 更新时自动更新

4. 关联字段设计

sql

-- 关联字段设计
author_id INT DEFAULT NULL COMMENT '作者ID'
course_id INT NOT NULL COMMENT '课程ID'

-- 说明：
-- 1. 可选关联使用 DEFAULT NULL
-- 2. 必须关联使用 NOT NULL
-- 3. 通过 FOREIGN KEY 建立外键约束

10.3 性能优化建议

code

性能优化建议：
│
├── 1⃣ 统计字段冗余
│   ├── study_count（学习人数）
│   ├── like_count（点赞数）
│   └── 原因：避免频繁 COUNT 查询
│
├── 2⃣ 索引优化
│   ├── 外键创建索引
│   ├── 常用查询字段创建索引
│   └── 排序字段创建索引
│
├── 3⃣ 分页查询
│   ├── 使用 LIMIT 分页
│   ├── 避免大偏移量
│   └── 考虑使用游标分页
│
└── 4⃣ 缓存策略
    ├── Redis 缓存热门数据
    ├── 统计数据缓存
    └── 定时更新缓存

十一、常见问题与解决方案

问题	原因	解决方案
字段命名不规范	缺乏规范	制定命名规范文档
字段类型选择错误	经验不足	参考最佳实践
缺少默认值	设计疏忽	为所有字段设置默认值
外键未建索引	忘记创建	外键字段自动创建索引
缺少注释	文档不全	所有字段添加 COMMENT
关联关系复杂	设计不合理	简化关系，使用中间表
查询性能差	缺少索引	分析慢查询，添加索引
数据冗余	未遵循范式	检查并优化表结构

十二、学习要点总结

12.1 核心要点

设计流程：从页面需求出发，逐步设计表结构
用户表：包含基本信息、会员信息、第三方登录信息
首页资源表：通过 module 和 type 区分不同模块和类型
课程表：包含课程信息、价格、状态、统计等
章节表：支持层级结构、多种类型、权限控制
字典表：统一管理标签和分类信息
评论表：支持回复、点赞、最佳评论

12.2 记忆技巧

code

数据库设计记忆技巧：
│
├── 设计流程
│   └── 页面需求 → 表结构 → 字段设计 → 关联关系
│
├── 字段类型
│   └── number（数字）、string（字符串）、decimal（金额）、datetime（时间）
│
├── 状态字段
│   └── 默认 0，注释说明含义
│
├── 时间字段
│   └── 自动填充 created_at、updated_at
│
└── 关联关系
    └── 一对一（外键+UNIQUE）、一对多（外键）、多对多（中间表）

12.3 学习路径

code

学习路径规划：
│
├── 第一阶段：理解需求（1-2 天）
│   ├── 分析页面需求
│   ├── 识别数据字段
│   └── 确定表结构
│
├── 第二阶段：设计实践（1 周）
│   ├── 创建数据表
│   ├── 设计字段
│   └── 建立关联关系
│
└── 第三阶段：优化调整（持续）
    ├── 性能优化
    ├── 索引优化
    └── 根据业务调整

十三、延伸学习资源

13.1 官方文档

13.2 推荐阅读

《数据库系统概念》
《高性能 MySQL》
《SQL 反模式》

13.3 练习建议

基础练习：设计用户-角色-权限系统
进阶练习：设计博客系统数据库
实战练习：设计课程系统完整数据库
思考练习：如何优化复杂查询？

十四、知识图谱

code

数据库详细设计知识图谱：
│
├── 用户表（users）
│   ├── 基本信息
│   ├── 会员信息
│   └── 第三方登录
│
├── 首页资源表（home_resources）
│   ├── module（模块）
│   ├── type（类型）
│   └── 资源信息
│
├── 课程表（courses）
│   ├── 课程信息
│   ├── 价格信息
│   ├── 统计信息
│   └── 状态控制
│
├── 章节表（course_contents）
│   ├── 层级结构
│   ├── 类型区分
│   └── 权限控制
│
├── 字典表
│   ├── dict_tags（标签字典）
│   └── dict_course_types（课程分类字典）
│
├── 标签关联表（course_tags）
│   └── 课程 M:N 标签
│
├── 评论表
│   ├── comments（评论信息）
│   ├── course_comments（课程评论关联）
│   └── content_comments（章节评论关联）
│
└── 最佳实践
    ├── 命名规范
    ├── 字段设计
    ├── 索引优化
    └── 性能优化

笔记整理时间：2026-03-07
最后更新时间：2026-03-07