4个常见系统的数据库表设计案例，包括电商系统、内容管理系统(CMS)、项目管理系统和论坛系统

常见系统数据库表设计案例

下面为您提供四个常见系统的数据库表设计案例，包括电商系统、内容管理系统(CMS)、项目管理系统和论坛系统。

一、电商系统数据库表设计

1. 用户模块

-- 用户登录表
CREATE TABLE customer_login (
  customer_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '用户ID',
  login_name VARCHAR(20) NOT NULL COMMENT '用户登录名',
  password CHAR(32) NOT NULL COMMENT 'md5加密的密码',
  user_stats TINYINT NOT NULL DEFAULT 1 COMMENT '用户状态(0:禁用,1:启用)',
  modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY pk_customerid(customer_id)
) ENGINE = innodb COMMENT '用户登录表';

-- 用户信息表
CREATE TABLE customer_inf (
  customer_inf_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '自增主键ID',
  customer_id INT UNSIGNED NOT NULL COMMENT 'customer_login表的自增ID',
  customer_name VARCHAR(20) NOT NULL COMMENT '用户真实姓名',
  identity_card_type TINYINT NOT NULL DEFAULT 1 COMMENT '证件类型：1 身份证，2 军官证，3 护照',
  identity_card_no VARCHAR(20) COMMENT '证件号码',
  mobile_phone VARCHAR(20) COMMENT '手机号',
  email VARCHAR(100) COMMENT '邮箱',
  gender TINYINT DEFAULT 0 COMMENT '性别(0:未知,1:男,2:女)',
  birthday DATE COMMENT '出生日期',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (customer_id) REFERENCES customer_login(customer_id),
  PRIMARY KEY pk_customer_inf_id(customer_inf_id)
) ENGINE = innodb COMMENT '用户信息表';

-- 收货地址表
CREATE TABLE delivery_address (
  address_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '地址ID',
  customer_id INT UNSIGNED NOT NULL COMMENT '用户ID',
  consignee VARCHAR(20) NOT NULL COMMENT '收货人',
  mobile VARCHAR(20) NOT NULL COMMENT '手机号',
  province VARCHAR(20) NOT NULL COMMENT '省',
  city VARCHAR(20) NOT NULL COMMENT '市',
  district VARCHAR(20) NOT NULL COMMENT '区',
  detail_address VARCHAR(200) NOT NULL COMMENT '详细地址',
  is_default TINYINT DEFAULT 0 COMMENT '是否默认地址(0:否,1:是)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (customer_id) REFERENCES customer_login(customer_id),
  PRIMARY KEY pk_address_id(address_id)
) ENGINE = innodb COMMENT '收货地址表';

2. 商品模块

-- 商品分类表
CREATE TABLE category (
  category_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '分类ID',
  category_name VARCHAR(50) NOT NULL COMMENT '分类名称',
  parent_id INT UNSIGNED DEFAULT 0 COMMENT '父分类ID',
  sort_order INT DEFAULT 0 COMMENT '排序',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY pk_category_id(category_id)
) ENGINE = innodb COMMENT '商品分类表';

-- 商品表
CREATE TABLE product (
  product_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '商品ID',
  product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
  category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
  brand_id INT UNSIGNED COMMENT '品牌ID',
  price DECIMAL(10,2) NOT NULL COMMENT '价格',
  original_price DECIMAL(10,2) COMMENT '原价',
  stock INT UNSIGNED NOT NULL COMMENT '库存',
  sales INT UNSIGNED DEFAULT 0 COMMENT '销量',
  main_image VARCHAR(255) COMMENT '主图URL',
  sub_images TEXT COMMENT '子图URL，用逗号分隔',
  description TEXT COMMENT '商品描述',
  status TINYINT DEFAULT 1 COMMENT '状态(0:下架,1:上架)',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (category_id) REFERENCES category(category_id),
  PRIMARY KEY pk_product_id(product_id)
) ENGINE = innodb COMMENT '商品表';

-- 商品属性表
CREATE TABLE product_attribute (
  attr_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '属性ID',
  category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
  attr_name VARCHAR(50) NOT NULL COMMENT '属性名称',
  sort_order INT DEFAULT 0 COMMENT '排序',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (category_id) REFERENCES category(category_id),
  PRIMARY KEY pk_attr_id(attr_id)
) ENGINE = innodb COMMENT '商品属性表';

-- 商品属性值表
CREATE TABLE product_attribute_value (
  value_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '属性值ID',
  attr_id INT UNSIGNED NOT NULL COMMENT '属性ID',
  product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  attr_value VARCHAR(255) NOT NULL COMMENT '属性值',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (attr_id) REFERENCES product_attribute(attr_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  PRIMARY KEY pk_value_id(value_id)
) ENGINE = innodb COMMENT '商品属性值表';

3. 订单模块

-- 订单表
CREATE TABLE orders (
  order_id VARCHAR(50) NOT NULL COMMENT '订单编号',
  customer_id INT UNSIGNED NOT NULL COMMENT '用户ID',
  payment DECIMAL(10,2) NOT NULL COMMENT '实付金额',
  payment_type TINYINT DEFAULT 1 COMMENT '支付类型(1:支付宝,2:微信)',
  postage DECIMAL(10,2) DEFAULT 0 COMMENT '运费',
  status TINYINT DEFAULT 10 COMMENT '订单状态(10:待付款,20:待发货,30:待收货,40:已完成,50:已取消)',
  payment_time DATETIME COMMENT '支付时间',
  shipping_time DATETIME COMMENT '发货时间',
  end_time DATETIME COMMENT '交易完成时间',
  close_time DATETIME COMMENT '交易关闭时间',
  shipping_name VARCHAR(20) COMMENT '物流公司',
  shipping_code VARCHAR(20) COMMENT '物流单号',
  receiver_name VARCHAR(20) NOT NULL COMMENT '收货人',
  receiver_phone VARCHAR(20) NOT NULL COMMENT '收货人电话',
  receiver_province VARCHAR(20) NOT NULL COMMENT '省',
  receiver_city VARCHAR(20) NOT NULL COMMENT '市',
  receiver_district VARCHAR(20) NOT NULL COMMENT '区',
  receiver_address VARCHAR(200) NOT NULL COMMENT '详细地址',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (customer_id) REFERENCES customer_login(customer_id),
  PRIMARY KEY pk_order_id(order_id)
) ENGINE = innodb COMMENT '订单表';

-- 订单明细表
CREATE TABLE order_item (
  id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '订单明细ID',
  order_id VARCHAR(50) NOT NULL COMMENT '订单编号',
  product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
  product_image VARCHAR(255) COMMENT '商品图片',
  current_unit_price DECIMAL(10,2) NOT NULL COMMENT '当前单价',
  quantity INT UNSIGNED NOT NULL COMMENT '数量',
  total_price DECIMAL(10,2) NOT NULL COMMENT '总价',
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  PRIMARY KEY pk_id(id)
) ENGINE = innodb COMMENT '订单明细表';

二、内容管理系统(CMS)数据库表设计

1. 用户权限管理

-- 管理员表
CREATE TABLE cms_admin (
  admin_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '管理员ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '管理员用户名',
  password_hash VARCHAR(128) NOT NULL COMMENT '密码哈希值',
  email VARCHAR(100) UNIQUE COMMENT '管理员邮箱',
  role ENUM('super_admin', 'admin', 'editor') DEFAULT 'editor' COMMENT '管理员角色',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  last_login_ip VARCHAR(20) COMMENT '最后登录IP',
  last_login_time DATETIME COMMENT '最后登录时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '管理员表';

-- 用户表
CREATE TABLE cms_user (
  user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  password_hash VARCHAR(128) NOT NULL COMMENT '密码哈希值',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  phone VARCHAR(20) UNIQUE COMMENT '手机号',
  gender TINYINT DEFAULT 0 COMMENT '性别(0:未知,1:男,2:女)',
  avatar VARCHAR(255) COMMENT '头像URL',
  bio TEXT COMMENT '个人简介',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '用户表';

-- 角色表
CREATE TABLE cms_role (
  role_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
  role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
  description TEXT COMMENT '角色描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '角色表';

-- 权限表
CREATE TABLE cms_permission (
  permission_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
  permission_name VARCHAR(100) NOT NULL COMMENT '权限名称',
  permission_code VARCHAR(50) NOT NULL UNIQUE COMMENT '权限编码',
  module VARCHAR(50) COMMENT '所属模块',
  type TINYINT DEFAULT 1 COMMENT '权限类型(1:菜单,2:按钮)',
  parent_id INT DEFAULT 0 COMMENT '父权限ID',
  sort_order INT DEFAULT 0 COMMENT '排序',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = innodb COMMENT '权限表';

-- 角色权限关联表
CREATE TABLE cms_role_permission (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  role_id INT NOT NULL COMMENT '角色ID',
  permission_id INT NOT NULL COMMENT '权限ID',
  FOREIGN KEY (role_id) REFERENCES cms_role(role_id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES cms_permission(permission_id) ON DELETE CASCADE,
  UNIQUE KEY uk_role_permission (role_id, permission_id)
) ENGINE = innodb COMMENT '角色权限关联表';

2. 内容管理

-- 内容分类表
CREATE TABLE cms_category (
  category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
  category_name VARCHAR(50) NOT NULL COMMENT '分类名称',
  parent_id INT DEFAULT 0 COMMENT '父分类ID',
  sort_order INT DEFAULT 0 COMMENT '排序',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '内容分类表';

-- 文章表
CREATE TABLE cms_article (
  article_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文章ID',
  title VARCHAR(255) NOT NULL COMMENT '文章标题',
  slug VARCHAR(255) NOT NULL UNIQUE COMMENT '文章别名(URL友好)',
  category_id INT NOT NULL COMMENT '分类ID',
  author_id INT COMMENT '作者ID',
  editor_id INT COMMENT '编辑ID',
  cover_image VARCHAR(255) COMMENT '封面图片',
  summary TEXT COMMENT '文章摘要',
  content LONGTEXT NOT NULL COMMENT '文章内容',
  view_count INT DEFAULT 0 COMMENT '浏览次数',
  comment_count INT DEFAULT 0 COMMENT '评论次数',
  status TINYINT DEFAULT 0 COMMENT '状态(0:草稿,1:已发布,2:已下架)',
  publish_time DATETIME COMMENT '发布时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (category_id) REFERENCES cms_category(category_id),
  FOREIGN KEY (author_id) REFERENCES cms_user(user_id),
  FOREIGN KEY (editor_id) REFERENCES cms_admin(admin_id)
) ENGINE = innodb COMMENT '文章表';

-- 标签表
CREATE TABLE cms_tag (
  tag_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
  tag_name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = innodb COMMENT '标签表';

-- 文章标签关联表
CREATE TABLE cms_article_tag (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  article_id INT NOT NULL COMMENT '文章ID',
  tag_id INT NOT NULL COMMENT '标签ID',
  FOREIGN KEY (article_id) REFERENCES cms_article(article_id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES cms_tag(tag_id) ON DELETE CASCADE,
  UNIQUE KEY uk_article_tag (article_id, tag_id)
) ENGINE = innodb COMMENT '文章标签关联表';

-- 评论表
CREATE TABLE cms_comment (
  comment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
  article_id INT NOT NULL COMMENT '文章ID',
  user_id INT COMMENT '用户ID',
  parent_id INT DEFAULT 0 COMMENT '父评论ID(回复评论时使用)',
  content TEXT NOT NULL COMMENT '评论内容',
  status TINYINT DEFAULT 0 COMMENT '状态(0:待审核,1:已审核,2:已删除)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
  FOREIGN KEY (article_id) REFERENCES cms_article(article_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES cms_user(user_id)
) ENGINE = innodb COMMENT '评论表';

3. 系统配置

-- 系统配置表
CREATE TABLE cms_system (
  config_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
  config_key VARCHAR(50) NOT NULL UNIQUE COMMENT '配置键',
  config_value TEXT COMMENT '配置值',
  config_type TINYINT DEFAULT 1 COMMENT '配置类型(1:文本,2:文本域,3:图片,4:开关)',
  description VARCHAR(255) COMMENT '配置描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '系统配置表';

-- 广告表
CREATE TABLE cms_advertisement (
  ad_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '广告ID',
  title VARCHAR(255) NOT NULL COMMENT '广告标题',
  description TEXT COMMENT '广告描述',
  image_url VARCHAR(255) COMMENT '广告图片URL',
  target_url VARCHAR(255) COMMENT '广告点击后跳转的URL',
  position VARCHAR(50) COMMENT '广告位置',
  start_time DATETIME COMMENT '开始时间',
  end_time DATETIME COMMENT '结束时间',
  sort_order INT DEFAULT 0 COMMENT '排序',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '广告表';

三、项目管理系统数据库表设计

1. 基础设置

-- 用户表
CREATE TABLE pm_user (
  user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  password_hash VARCHAR(128) NOT NULL COMMENT '密码哈希值',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  real_name VARCHAR(50) COMMENT '真实姓名',
  avatar VARCHAR(255) COMMENT '头像URL',
  department_id INT COMMENT '部门ID',
  position VARCHAR(50) COMMENT '职位',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '用户表';

-- 部门表
CREATE TABLE pm_department (
  department_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
  department_name VARCHAR(50) NOT NULL COMMENT '部门名称',
  parent_id INT DEFAULT 0 COMMENT '父部门ID',
  leader_id INT COMMENT '部门负责人ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (leader_id) REFERENCES pm_user(user_id)
) ENGINE = innodb COMMENT '部门表';

-- 更新部门表的外键约束
ALTER TABLE pm_user ADD FOREIGN KEY (department_id) REFERENCES pm_department(department_id);

-- 项目类型表
CREATE TABLE pm_project_type (
  type_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '类型ID',
  type_name VARCHAR(50) NOT NULL COMMENT '类型名称',
  description TEXT COMMENT '类型描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = innodb COMMENT '项目类型表';

-- 优先级表
CREATE TABLE pm_priority (
  priority_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '优先级ID',
  priority_name VARCHAR(20) NOT NULL COMMENT '优先级名称',
  priority_value INT NOT NULL COMMENT '优先级数值(越大优先级越高)',
  color VARCHAR(20) COMMENT '显示颜色',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = innodb COMMENT '优先级表';

2. 项目管理

-- 项目表
CREATE TABLE pm_project (
  project_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
  project_name VARCHAR(100) NOT NULL COMMENT '项目名称',
  project_code VARCHAR(50) NOT NULL UNIQUE COMMENT '项目编码',
  type_id INT NOT NULL COMMENT '项目类型ID',
  manager_id INT NOT NULL COMMENT '项目经理ID',
  description TEXT COMMENT '项目描述',
  start_date DATE COMMENT '开始日期',
  end_date DATE COMMENT '结束日期',
  status TINYINT DEFAULT 1 COMMENT '状态(1:计划中,2:进行中,3:已暂停,4:已完成,5:已取消)',
  progress INT DEFAULT 0 COMMENT '进度(0-100)',
  budget DECIMAL(15,2) COMMENT '预算',
  actual_cost DECIMAL(15,2) DEFAULT 0 COMMENT '实际成本',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (type_id) REFERENCES pm_project_type(type_id),
  FOREIGN KEY (manager_id) REFERENCES pm_user(user_id)
) ENGINE = innodb COMMENT '项目表';

-- 项目成员表
CREATE TABLE pm_project_member (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  project_id INT NOT NULL COMMENT '项目ID',
  user_id INT NOT NULL COMMENT '用户ID',
  role VARCHAR(50) COMMENT '在项目中的角色',
  join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入日期',
  FOREIGN KEY (project_id) REFERENCES pm_project(project_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES pm_user(user_id),
  UNIQUE KEY uk_project_user (project_id, user_id)
) ENGINE = innodb COMMENT '项目成员表';

-- 任务表
CREATE TABLE pm_task (
  task_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '任务ID',
  project_id INT NOT NULL COMMENT '项目ID',
  task_name VARCHAR(100) NOT NULL COMMENT '任务名称',
  parent_id INT DEFAULT 0 COMMENT '父任务ID',
  assignee_id INT COMMENT '执行人ID',
  creator_id INT NOT NULL COMMENT '创建人ID',
  priority_id INT DEFAULT 3 COMMENT '优先级ID',
  description TEXT COMMENT '任务描述',
  start_date DATETIME COMMENT '计划开始时间',
  due_date DATETIME COMMENT '计划结束时间',
  actual_start_date DATETIME COMMENT '实际开始时间',
  actual_end_date DATETIME COMMENT '实际结束时间',
  status TINYINT DEFAULT 1 COMMENT '状态(1:待处理,2:进行中,3:已完成,4:已取消)',
  progress INT DEFAULT 0 COMMENT '进度(0-100)',
  estimated_hours DECIMAL(10,2) COMMENT '预计工时(小时)',
  actual_hours DECIMAL(10,2) DEFAULT 0 COMMENT '实际工时(小时)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (project_id) REFERENCES pm_project(project_id),
  FOREIGN KEY (assignee_id) REFERENCES pm_user(user_id),
  FOREIGN KEY (creator_id) REFERENCES pm_user(user_id),
  FOREIGN KEY (priority_id) REFERENCES pm_priority(priority_id)
) ENGINE = innodb COMMENT '任务表';

-- 任务评论表
CREATE TABLE pm_task_comment (
  comment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
  task_id INT NOT NULL COMMENT '任务ID',
  user_id INT NOT NULL COMMENT '用户ID',
  content TEXT NOT NULL COMMENT '评论内容',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
  FOREIGN KEY (task_id) REFERENCES pm_task(task_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES pm_user(user_id)
) ENGINE = innodb COMMENT '任务评论表';

-- 工作日志表
CREATE TABLE pm_work_log (
  log_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
  task_id INT NOT NULL COMMENT '任务ID',
  user_id INT NOT NULL COMMENT '用户ID',
  work_date DATE NOT NULL COMMENT '工作日期',
  hours DECIMAL(5,2) NOT NULL COMMENT '工作时长(小时)',
  content TEXT NOT NULL COMMENT '工作内容',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (task_id) REFERENCES pm_task(task_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES pm_user(user_id)
) ENGINE = innodb COMMENT '工作日志表';

3. 文档管理

-- 文档表
CREATE TABLE pm_document (
  doc_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文档ID',
  project_id INT NOT NULL COMMENT '项目ID',
  doc_name VARCHAR(255) NOT NULL COMMENT '文档名称',
  file_path VARCHAR(255) NOT NULL COMMENT '文件路径',
  file_size BIGINT COMMENT '文件大小(字节)',
  file_type VARCHAR(50) COMMENT '文件类型',
  creator_id INT NOT NULL COMMENT '创建人ID',
  description TEXT COMMENT '文档描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (project_id) REFERENCES pm_project(project_id) ON DELETE CASCADE,
  FOREIGN KEY (creator_id) REFERENCES pm_user(user_id)
) ENGINE = innodb COMMENT '文档表';

-- 里程碑表
CREATE TABLE pm_milestone (
  milestone_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '里程碑ID',
  project_id INT NOT NULL COMMENT '项目ID',
  milestone_name VARCHAR(100) NOT NULL COMMENT '里程碑名称',
  description TEXT COMMENT '里程碑描述',
  due_date DATE NOT NULL COMMENT '计划完成日期',
  actual_complete_date DATE COMMENT '实际完成日期',
  status TINYINT DEFAULT 1 COMMENT '状态(1:未完成,2:已完成,3:延期)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (project_id) REFERENCES pm_project(project_id) ON DELETE CASCADE
) ENGINE = innodb COMMENT '里程碑表';

四、论坛系统数据库表设计

1. 用户管理

-- 用户表
CREATE TABLE forum_user (
  user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  password_hash VARCHAR(128) NOT NULL COMMENT '密码哈希值',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  avatar VARCHAR(255) COMMENT '头像URL',
  signature TEXT COMMENT '个性签名',
  posts_count INT DEFAULT 0 COMMENT '发帖数',
  replies_count INT DEFAULT 0 COMMENT '回复数',
  followers_count INT DEFAULT 0 COMMENT '粉丝数',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:正常,2:待审核)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  last_login_time DATETIME COMMENT '最后登录时间'
) ENGINE = innodb COMMENT '用户表';

2. 版块与帖子

-- 版块表
CREATE TABLE forum_category (
  category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '版块ID',
  category_name VARCHAR(50) NOT NULL COMMENT '版块名称',
  description TEXT COMMENT '版块描述',
  sort_order INT DEFAULT 0 COMMENT '排序',
  posts_count INT DEFAULT 0 COMMENT '帖子数',
  last_post_id INT COMMENT '最后发帖ID',
  status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE = innodb COMMENT '版块表';

-- 帖子表
CREATE TABLE forum_topic (
  topic_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '帖子ID',
  category_id INT NOT NULL COMMENT '版块ID',
  user_id INT NOT NULL COMMENT '作者ID',
  title VARCHAR(255) NOT NULL COMMENT '标题',
  content LONGTEXT NOT NULL COMMENT '内容',
  views_count INT DEFAULT 0 COMMENT '浏览数',
  replies_count INT DEFAULT 0 COMMENT '回复数',
  last_reply_time DATETIME COMMENT '最后回复时间',
  last_reply_user_id INT COMMENT '最后回复用户ID',
  status TINYINT DEFAULT 1 COMMENT '状态(0:草稿,1:正常,2:精华,3:置顶,4:已删除)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (category_id) REFERENCES forum_category(category_id),
  FOREIGN KEY (user_id) REFERENCES forum_user(user_id),
  FOREIGN KEY (last_reply_user_id) REFERENCES forum_user(user_id)
) ENGINE = innodb COMMENT '帖子表';

-- 回复表
CREATE TABLE forum_reply (
  reply_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '回复ID',
  topic_id INT NOT NULL COMMENT '帖子ID',
  user_id INT NOT NULL COMMENT '回复者ID',
  parent_id INT DEFAULT 0 COMMENT '父回复ID(用于嵌套回复)',
  content TEXT NOT NULL COMMENT '回复内容',
  likes_count INT DEFAULT 0 COMMENT '点赞数',
  status TINYINT DEFAULT 1 COMMENT '状态(0:待审核,1:已发布,2:已删除)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '回复时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (topic_id) REFERENCES forum_topic(topic_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES forum_user(user_id)
) ENGINE = innodb COMMENT '回复表';

-- 标签表
CREATE TABLE forum_tag (
  tag_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
  tag_name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
  topic_count INT DEFAULT 0 COMMENT '使用次数',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = innodb COMMENT '标签表';

-- 帖子标签关联表
CREATE TABLE forum_topic_tag (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
  topic_id INT NOT NULL COMMENT '帖子ID',
  tag_id INT NOT NULL COMMENT '标签ID',
  FOREIGN KEY (topic_id) REFERENCES forum_topic(topic_id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES forum_tag(tag_id) ON DELETE CASCADE,
  UNIQUE KEY uk_topic_tag (topic_id, tag_id)
) ENGINE = innodb COMMENT '帖子标签关联表';

3. 互动功能

-- 用户点赞表
CREATE TABLE forum_like (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '点赞ID',
  user_id INT NOT NULL COMMENT '用户ID',
  target_type TINYINT NOT NULL COMMENT '目标类型(1:帖子,2:回复)',
  target_id INT NOT NULL COMMENT '目标ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '点赞时间',
  FOREIGN KEY (user_id) REFERENCES forum_user(user_id),
  UNIQUE KEY uk_user_target (user_id, target_type, target_id)
) ENGINE = innodb COMMENT '用户点赞表';

-- 用户收藏表
CREATE TABLE forum_collection (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '收藏ID',
  user_id INT NOT NULL COMMENT '用户ID',
  topic_id INT NOT NULL COMMENT '帖子ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '收藏时间',
  FOREIGN KEY (user_id) REFERENCES forum_user(user_id),
  FOREIGN KEY (topic_id) REFERENCES forum_topic(topic_id),
  UNIQUE KEY uk_user_topic (user_id, topic_id)
) ENGINE = innodb COMMENT '用户收藏表';

-- 通知表
CREATE TABLE forum_notification (
  notification_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '通知ID',
  receiver_id INT NOT NULL COMMENT '接收者ID',
  sender_id INT COMMENT '发送者ID',
  notification_type TINYINT NOT NULL COMMENT '通知类型(1:回复,2:点赞,3:关注,4:系统)',
  target_id INT COMMENT '相关ID',
  content TEXT COMMENT '通知内容',
  is_read TINYINT DEFAULT 0 COMMENT '是否已读(0:未读,1:已读)',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (receiver_id) REFERENCES forum_user(user_id),
  FOREIGN KEY (sender_id) REFERENCES forum_user(user_id)
) ENGINE = innodb COMMENT '通知表';

设计总结

以上四个系统的数据库表设计遵循了以下原则：

1. 高内聚、低耦合：每个表专注于一个业务实体，职责单一
2. 合理的关系设计：使用外键维护表之间的关系，确保数据完整性
3. 索引优化：为常用查询字段设计适当的索引
4. 时间戳记录：大多数表都包含创建和更新时间字段
5. 状态管理：使用状态字段表示数据的不同状态，避免物理删除
6. 性能考虑：对于热点字段（如浏览数、评论数）进行冗余存储，减少计算开销

这些设计案例可以作为实际项目开发的参考，根据具体业务需求进行适当调整和扩展。