shiro/campus-activity-system
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
campus-activity-system/server/docs/数据库设计.md

18 KiB
Raw Permalink Blame History

校园活动组织与报名系统 - 数据库设计

1. 数据库概述

  • 数据库名称：campus_activity
  • 字符集：utf8mb4
  • 排序规则：utf8mb4_general_ci

2. ER图（实体关系图）

┌─────────────┐       ┌─────────────────┐       ┌─────────────┐
│    User     │       │   Registration  │       │  Activity   │
│─────────────│       │─────────────────│       │─────────────│
│ id (PK)     │───┐   │ id (PK)         │   ┌───│ id (PK)     │
│ username    │   │   │ user_id (FK)    │───┘   │ title       │
│ password    │   └───│ activity_id(FK) │       │ description │
│ name        │       │ status          │       │ start_time  │
│ student_id  │       │ ticket_code     │       │ end_time    │
│ role        │       │ created_at      │       │ location    │
│ ...         │       │ ...             │       │ max_num     │
└─────────────┘       └─────────────────┘       │ status      │
      │                       │                 │ admin_id(FK)│
      │               ┌───────┴───────┐         └─────────────┘
      │               │               │                │
      │       ┌───────┴───┐   ┌───────┴───┐           │
      │       │  CheckIn  │   │  Review   │           │
      │       │───────────│   │───────────│           │
      │       │ id (PK)   │   │ id (PK)   │           │
      └───────│ reg_id(FK)│   │ user_id   │───────────┘
              │ check_time│   │ act_id    │
              └───────────┘   │ rating    │
                              │ content   │
                              └───────────┘

3. 数据表设计

3.1 用户表 (user)

存储系统用户信息，包括学生和管理员。

字段名 类型 约束 说明
id BIGINT PK, AUTO_INCREMENT 用户ID
username VARCHAR(50) UNIQUE, NOT NULL 登录用户名
password VARCHAR(255) NOT NULL 密码（BCrypt加密）
name VARCHAR(50) NOT NULL 真实姓名
student_id VARCHAR(20) UNIQUE 学号（学生必填）
email VARCHAR(100) 邮箱
phone VARCHAR(20) 手机号
avatar VARCHAR(255) 头像URL
role TINYINT NOT NULL, DEFAULT 0 角色：0-学生，1-管理员
status TINYINT DEFAULT 1 状态：0-禁用，1-正常
created_at DATETIME DEFAULT CURRENT_TIMESTAMP 创建时间
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP 更新时间
deleted TINYINT DEFAULT 0 逻辑删除：0-未删除，1-已删除

索引：

  • idx_username (username)
  • idx_student_id (student_id)
CREATE TABLE `user` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL COMMENT '登录用户名',
    `password` VARCHAR(255) NOT NULL COMMENT '密码',
    `name` VARCHAR(50) NOT NULL COMMENT '真实姓名',
    `student_id` VARCHAR(20) DEFAULT NULL COMMENT '学号',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
    `role` TINYINT NOT NULL DEFAULT 0 COMMENT '角色：0-学生，1-管理员',
    `status` TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_student_id` (`student_id`),
    KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

3.2 活动表 (activity)

存储活动信息。

字段名 类型 约束 说明
id BIGINT PK, AUTO_INCREMENT 活动ID
title VARCHAR(100) NOT NULL 活动名称
description TEXT 活动简介
cover_image VARCHAR(255) 封面图片URL
start_time DATETIME NOT NULL 开始时间
end_time DATETIME NOT NULL 结束时间
registration_deadline DATETIME 报名截止时间
location VARCHAR(200) NOT NULL 活动地点
max_participants INT NOT NULL 报名人数上限
current_participants INT DEFAULT 0 当前报名人数
status TINYINT DEFAULT 0 状态：0-未开始，1-报名中，2-进行中，3-已结束
category VARCHAR(50) 活动分类
admin_id BIGINT FK 创建者ID（管理员）
qr_code VARCHAR(255) 签到二维码
created_at DATETIME DEFAULT CURRENT_TIMESTAMP 创建时间
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP 更新时间
deleted TINYINT DEFAULT 0 逻辑删除

索引：

  • idx_status (status)
  • idx_start_time (start_time)
  • idx_admin_id (admin_id)
CREATE TABLE `activity` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '活动ID',
    `title` VARCHAR(100) NOT NULL COMMENT '活动名称',
    `description` TEXT DEFAULT NULL COMMENT '活动简介',
    `cover_image` VARCHAR(255) DEFAULT NULL COMMENT '封面图片',
    `start_time` DATETIME NOT NULL COMMENT '开始时间',
    `end_time` DATETIME NOT NULL COMMENT '结束时间',
    `registration_deadline` DATETIME DEFAULT NULL COMMENT '报名截止时间',
    `location` VARCHAR(200) NOT NULL COMMENT '活动地点',
    `max_participants` INT NOT NULL COMMENT '报名人数上限',
    `current_participants` INT DEFAULT 0 COMMENT '当前报名人数',
    `status` TINYINT DEFAULT 0 COMMENT '状态：0-未开始，1-报名中，2-进行中，3-已结束',
    `category` VARCHAR(50) DEFAULT NULL COMMENT '活动分类',
    `admin_id` BIGINT NOT NULL COMMENT '创建者ID',
    `qr_code` VARCHAR(255) DEFAULT NULL COMMENT '签到二维码',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_start_time` (`start_time`),
    KEY `idx_admin_id` (`admin_id`),
    CONSTRAINT `fk_activity_admin` FOREIGN KEY (`admin_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='活动表';

3.3 报名表 (registration)

存储学生活动报名信息。

字段名 类型 约束 说明
id BIGINT PK, AUTO_INCREMENT 报名ID
user_id BIGINT FK, NOT NULL 用户ID
activity_id BIGINT FK, NOT NULL 活动ID
ticket_code VARCHAR(100) UNIQUE 电子票唯一码
ticket_pdf_url VARCHAR(255) 电子票PDF地址
status TINYINT DEFAULT 1 状态：0-已取消，1-已报名，2-已签到
created_at DATETIME DEFAULT CURRENT_TIMESTAMP 报名时间
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP 更新时间
canceled_at DATETIME 取消时间

索引：

  • uk_user_activity (user_id, activity_id) UNIQUE
  • idx_activity_id (activity_id)
  • idx_ticket_code (ticket_code)
CREATE TABLE `registration` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '报名ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `ticket_code` VARCHAR(100) DEFAULT NULL COMMENT '电子票唯一码',
    `ticket_pdf_url` VARCHAR(255) DEFAULT NULL COMMENT '电子票PDF地址',
    `status` TINYINT DEFAULT 1 COMMENT '状态：0-已取消，1-已报名，2-已签到',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '报名时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `canceled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_activity` (`user_id`, `activity_id`),
    UNIQUE KEY `uk_ticket_code` (`ticket_code`),
    KEY `idx_activity_id` (`activity_id`),
    CONSTRAINT `fk_registration_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_registration_activity` FOREIGN KEY (`activity_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='报名表';

3.4 签到表 (check_in)

存储签到记录。

字段名 类型 约束 说明
id BIGINT PK, AUTO_INCREMENT 签到ID
registration_id BIGINT FK, UNIQUE 报名ID
user_id BIGINT FK 用户ID
activity_id BIGINT FK 活动ID
check_in_time DATETIME DEFAULT CURRENT_TIMESTAMP 签到时间
check_in_method TINYINT DEFAULT 0 签到方式：0-扫码，1-管理员代签

索引：

  • uk_registration_id (registration_id) UNIQUE
  • idx_activity_id (activity_id)
CREATE TABLE `check_in` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '签到ID',
    `registration_id` BIGINT NOT NULL COMMENT '报名ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `check_in_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间',
    `check_in_method` TINYINT DEFAULT 0 COMMENT '签到方式：0-扫码，1-管理员代签',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_registration_id` (`registration_id`),
    KEY `idx_activity_id` (`activity_id`),
    KEY `idx_user_id` (`user_id`),
    CONSTRAINT `fk_checkin_registration` FOREIGN KEY (`registration_id`) REFERENCES `registration` (`id`),
    CONSTRAINT `fk_checkin_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_checkin_activity` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='签到表';

3.5 评价表 (review)

存储活动评价信息。

字段名 类型 约束 说明
id BIGINT PK, AUTO_INCREMENT 评价ID
user_id BIGINT FK, NOT NULL 用户ID
activity_id BIGINT FK, NOT NULL 活动ID
rating TINYINT NOT NULL 评分（1-5）
content TEXT 评论内容
created_at DATETIME DEFAULT CURRENT_TIMESTAMP 评价时间
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP 更新时间

索引：

  • uk_user_activity (user_id, activity_id) UNIQUE（确保每人每活动只能评价一次）
  • idx_activity_id (activity_id)
CREATE TABLE `review` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '评价ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `rating` TINYINT NOT NULL COMMENT '评分（1-5）',
    `content` TEXT DEFAULT NULL COMMENT '评论内容',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '评价时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_activity` (`user_id`, `activity_id`),
    KEY `idx_activity_id` (`activity_id`),
    CONSTRAINT `fk_review_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_review_activity` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='评价表';

4. 完整建库脚本

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `campus_activity`
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

USE `campus_activity`;

-- 创建用户表
CREATE TABLE `user` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL COMMENT '登录用户名',
    `password` VARCHAR(255) NOT NULL COMMENT '密码',
    `name` VARCHAR(50) NOT NULL COMMENT '真实姓名',
    `student_id` VARCHAR(20) DEFAULT NULL COMMENT '学号',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
    `role` TINYINT NOT NULL DEFAULT 0 COMMENT '角色：0-学生，1-管理员',
    `status` TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_student_id` (`student_id`),
    KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

-- 创建活动表
CREATE TABLE `activity` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '活动ID',
    `title` VARCHAR(100) NOT NULL COMMENT '活动名称',
    `description` TEXT DEFAULT NULL COMMENT '活动简介',
    `cover_image` VARCHAR(255) DEFAULT NULL COMMENT '封面图片',
    `start_time` DATETIME NOT NULL COMMENT '开始时间',
    `end_time` DATETIME NOT NULL COMMENT '结束时间',
    `registration_deadline` DATETIME DEFAULT NULL COMMENT '报名截止时间',
    `location` VARCHAR(200) NOT NULL COMMENT '活动地点',
    `max_participants` INT NOT NULL COMMENT '报名人数上限',
    `current_participants` INT DEFAULT 0 COMMENT '当前报名人数',
    `status` TINYINT DEFAULT 0 COMMENT '状态：0-未开始，1-报名中，2-进行中，3-已结束',
    `category` VARCHAR(50) DEFAULT NULL COMMENT '活动分类',
    `admin_id` BIGINT NOT NULL COMMENT '创建者ID',
    `qr_code` VARCHAR(255) DEFAULT NULL COMMENT '签到二维码',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_start_time` (`start_time`),
    KEY `idx_admin_id` (`admin_id`),
    CONSTRAINT `fk_activity_admin` FOREIGN KEY (`admin_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='活动表';

-- 创建报名表
CREATE TABLE `registration` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '报名ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `ticket_code` VARCHAR(100) DEFAULT NULL COMMENT '电子票唯一码',
    `ticket_pdf_url` VARCHAR(255) DEFAULT NULL COMMENT '电子票PDF地址',
    `status` TINYINT DEFAULT 1 COMMENT '状态：0-已取消，1-已报名，2-已签到',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '报名时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `canceled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_activity` (`user_id`, `activity_id`),
    UNIQUE KEY `uk_ticket_code` (`ticket_code`),
    KEY `idx_activity_id` (`activity_id`),
    CONSTRAINT `fk_registration_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_registration_activity` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='报名表';

-- 创建签到表
CREATE TABLE `check_in` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '签到ID',
    `registration_id` BIGINT NOT NULL COMMENT '报名ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `check_in_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间',
    `check_in_method` TINYINT DEFAULT 0 COMMENT '签到方式：0-扫码，1-管理员代签',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_registration_id` (`registration_id`),
    KEY `idx_activity_id` (`activity_id`),
    KEY `idx_user_id` (`user_id`),
    CONSTRAINT `fk_checkin_registration` FOREIGN KEY (`registration_id`) REFERENCES `registration` (`id`),
    CONSTRAINT `fk_checkin_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_checkin_activity` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='签到表';

-- 创建评价表
CREATE TABLE `review` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '评价ID',
    `user_id` BIGINT NOT NULL COMMENT '用户ID',
    `activity_id` BIGINT NOT NULL COMMENT '活动ID',
    `rating` TINYINT NOT NULL COMMENT '评分（1-5）',
    `content` TEXT DEFAULT NULL COMMENT '评论内容',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '评价时间',
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_activity` (`user_id`, `activity_id`),
    KEY `idx_activity_id` (`activity_id`),
    CONSTRAINT `fk_review_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
    CONSTRAINT `fk_review_activity` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='评价表';

-- 插入测试管理员账号（密码：admin123，BCrypt加密）
INSERT INTO `user` (`username`, `password`, `name`, `role`) VALUES
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iKXP6X3SqmI8Q0WJLoUbWAHZJ.5i', '系统管理员', 1);

5. 数据字典

5.1 角色类型 (user.role)

值 说明
0 普通学生
1 活动管理员

5.2 活动状态 (activity.status)

值 说明
0 未开始
1 报名中
2 进行中
3 已结束

5.3 报名状态 (registration.status)

值 说明
0 已取消
1 已报名
2 已签到

5.4 签到方式 (check_in.check_in_method)

值 说明
0 扫码签到
1 管理员代签
Reference in New Issue View Git Blame Copy Permalink