关于积分的Db设计之一

今天来讲一个业务场景及其对应的解决方案：用户积分！肯定不陌生，毕竟现在很多电商系统都提供积分功能。简单说一下我手里的电商项目中积分的相关规则：

1. 下单会根据订单金额，奖励对应比例的积分
2. 订单结算时，用户可以使用积分来按照比例抵扣现金消费
3. 积分存在有效期，过期作废
4. 用户可以查看自身的积分流水
5. 用户可以查看当前可用积分总数

极其常规的业务逻辑，对吧~~

这里延伸几点规则，算是基于上述规则的合理推导：

1. 结算时，若用户选择使用积分，需要优先使用快要到期的积分部分
2. 退款时，若用户之前有使用积分，需要按照积分的有效期，优先退还到期时间晚的积分部分
3. 有积分快过期时给用户足够的提醒
4. 允许用户看到累积过期的积分总数

那么接下来，如果要你来设计满足上面这些需求点的db结构，你会怎么做呢？

经过我们开发组的一轮商讨，给出了下面的一个表设计方案：

CREATE TABLE points(
    id INT NOT NULL AUTO_INCREMENT  COMMENT '主键' ,
    user_id INT NOT NULL   COMMENT '用户id' ,
    total_point INT NOT NULL  DEFAULT 0 COMMENT '积分总值' ,
    expire INT NOT NULL   COMMENT '到期时间戳' ,
    cost_point INT NOT NULL  DEFAULT 0 COMMENT '消耗积分值' ,
    used_detail TEXT NOT NULL  DEFAULT [] COMMENT '使用占比明细' ,
    order_id INT NOT NULL  DEFAULT 0 COMMENT '订单id' ,
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

单看表结构，无法直观的理解到这种设计的用意，我下面拿一个实际场景来举例吧~~

假设目前就只有一个客户，他通过下单，已经挣到了200积分，那么在db中会保存对应的记录：

注意：我把expire字段写成容易阅读的时间格式了，实际存储的应该是unix时间戳

目前应该不需要额外的解释，除了 cost_point ， used_detail 外，其它字段的含义都是很直观的。

接下来，我们的客户又要开始shopping了，不过这次他结账的时候想要使用积分来抵扣现金了，那么db中会如何记录呢？

看到了么？这个时候， used_detail 里保存了这次使用积分的细节，以json结构字符串保存在表中。这个json结构也比较明了吧：

• id: 指向对应的积分记录
• val: 标识消耗了对应积分记录中的多少积分

同时，对应积分记录的 cost_point 字段也会出现对应的值。

这么设计是否能很好的满足我们的业务需要呢？我们来拿前面提到的业务指标来核查一下是否都满足。其中一些简单的业务规则咱们就不讨论了，我们就拿其中比较重要的一些来检查：

1. 积分存在有效期，过期作废
2. 用户可以查看当前可用积分总数
3. 允许用户看到累积过期的积分总数
4. 结算时，若用户选择使用积分，需要优先使用快要到期的积分部分
5. 退款时，若用户之前有使用积分，需要按照积分的有效期，优先退还到期时间晚的积分部分

第1点，靠 expire 字段可以很容易保证，只需要检查一下 expire 和当前时间对比一下，就可知道哪些积分已经过期了；

第2、3、4点，靠 expire ， total_point 和 cost_point 三个字段就可以实时计算出值，不过感觉不是那么直观，相当于每次都需要做实时聚合操作；

第5点，可以靠 used_detail 字段中的明细信息，在退款时保证正确的返还合规的积分。

从上面的分析我们应该得到了初步的结论，这么设计表结构，有它精妙的地方，也有不足的地方。可以看出，我们在设计这个结构之初，过于关注退款所带来的限制条件，而忽略了一个细节： 相比其它，退款发生的频率应该是最低的 。这意味着，在相对频繁的场景中（如可用积分总数），我们不得不进行复杂繁琐的计算。

那么我们来试着在第4点的时候，把程序要做的步骤列一下：

SELECT * FROM points WHERE user_id=1 AND expire > NOW AND total_point != cost_point ORDER BY expire asc FOR UPDATE

除了步骤多外，事务中的锁也会成为一个问题（当然同一个用户并发下单的可能性有待商讨）。但不可否认的是这么设计表结构是会导致这个场景中数据的使用是比较“麻烦”的~~不知道读到这里，你是否觉得这个表结构是可以接受的呢？是否觉得使用数据时并不是很复杂呢？

那有没有更好一些的设计方案呢？