3. 数据库表结构设计：活动主表、规则表、参与记录表、奖品表的设计与SQL脚本

数据库设计，说白了就是给营销活动搭骨架。骨架搭歪了，后面写再多代码也白搭。我见过太多项目，活动上线后才发现表结构不合理，改起来那叫一个痛苦。今天咱们就把这几张核心表聊透。

3.1 活动主表：一切业务的起点

活动主表是整个模块的根。每创建一个活动，就往这张表里插一条记录。我个人习惯把活动的基本属性都放在这里，比如活动名称、时间范围、状态等。

核心字段设计思路：

  • activity_id：主键，用自增ID还是UUID？我建议用自增ID，查询快，排序方便。如果担心分库分表，可以后期用雪花算法替换。
  • activity_name：活动名称，varchar(100)就够了，别太长。
  • activity_type：活动类型，比如1-满减、2-折扣、3-抽奖。用tinyint存，别用varchar存中文，性能差。
  • start_time / end_time：活动起止时间。这里有个坑——一定要用datetime类型，别用timestamp。timestamp有2038年问题，你想想看，万一活动要跨世纪呢？
  • status：活动状态，0-草稿、1-进行中、2-已结束、3-已下架。用tinyint，别用枚举。
-- 活动主表
CREATE TABLE `t_activity` (
  `activity_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
  `activity_name` varchar(100) NOT NULL COMMENT '活动名称',
  `activity_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '活动类型：1-满减 2-折扣 3-抽奖',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-草稿 1-进行中 2-已结束 3-已下架',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`activity_id`),
  KEY `idx_status` (`status`),
  KEY `idx_time` (`start_time`, `end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动主表';

避坑指南：我曾经接手过一个项目，活动主表没有加时间索引。结果运营查「当前进行中的活动」时，全表扫描，数据库直接被打挂了。所以，start_time和end_time一定要建联合索引。

3.2 规则表：活动的灵魂

规则表决定了活动怎么玩。比如满100减20，还是打8折，还是抽奖中iPhone。规则表的设计，我建议用「规则模板 + 规则参数」的方式，这样扩展性最好。

为什么会这样？因为营销活动的规则千变万化。今天满减，明天满赠，后天可能就变成「买一送一」了。如果每改一次规则就加字段，那表结构迟早变成一锅粥。

-- 规则表
CREATE TABLE `t_rule` (
  `rule_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '规则ID',
  `activity_id` bigint(20) NOT NULL COMMENT '关联活动ID',
  `rule_type` tinyint(4) NOT NULL COMMENT '规则类型：1-满减 2-折扣 3-抽奖',
  `rule_name` varchar(50) NOT NULL COMMENT '规则名称',
  `rule_params` json NOT NULL COMMENT '规则参数（JSON格式）',
  `priority` tinyint(4) NOT NULL DEFAULT '0' COMMENT '优先级，数值越小越优先',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`rule_id`),
  KEY `idx_activity` (`activity_id`),
  CONSTRAINT `fk_rule_activity` FOREIGN KEY (`activity_id`) REFERENCES `t_activity` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规则表';

rule_params字段的JSON示例：

  • 满减规则：{"min_amount": 100, "discount": 20}
  • 折扣规则：{"rate": 0.8, "max_discount": 50}
  • 抽奖规则：{"draw_count": 1, "prize_pool_id": 1}

注意：JSON字段虽然灵活，但查询时不能走索引。如果规则参数需要频繁查询，建议把常用字段抽出来单独建列。我一般只在「参数不常查」或「参数结构多变」时才用JSON。

3.3 参与记录表：活动的流水账

参与记录表，说白了就是谁在什么时间参加了什么活动。这张表的数据量会非常大，一天几百万条很正常。所以设计时一定要考虑性能和归档策略。

我记得有个项目，上线第一天参与记录表就爆了——因为没加索引，查询用户参与历史时慢得像蜗牛。后来加了联合索引才救回来。

-- 参与记录表
CREATE TABLE `t_participation` (
  `participation_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '参与记录ID',
  `activity_id` bigint(20) NOT NULL COMMENT '活动ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `rule_id` bigint(20) DEFAULT NULL COMMENT '触发的规则ID',
  `participation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '参与时间',
  `result` tinyint(4) NOT NULL DEFAULT '0' COMMENT '参与结果：0-未中奖 1-中奖',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`participation_id`),
  KEY `idx_user_activity` (`user_id`, `activity_id`),
  KEY `idx_activity_time` (`activity_id`, `participation_time`),
  KEY `idx_user_time` (`user_id`, `participation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='参与记录表';

性能优化建议：

  • 按时间分区：比如按月分区，查询历史数据时只扫一个分区，快很多。
  • 归档策略：超过3个月的数据，迁移到历史表。我习惯用定时任务每天凌晨跑一次。
  • 索引别建太多：写多读少的表，索引多了影响写入性能。保留最常用的2-3个索引就够了。

3.4 奖品表：活动的诱惑

奖品表管理的是「活动里有什么奖品」。注意，这里说的是奖品池，不是用户实际领到的奖品。用户领到的奖品，应该单独建一张「奖品发放表」。

你想想看，一个抽奖活动可能有100个一等奖、500个二等奖。如果每个奖品都单独一条记录，那表会膨胀得很快。所以我建议用「库存模式」——每个奖品类型一条记录，用库存字段控制剩余数量。

-- 奖品表
CREATE TABLE `t_prize` (
  `prize_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '奖品ID',
  `activity_id` bigint(20) NOT NULL COMMENT '关联活动ID',
  `prize_name` varchar(100) NOT NULL COMMENT '奖品名称',
  `prize_type` tinyint(4) NOT NULL COMMENT '奖品类型：1-实物 2-虚拟券 3-积分',
  `total_count` int(11) NOT NULL DEFAULT '0' COMMENT '总库存',
  `remain_count` int(11) NOT NULL DEFAULT '0' COMMENT '剩余库存',
  `probability` decimal(5,4) NOT NULL DEFAULT '0.0000' COMMENT '中奖概率（0.0000~1.0000）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`prize_id`),
  KEY `idx_activity` (`activity_id`),
  CONSTRAINT `fk_prize_activity` FOREIGN KEY (`activity_id`) REFERENCES `t_activity` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖品表';

中奖概率的计算方式：

我习惯用「概率池」的方式。比如一等奖概率0.01，二等奖概率0.09，三等奖概率0.9。所有奖品的概率加起来必须等于1。抽奖时，生成一个0~1的随机数，落在哪个区间就中哪个奖。

这样做的好处是：概率调整灵活，而且不会出现「总概率不等于1」的bug。

并发安全：奖品库存的扣减一定要用乐观锁或悲观锁。我曾经见过一个项目，高并发下奖品多发了几百份，运营差点崩溃。推荐用UPDATE t_prize SET remain_count = remain_count - 1 WHERE prize_id = ? AND remain_count > 0，利用数据库的行锁保证原子性。

3.5 表关系总结

这几张表的关系其实很简单：

  • 活动主表是老大，一条活动对应多条规则、多条参与记录、多个奖品。
  • 规则表和奖品表都通过activity_id关联到活动主表。
  • 参与记录表记录了用户和活动的交互，同时关联到触发的规则和获得的奖品。

嗯，这里要注意：不要为了「省事」把规则和奖品都塞到活动主表里。我见过有人把规则参数直接存成活动表的一个字段，结果后来规则一复杂，代码里全是JSON解析，维护起来想死的心都有。

好了，表结构设计就聊到这儿。下一章咱们开始写业务逻辑——怎么用这些表实现一个完整的营销活动。

← 上一章 📖 返回目录 下一章 →