JDWA Green-U 数据库设计文档

本文档详细描述JDWA Green-U项目的数据库设计，包括表结构设计、表关系模型、索引优化以及完整的数据字典，为开发者提供全面的数据层面参考。

数据库概览

JDWA Green-U项目采用MySQL 8.0作为关系型数据库，存储用户数据、活动记录、积分交易、成就系统等核心业务数据。数据库设计遵循规范化原则，同时考虑了查询性能和扩展性。

数据库基本信息

  • 数据库名称: jdwa_green
  • 字符集: utf8mb4
  • 排序规则: utf8mb4_general_ci
  • 存储引擎: InnoDB

核心表概览

表名说明关键字段
jdwa_user用户信息表id, username, password
jdwa_activity绿色活动记录表id, user_id, activity_type
jdwa_carbon_record碳减排记录表id, user_id, carbon_amount
jdwa_point_record积分记录表id, user_id, points, record_type
jdwa_achievement成就定义表id, name, achievement_type, condition_value
jdwa_user_achievement用户成就关联表id, user_id, achievement_id
jdwa_prize奖品信息表id, name, points_needed
jdwa_exchange_record兑换记录表id, user_id, prize_id

表结构设计

用户信息表 (jdwa_user)

存储用户基本信息、认证信息和统计数据。

CREATE TABLE `jdwa_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '密码（加密存储）',
  `nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `gender` tinyint(4) DEFAULT '0' COMMENT '性别：0-未知，1-男，2-女',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `total_carbon` decimal(10,2) DEFAULT '0.00' COMMENT '总碳减排量(kg)',
  `carbon_points` int(11) DEFAULT '0' COMMENT '当前积分',
  `total_points` int(11) DEFAULT '0' COMMENT '累计积分',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-禁用，1-启用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

绿色活动记录表 (jdwa_activity)

记录用户的各类环保活动数据，如步行、骑行、公共交通出行等。

CREATE TABLE `jdwa_activity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `activity_type` varchar(20) NOT NULL COMMENT '活动类型：WALK-步行，BIKE-骑行，BUS-公交，SUBWAY-地铁',
  `activity_data` json DEFAULT NULL COMMENT '活动数据JSON',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `distance` decimal(10,2) DEFAULT '0.00' COMMENT '距离(米)',
  `step_count` int(11) DEFAULT '0' COMMENT '步数(步行时)',
  `carbon_reduction` decimal(10,2) DEFAULT '0.00' COMMENT '碳减排量(kg)',
  `points_earned` int(11) DEFAULT '0' COMMENT '获得积分',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-无效，1-有效',
  `data_source` varchar(20) DEFAULT 'APP' COMMENT '数据来源：APP-应用记录，DEVICE-设备同步',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_activity_type` (`activity_type`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='绿色活动记录表';

碳减排记录表 (jdwa_carbon_record)

详细记录用户碳减排量的变化情况。

CREATE TABLE `jdwa_carbon_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `activity_id` bigint(20) DEFAULT NULL COMMENT '关联的活动ID',
  `carbon_amount` decimal(10,2) NOT NULL COMMENT '碳减排量(kg)',
  `record_type` varchar(20) NOT NULL COMMENT '记录类型：ACTIVITY-活动，TASK-任务，OTHER-其他',
  `description` varchar(255) DEFAULT NULL COMMENT '记录描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_activity_id` (`activity_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='碳减排记录表';

积分记录表 (jdwa_point_record)

记录用户积分的获取和消费情况。

CREATE TABLE `jdwa_point_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `activity_id` bigint(20) DEFAULT NULL COMMENT '关联的活动ID',
  `points` int(11) NOT NULL COMMENT '积分变动数量',
  `balance` int(11) NOT NULL COMMENT '变动后余额',
  `record_type` varchar(20) NOT NULL COMMENT '记录类型：EARN-获取，CONSUME-消费，EXPIRE-过期',
  `source_type` varchar(20) DEFAULT NULL COMMENT '来源类型：ACTIVITY-活动，ACHIEVEMENT-成就，EXCHANGE-兑换',
  `source_id` bigint(20) DEFAULT NULL COMMENT '来源ID',
  `description` varchar(255) DEFAULT NULL COMMENT '记录描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_record_type` (`record_type`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分记录表';

成就定义表 (jdwa_achievement)

定义系统中的各类成就及其解锁条件。

CREATE TABLE `jdwa_achievement` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '成就ID',
  `name` varchar(50) NOT NULL COMMENT '成就名称',
  `description` varchar(255) DEFAULT NULL COMMENT '成就描述',
  `icon_url` varchar(255) DEFAULT NULL COMMENT '图标URL',
  `achievement_type` varchar(20) NOT NULL COMMENT '成就类型：CARBON-碳减排，STEP-步数，ACTIVITY-活动次数',
  `condition_value` int(11) NOT NULL COMMENT '条件值',
  `reward_points` int(11) DEFAULT '0' COMMENT '奖励积分',
  `difficulty_level` tinyint(4) DEFAULT '1' COMMENT '难度等级：1-简单，2-中等，3-困难',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-禁用，1-启用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_achievement_type` (`achievement_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成就定义表';

用户成就关联表 (jdwa_user_achievement)

记录用户获得的成就。

CREATE TABLE `jdwa_user_achievement` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `achievement_id` bigint(20) NOT NULL COMMENT '成就ID',
  `achievement_name` varchar(50) DEFAULT NULL COMMENT '成就名称',
  `achieve_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
  `is_rewarded` tinyint(1) DEFAULT '0' COMMENT '是否已发放奖励',
  `reward_points` int(11) DEFAULT '0' COMMENT '奖励积分',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_achievement` (`user_id`,`achievement_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_achievement_id` (`achievement_id`),
  KEY `idx_achieve_time` (`achieve_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户成就关联表';

奖品信息表 (jdwa_prize)

存储可用积分兑换的奖品信息。

CREATE TABLE `jdwa_prize` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '奖品ID',
  `name` varchar(100) NOT NULL COMMENT '奖品名称',
  `description` varchar(500) DEFAULT NULL COMMENT '奖品描述',
  `image_url` varchar(255) DEFAULT NULL COMMENT '图片URL',
  `points_needed` int(11) NOT NULL COMMENT '所需积分',
  `total_stock` int(11) DEFAULT '0' COMMENT '总库存',
  `remain_stock` int(11) DEFAULT '0' COMMENT '剩余库存',
  `exchange_count` int(11) DEFAULT '0' COMMENT '兑换次数',
  `prize_type` varchar(20) DEFAULT 'PHYSICAL' COMMENT '奖品类型：PHYSICAL-实物，VIRTUAL-虚拟，COUPON-优惠券',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-下架，1-上架',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_points_needed` (`points_needed`),
  KEY `idx_status` (`status`),
  KEY `idx_prize_type` (`prize_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖品信息表';

兑换记录表 (jdwa_exchange_record)

记录用户积分兑换奖品的情况。

CREATE TABLE `jdwa_exchange_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `prize_id` bigint(20) NOT NULL COMMENT '奖品ID',
  `prize_name` varchar(100) DEFAULT NULL COMMENT '奖品名称',
  `points_cost` int(11) NOT NULL COMMENT '消耗积分',
  `exchange_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '兑换时间',
  `exchange_code` varchar(50) DEFAULT NULL COMMENT '兑换码',
  `status` varchar(20) DEFAULT 'PENDING' COMMENT '状态：PENDING-待处理，PROCESSING-处理中，COMPLETED-已完成，CANCELLED-已取消',
  `contact_name` varchar(50) DEFAULT NULL COMMENT '联系人姓名',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `address` varchar(255) DEFAULT NULL COMMENT '收货地址',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_prize_id` (`prize_id`),
  KEY `idx_exchange_time` (`exchange_time`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='兑换记录表';

表关系设计

一对多关系

  • 一个用户(jdwa_user)可以有多条活动记录(jdwa_activity)
  • 一个用户(jdwa_user)可以有多条碳减排记录(jdwa_carbon_record)
  • 一个用户(jdwa_user)可以有多条积分记录(jdwa_point_record)
  • 一个用户(jdwa_user)可以有多个成就(jdwa_user_achievement)
  • 一个用户(jdwa_user)可以有多条兑换记录(jdwa_exchange_record)
  • 一个活动(jdwa_activity)可以产生多条碳减排记录(jdwa_carbon_record)
  • 一个活动(jdwa_activity)可以产生多条积分记录(jdwa_point_record)
  • 一个奖品(jdwa_prize)可以被多个用户兑换(jdwa_exchange_record)

多对多关系

  • 用户与成就之间是多对多关系，通过jdwa_user_achievement表关联

索引设计

主键索引

每个表都有自增主键id，作为主键索引

唯一索引

  • jdwa_user表：username字段设置唯一索引，确保用户名唯一
  • jdwa_user表：email字段设置唯一索引，确保邮箱唯一
  • jdwa_achievement表：name字段设置唯一索引，确保成就名称唯一
  • jdwa_user_achievement表：user_id和achievement_id组合字段设置唯一索引，确保用户不会重复获得同一成就

常用查询索引

  • 用户ID索引：在与用户相关的表中都建立了user_id字段的索引，便于按用户查询
  • 时间索引：create_time字段在多个表中建立索引，便于按时间范围查询
  • 类型索引：activity_type、record_type等类型字段建立索引，便于按类型查询
  • 状态索引：status字段建立索引，便于按状态筛选

数据字典

字段类型说明

  • bigint: 用于ID字段，64位整数
  • varchar: 可变长度字符串，用于名称、描述等文本字段
  • decimal: 定点数，用于金额、距离等需要精确计算的数值
  • int: 32位整数，用于计数、积分等整数值
  • tinyint: 8位整数，用于状态、标志等小范围整数值
  • datetime: 日期时间类型，精确到秒
  • date: 日期类型，不含时间部分
  • json: JSON数据类型，用于存储结构化数据

状态码说明

用户状态(jdwa_user.status)

  • 0: 禁用
  • 1: 启用

活动状态(jdwa_activity.status)

  • 0: 无效
  • 1: 有效

奖品状态(jdwa_prize.status)

  • 0: 下架
  • 1: 上架

兑换记录状态(jdwa_exchange_record.status)

  • PENDING: 待处理
  • PROCESSING: 处理中
  • COMPLETED: 已完成
  • CANCELLED: 已取消

类型码说明

活动类型(jdwa_activity.activity_type)

  • WALK: 步行
  • BIKE: 骑行
  • BUS: 公交
  • SUBWAY: 地铁

记录类型(jdwa_carbon_record.record_type)

  • ACTIVITY: 活动
  • TASK: 任务
  • OTHER: 其他

积分记录类型(jdwa_point_record.record_type)

  • EARN: 获取积分
  • CONSUME: 消费积分
  • EXPIRE: 积分过期

积分来源类型(jdwa_point_record.source_type)

  • ACTIVITY: 活动
  • ACHIEVEMENT: 成就
  • EXCHANGE: 兑换

成就类型(jdwa_achievement.achievement_type)

  • CARBON: 碳减排量
  • STEP: 步数
  • RUN_DISTANCE: 跑步距离
  • BIKE_DISTANCE: 骑行距离
  • ACTIVITY_COUNT: 活动次数
  • ACHIEVEMENT_COUNT: 成就数量

奖品类型(jdwa_prize.prize_type)

  • PHYSICAL: 实物奖品
  • VIRTUAL: 虚拟奖品
  • COUPON: 优惠券

初始化数据

初始用户数据

INSERT INTO `jdwa_user` (`username`, `password`, `nickname`, `email`, `status`, `carbon_points`, `total_points`)
VALUES 
('admin', 'e10adc3949ba59abbe56e057f20f883e', '管理员', 'admin@example.com', 1, 1000, 1000),
('test', 'e10adc3949ba59abbe56e057f20f883e', '测试用户', 'test@example.com', 1, 500, 500);

成就数据

INSERT INTO `jdwa_achievement` (`name`, `description`, `achievement_type`, `condition_value`, `reward_points`, `difficulty_level`)
VALUES 
('低碳新手', '累计减少碳排放1kg', 'CARBON', 1, 10, 1),
('低碳达人', '累计减少碳排放10kg', 'CARBON', 10, 50, 2),
('低碳专家', '累计减少碳排放100kg', 'CARBON', 100, 200, 3),
('健步如飞', '累计步行10000步', 'STEP', 10000, 20, 1),
('长跑健将', '累计跑步10公里', 'RUN_DISTANCE', 10000, 30, 2),
('单车骑士', '累计骑行50公里', 'BIKE_DISTANCE', 50000, 50, 2),
('绿色出行者', '累计使用公共交通10次', 'ACTIVITY_COUNT', 10, 20, 1),
('成就收集者', '获得5项成就', 'ACHIEVEMENT_COUNT', 5, 100, 2);

初始奖品数据

INSERT INTO `jdwa_prize` (`name`, `description`, `points_needed`, `total_stock`, `remain_stock`, `prize_type`, `status`)
VALUES 
('环保购物袋', '可折叠式环保购物袋', 100, 1000, 1000, 'PHYSICAL', 1),
('不锈钢吸管套装', '可重复使用的不锈钢吸管套装', 200, 500, 500, 'PHYSICAL', 1),
('电子书券', '价值10元的电子书券', 150, 2000, 2000, 'COUPON', 1),
('视频会员7天', '某视频平台7天会员', 300, 5000, 5000, 'VIRTUAL', 1);

数据库优化建议

查询优化

  1. 使用合适的索引：

    • 为常用的查询条件建立索引
    • 避免过多索引导致写入性能下降

  2. 避免全表扫描：

    • 使用带索引的字段作为查询条件
    • 对大表的查询增加合理的过滤条件

  3. 分页查询优化：

    • 使用ID范围查询替代OFFSET-LIMIT分页
    • 例如: WHERE id > last_id LIMIT n而不是OFFSET m LIMIT n

数据拆分策略

  1. 垂直分表：

    • 将较大的表按照字段用途拆分
    • 例如：将用户基本信息和用户详细信息拆分

  2. 水平分表：

    • 当单表数据量超过千万级时考虑水平分表
    • 按照用户ID哈希或时间范围进行分表

大表处理策略

  1. 历史数据归档：

    • 将一年前的活动记录、积分记录等历史数据归档到历史表
    • 保持活跃表的数据量在合理范围

  2. 统计数据汇总：

    • 使用定时任务定期汇总统计数据
    • 避免每次查询都进行大量计算

数据库维护计划

日常维护

  1. 数据备份：

    • 每日全量备份
    • 重要数据实时binlog备份

  2. 索引优化：

    • 定期检查索引使用情况
    • 删除不使用的索引，优化低效索引

  3. 定期清理：

    • 清理临时表和日志表
    • 归档长期不活跃的数据

性能监控

  1. 慢查询监控：

    • 设置慢查询日志，记录执行时间超过500ms的查询
    • 定期分析慢查询日志，优化问题SQL

  2. 资源使用监控：

    • 监控CPU、内存、磁盘IO使用情况
    • 监控连接数和线程状态

  3. 表状态监控：

    • 监控表大小和增长速度
    • 监控表的碎片率和需要优化的表

扩展性考虑

  1. 分库分表：

    • 设计考虑未来分库分表的可能性
    • 尽量使用分布式主键

  2. 读写分离：

    • 高峰期可通过读写分离减轻主库压力
    • 只读查询路由到从库执行

  3. 缓存策略：

    • 使用Redis缓存热点数据
    • 实现数据变更的缓存同步机制

数据安全与合规

  1. 敏感数据加密：

    • 密码使用单向加密存储
    • 敏感个人信息如手机号、邮箱考虑加密存储

  2. 数据访问控制：

    • 最小权限原则，数据库账号只赋予必要权限
    • 生产环境禁止使用root账号直接操作数据库

  3. 数据备份策略：

    • 多副本备份，异地容灾
    • 定期测试数据恢复流程

  4. 合规要求：

    • 符合个人信息保护法要求
    • 用户数据脱敏和匿名化处理

参考资源