活动数据表设计

活动数据表设计（含捐赠、点赞、评论、收藏功能）

一、设计原则

1. 模块化拆分：按 “活动基础信息 - 互动行为 - 捐赠业务” 拆分表结构，避免单表冗余，提升查询效率。
2. 关联完整性：通过外键建立表间关联，确保数据溯源（如某条评论 / 点赞归属哪个活动 / 用户）。
3. 状态可追溯：用枚举值定义核心状态（如活动状态、捐赠状态），支持业务流程追踪与统计。
4. 扩展性预留：通过ext_json字段存储非结构化数据（如活动标签、捐赠附加信息），适配后续功能迭代。
5. 性能优化：针对高频查询场景（如活动列表、互动数据统计）设计索引，减少全表扫描。

二、完整表结构设计

1. 基础关联表（依赖表）

（1）用户表（user，已存在，简化版）

字段名	数据类型	长度	主键 / 外键	非空	说明
user_id	BIGINT	-	主键	✅	用户唯一 ID（雪花 ID）
username	VARCHAR	50	-	✅	用户名（用于互动展示）
avatar_url	VARCHAR	500	-	❌	用户头像 URL（可选）
phone	VARCHAR	20	-	❌	手机号（用于捐赠联系，可选）

2. 活动核心表

（1）活动基础信息表（activity）

存储活动核心配置，是所有关联表的 “主表”，管理活动生命周期。

字段名	数据类型	长度	主键 / 外键	非空	说明
activity_id	BIGINT	-	主键	✅	活动唯一 ID（雪花 ID）
title	VARCHAR	200	-	✅	活动标题（如 “流浪动物救助公益捐赠活动”）
cover_img_url	VARCHAR	500	-	✅	活动封面图 URL（关联 OSS/CDN）
intro	TEXT	-	-	✅	活动简介（短描述，用于列表页展示）
detail	LONGTEXT	-	-	✅	活动详情（富文本，含捐赠用途、时间安排等）
start_time	DATETIME	-	-	✅	活动开始时间（互动 / 捐赠开放时间）
end_time	DATETIME	-	-	✅	活动结束时间（互动 / 捐赠关闭时间）
target_amount	DECIMAL(16,2)	-	-	✅	目标捐赠金额（如 10000.00 元，用于进度展示）
current_amount	DECIMAL(16,2)	-	-	✅	当前已筹金额（实时更新，关联donation_record求和）
status	TINYINT	-	-	✅	活动状态：0 - 草稿、1 - 进行中、2 - 已结束、3 - 已下架（枚举值）
creator_id	BIGINT	-	外键	✅	活动创建者 ID（关联user.user_id，如管理员 / 公益组织）
ext_json	JSON	-	-	❌	扩展字段（如活动标签 ["公益","救助"]、联系电话等）
created_at	DATETIME	-	-	✅	活动创建时间（自动填充）
updated_at	DATETIME	-	-	✅	活动更新时间（自动更新，如修改状态 / 金额时）

索引：

• 普通索引：idx_creator_id（creator_id）→ 快速查询 “某创建者的所有活动”
• 联合索引：idx_status_time（status+start_time）→ 按状态 + 时间筛选活动列表（如 “进行中的活动”）

3. 捐赠业务表

（1）捐赠记录表（donation_record）

存储每笔捐赠的核心信息，关联 “活动 - 用户”，支持捐赠追踪与对账。

字段名	数据类型	长度	主键 / 外键	非空	说明
donation_id	BIGINT	-	主键	✅	捐赠记录唯一 ID（雪花 ID）
activity_id	BIGINT	-	外键	✅	关联活动 ID（activity.activity_id）
user_id	BIGINT	-	外键	✅	捐赠用户 ID（user.user_id，匿名捐赠填 0）
donor_name	VARCHAR	50	-	❌	捐赠者姓名（匿名填 “爱心人士”，user_id=0时必填）
donor_phone	VARCHAR	20	-	❌	捐赠者电话（用于对账 / 通知，可选）
amount	DECIMAL(16,2)	-	-	✅	捐赠金额（精确到分，如 50.00 元）
message	VARCHAR	500	-	❌	捐赠留言（如 “希望小动物健康”，可选）
pay_type	TINYINT	-	-	✅	支付方式：1 - 微信、2 - 支付宝、3 - 银行卡、4 - 线下转账（枚举值）
pay_order_no	VARCHAR	64	-	❌	支付订单号（关联第三方支付平台，用于对账）
status	TINYINT	-	-	✅	捐赠状态：0 - 待支付、1 - 已完成、2 - 已退款、3 - 支付失败（枚举值）
pay_time	DATETIME	-	-	❌	支付完成时间（status=1时必填）
refund_time	DATETIME	-	-	❌	退款时间（status=2时必填）
ext_json	JSON	-	-	❌	扩展字段（如捐赠凭证 URL、线下转账备注）
created_at	DATETIME	-	-	✅	捐赠记录创建时间（发起捐赠时间）
updated_at	DATETIME	-	-	✅	捐赠记录更新时间（支付 / 退款时更新）

索引：

• 联合索引：idx_activity_user（activity_id+user_id）→ 快速查询 “某用户在某活动的捐赠记录”
• 普通索引：idx_status（status）→ 筛选 “已完成”“待支付” 等状态的捐赠

4. 互动功能表

（1）活动点赞表（activity_like）

存储用户对活动的点赞记录，避免重复点赞，支持 “取消点赞” 逻辑。

字段名	数据类型	长度	主键 / 外键	非空	说明
like_id	BIGINT	-	主键	✅	点赞记录唯一 ID（雪花 ID）
activity_id	BIGINT	-	外键	✅	关联活动 ID（activity.activity_id）
user_id	BIGINT	-	外键	✅	点赞用户 ID（user.user_id）
created_at	DATETIME	-	-	✅	点赞时间

索引：

• 联合唯一索引：uk_activity_user（activity_id+user_id）→ 防止同一用户重复点赞，快速判断 “是否已点赞”

（2）活动评论表（activity_comment）

存储用户对活动的评论，支持 “评论回复”（嵌套评论），关联用户信息用于展示。

字段名	数据类型	长度	主键 / 外键	非空	说明
comment_id	BIGINT	-	主键	✅	评论唯一 ID（雪花 ID）
activity_id	BIGINT	-	外键	✅	关联活动 ID（activity.activity_id）
user_id	BIGINT	-	外键	✅	评论用户 ID（user.user_id，谁发的评论）
parent_id	BIGINT	-	外键	❌	父评论 ID：NULL = 直接评论活动，非 NULL = 回复某条评论（关联本表comment_id）
to_user_id	BIGINT	-	外键	❌	被回复用户 ID（user.user_id，如 “回复 @张三”，parent_id非空时必填）
content	VARCHAR	500	-	✅	评论内容（限制 500 字以内）
is_deleted	TINYINT	-	-	✅	逻辑删除：0 - 正常、1 - 已删除（避免物理删除数据）
created_at	DATETIME	-	-	✅	评论时间
updated_at	DATETIME	-	-	✅	评论更新时间（删除时更新）

索引：

• 普通索引：idx_activity_id（activity_id）→ 快速查询 “某活动的所有评论”
• 普通索引：idx_parent_id（parent_id）→ 快速查询 “某条评论的所有回复”

（3）活动收藏表（activity_collection）

存储用户对活动的收藏记录，支持 “取消收藏”，用于 “我的收藏” 列表查询。

字段名	数据类型	长度	主键 / 外键	非空	说明
collection_id	BIGINT	-	主键	✅	收藏记录唯一 ID（雪花 ID）
activity_id	BIGINT	-	外键	✅	关联活动 ID（activity.activity_id）
user_id	BIGINT	-	外键	✅	收藏用户 ID（user.user_id）
created_at	DATETIME	-	-	✅	收藏时间

索引：

• 联合唯一索引：uk_activity_user（activity_id+user_id）→ 防止同一用户重复收藏，快速判断 “是否已收藏”
• 普通索引：idx_user_id（user_id）→ 快速查询 “某用户的所有收藏活动”

三、核心业务 SQL 示例

1. 活动详情查询（含捐赠进度、互动统计）

sql

SELECT 
    a.activity_id, a.title, a.cover_img_url, a.intro,
    a.target_amount, a.current_amount,
    -- 计算捐赠进度（百分比）
    ROUND((a.current_amount / a.target_amount) * 100, 2) AS donate_progress,
    -- 统计互动数据
    (SELECT COUNT(*) FROM activity_like al WHERE al.activity_id = a.activity_id) AS like_count,
    (SELECT COUNT(*) FROM activity_comment ac WHERE ac.activity_id = a.activity_id AND ac.is_deleted=0) AS comment_count,
    (SELECT COUNT(*) FROM activity_collection aco WHERE aco.activity_id = a.activity_id) AS collect_count,
    a.status, a.start_time, a.end_time
FROM activity a
WHERE a.activity_id = 123; -- 活动ID

 

 

2. 点赞 / 取消点赞（防止重复点赞）

-- 1. 点赞（不存在则插入）
INSERT INTO activity_like (activity_id, user_id, created_at)
SELECT 123, 456, NOW()
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM activity_like 
    WHERE activity_id=123 AND user_id=456
);

-- 2. 取消点赞（逻辑删除或物理删除，推荐物理删除）
DELETE FROM activity_like
WHERE activity_id=123 AND user_id=456;

3. 捐赠成功后更新活动金额（事务保证原子性）

BEGIN; -- 开启事务

-- 1. 插入捐赠记录
INSERT INTO donation_record (
    activity_id, user_id, donor_name, amount, 
    pay_type, status, pay_time, created_at
)
VALUES (123, 456, '张三', 100.00, 1, 1, NOW(), NOW());

-- 2. 更新活动当前已筹金额
UPDATE activity
SET current_amount = current_amount + 100.00, updated_at = NOW()
WHERE activity_id = 123;

COMMIT; -- 提交事务（失败则ROLLBACK）

4. 查询活动评论列表（含回复层级）

-- 查某活动的所有评论（主评论+回复，按时间倒序）
SELECT 
    ac.comment_id, ac.user_id, u.username, u.avatar_url,
    ac.content, ac.parent_id, ac.created_at,
    -- 被回复用户信息（parent_id非空时显示）
    tu.username AS to_username
FROM activity_comment ac
LEFT JOIN `user` u ON ac.user_id = u.user_id
LEFT JOIN `user` tu ON ac.to_user_id = tu.user_id
WHERE ac.activity_id = 123 AND ac.is_deleted=0
ORDER BY ac.parent_id ASC, ac.created_at DESC;

五、优化建议

1. 互动数据缓存：对 “活动点赞数、评论数、收藏数” 等高频查询数据，用 Redis 缓存（如key=activity:like:123，值为点赞数），定时同步到数据库，减少数据库压力。
2. 评论分页：当活动评论量较大时，用LIMIT + OFFSET或 “游标分页”（按comment_id倒序）实现分页查询，避免一次性加载所有评论。
3. 捐赠统计定时任务：若current_amount实时求和压力大，可定时（如每 5 分钟）执行求和 SQL 更新activity.current_amount，牺牲少量实时性换取性能。
4. 索引维护：定期清理无效索引（如长期未使用的索引），避免索引过多导致写入性能下降。