数据库设计文档

详细表结构定义、字段类型、约束条件、SQL 建表语句与索引优化建议。

1. 概述

1.1 数据库选型

主数据库: MySQL 8.0
缓存: Redis
文件存储: 阿里云 OSS / 腾讯云 COS

1.2 命名规范

表名: 小写字母 + 下划线，单数形式
字段名: 小写字母 + 下划线
主键: id (VARCHAR(32) UUID)
外键: 关联表名_id
时间字段: created_at, updated_at, deleted_at

1.3 通用字段

所有表均含：id、created_at、updated_at；部分表含 deleted_at（软删除）。

2. 核心表结构

2.1 用户表 (user)

字段名	类型	说明	约束
id	VARCHAR(32)	用户ID	PK
openid	VARCHAR(64)	微信OpenID	UNIQUE, NOT NULL
unionid	VARCHAR(64)	微信UnionID	UNIQUE
phone	VARCHAR(20)	手机号
name	VARCHAR(50)	姓名
avatar	VARCHAR(255)	头像URL
role	ENUM	角色	NOT NULL, DEFAULT 'researcher'
status	TINYINT	状态(0禁用,1启用)	DEFAULT 1
last_login_at	DATETIME	最后登录时间
created_at	DATETIME	创建时间	NOT NULL
updated_at	DATETIME	更新时间

role: researcher, reviewer, dispatcher, admin。

2.2 电子围栏表 (geofence)

字段名	类型	说明	约束
id	VARCHAR(32)	围栏ID	PK
name	VARCHAR(100)	围栏名称	NOT NULL
address	VARCHAR(255)	地址描述
type	ENUM	类型(circle/polygon)	NOT NULL
center_lat	DECIMAL(10,7)	中心点纬度
center_lng	DECIMAL(10,7)	中心点经度
radius	INT	半径(米)
polygon	JSON	多边形坐标数组
created_by	VARCHAR(32)	创建人ID	FK -> user.id
created_at	DATETIME	创建时间	NOT NULL
updated_at	DATETIME	更新时间
deleted_at	DATETIME	删除时间

2.3 表单模板表 (form_template)

字段：id, name, description, fields(JSON), status, created_by, created_at, updated_at, deleted_at。fields 为 JSON 数组，每项含 id、type、label、required、options 等。

2.4 任务表 (task)

字段名	类型	说明
id	VARCHAR(32)	任务ID
title	VARCHAR(200)	任务标题
description	TEXT	任务描述
geofence_id	VARCHAR(32)	电子围栏ID
form_template_id	VARCHAR(32)	表单模板ID
status	ENUM	draft/published/in_progress/completed/cancelled
deadline	DATETIME	截止时间
require_photo	TINYINT	是否需要拍照
photo_labels	JSON	照片标签配置
max_assignees	INT	最大分配人数
dispatch_mode	ENUM	manual/auto
open_for_grab	TINYINT	是否开放抢单
created_by	VARCHAR(32)	创建人ID
published_at	DATETIME	发布时间
created_at	DATETIME	创建时间
updated_at	DATETIME	更新时间
deleted_at	DATETIME	删除时间

2.5 任务分配表 (task_assignment)

字段：id, task_id, user_id, status(pending/in_progress/submitted/approved/rejected), assign_type(auto/manual/grab), assigned_at, started_at, submitted_at, completed_at, created_at, updated_at。UNIQUE(task_id, user_id)。

2.6 打卡记录表 (checkin_record)

字段：id, assignment_id, latitude, longitude, accuracy, address, is_in_geofence, distance, checkin_at, created_at。

2.7 照片表 (photo)

字段：id, assignment_id, url, thumbnail_url, label, ocr_result(JSON), file_size, width, height, created_at。

2.8 表单提交表 (form_submission)

字段：id, assignment_id, form_template_id, form_data(JSON), is_draft, submitted_at, created_at, updated_at。

2.9 审核记录表 (review_record)

字段：id, submission_id, reviewer_id, status(pending/approved/rejected), comment, reject_reason, review_level, reviewed_at, created_at。

3. 辅助表

message：user_id, type(task/review/system), title, content, related_id, is_read, created_at。

operation_log：user_id, action, target_type, target_id, detail(JSON), ip, user_agent, created_at。

4. 索引优化建议

常用查询索引：task(status, deadline, created_by)；task_assignment(user_id, status)；form_submission(is_draft, submitted_at)。可增加联合索引 idx_user_status、idx_submission_level。

5. 表关系说明

user (1) --- (N) task_assignment
task (1) --- (N) task_assignment
task (N) --- (1) geofence
task (N) --- (1) form_template
task_assignment (1) --- (1) checkin_record
task_assignment (1) --- (N) photo
task_assignment (1) --- (1) form_submission
form_submission (1) --- (N) review_record