应用实战——数据库设计时设计标识字段的一些思考【mysql】

关注
发布于: 2020 年 11 月 08 日
Tips:
本文转自笔者2017年的一篇博文，时至今天，笔者认为在某些特定的应用场景下，这种数据库标识字段的设计方式，依然有一定的参考价值，故在此贴出。
一、前言　　在很多应用场景中，通常需要给数据加上一些标识，以表明这条数据的某个特性，如：标识用户的性别、标识订单支付的渠道、标识商品的类型等等。在数据库设计时，通常我们会单独用一个字段来存储这些标识，如：可用gender字段来标识用户的性别,其值为“男”、“女”、“未知”这3种值中的一个;对于普通的具有有限固定的几个值的标识，这样自然没有什么问题，但是，对于一些同时具有多个属性且变化较大的就有些不合适了，比方说，标识智能手机的商品的类型时，其类型可标识为电子商品、娱乐商品、数码商品等多种类型，其具有的可分类属性众多且不固定；这些，就是本文需要讨论的：关于数据库设计时，给数据设计标识字段时的一些思考。
二、常见场景、问题与解决方法场景一：
问题与分析：
　　用户是任何系统中最为重要的组成部分之一，在设计存储用户信息时，性别是用户信息的重要组成部分，应该如何存储呢？性别有 “男”、“女”2种情况，如果某个用户没有具体性别数据的话，可以标识为“未知”，因此，每一个用户，性别有“男”、“女”、“未知”共3种情况，我们需要在数据表字段中需要存储这3种情况的一种(实际应用可能需要更多情况以满足具体业务，这里姑且以常见的3种为例)。
思考与方法：
　　方法一：在用户信息表中，添加一个性别字段，其数据类型为char，其值为“未知”、“男”、“女”中的一个，当需要使用该值时(前端展示、分析统计等)，直接取出使用即可。
　　方法二：在用户信息表中，添加一个字段，其数据类型为tinyint（一个字节），其值为 “0”、“1”、“2”，分别对应“未知”、“男”、“女”这3种情况，其中0对应的未知为默认值。当需要使用该值时，可针对不同的使用场景进行简单的转换，如前端需展示时可由数字转换成相应含义文字后再展示。
　　方法一的优点是语义明确、便于前端展示，不足之处有相对占用存储空间多、数据传输更耗流量等；方法二的优点是相对占用存储空间少、数据传输更省流量、一定程度上有利于数据统计分析，不足之处有展示时需要转换、数字语义需要约定等。笔者个人推荐使用方法二。(换个角度看，需要转换也带来了一定的灵活性，如：若某个业务需要展示性别为“保密”、“帅哥”、“靓女”，这样我们只需修改转换的地方即可，而不需修改数据库数据，这也有利于一个用户中心为不同的具体业务线提供服务)
﻿
场景二：
问题与分析：
　　电商平台通常会划分商品品类，如服饰类、食品类、数码类、书籍类等等，而有的商品可能具有多个商品品类属性，如智能手机，其既可划为数码类、又可划分为手机类、智能设备类，常见的场景是显示商品所属品类、修改商品的所属品类、查询某个品类下有哪些商品等，在这种情况下，该如何存储呢？
思考与方法：
　　假如平台的商品品类共有n种，那么，理论上某个商品的所属品类可能的组合就有[2^n-1]种情况，当然，实际上不会有这么多组合，但像用户选择兴趣爱好之类的场景可能的组合就多了，因此，上文场景一中的2种方法已不再适用。
　　可行的一个方法是：再引入一个品类关系表，用于存储商品品类与各商品的关系。这样的话，商品信息表本身用一个字符字段直接存储其所属品类信息，以满足基本需求，而操作某个品类下的商品的相关业务，则可通过品类关系表去做；一旦发生数据的变化，则同时维护这2个地方的数据。（事实上，通常的做法是，一件商品最多只能划分为有限的商品品类，一个人最多只能选择有限个兴趣爱好，如：一件商品最多只能所属3种品类，一个人最多只能选5个兴趣爱好）
﻿
场景三：
问题与分析：
　　在电商系统中，通常会通过各种优惠的方式来促销，如给用户发各种优惠券、积分抵扣等，用户提交订单时可以使用满足条件的各种优惠；那么，如何存储该订单具体使用了哪些优惠信息呢？
思考与方法：
　　与以上两种情况有所不同，以上的两种场景更多的业务场景是前端的展示与业务查询，而这种场景更多是标识优惠以计算用户实际所需支付金额，以及为后续业绩统计、制定促销计划、提高用户活跃度等提供数据依据。
　　这里我们举一个具体例子来逐步分析。
实例分析：
假设某平台为A平台，其平台当前可使用的优惠方式有以下几种：
　　用户下单时，只要是满足各优惠的使用条件，就可以叠加使用各种优惠，那么，数据库如何存储用户具体使用了哪些优惠呢？（实际各种促销优惠可能更多，尤其是各种优惠券，这里姑且以5种举例）
分析：
　　在这个业务场景中，用户下单时最多可以同时使用5种优惠抵扣方式，用户可能使用的优惠组合共有 2^5-1=31 种，在最终计算用户的订单实际需要支付的金额时，如何标识并存储用户到底使用了哪种优惠组合呢？
　　如果单独用一个普通标识字段来标识存储，实现起来是比较简单，但是其需要标识的组合种类实在有点多，不太利于编码与后续扩展，试想，如果新加了一种优惠类型，其需要添加多少种组合标识啊，且呈指数式爆长，这种方式显然不太合理。如果采用另外引入一张关联表的方式，专门用一张关联表来存储订单使用的优惠组合信息，每使用一种优惠就添加一条关联记录，相比单独使用普通字段标识，这在一定程度上减少了设置标识的繁琐性，增加了灵活性（每多使用一种优惠就添加一条关联记录），但是，同时也带来了另一些问题，其中主要问题是：新增一张关联表后，数据维护起来麻烦。在互联网场景下，数据量通常是非常大的，像订单数据一般都需要进行数据库sharding，以应对数据量暴涨后数据库的读写性能瓶颈，增加系统的水平扩展能力。因此，另外增加一张数据量是订单数据本身数据量几倍的关联表也显然不太合适。
　　那么，有没有一种方式既方便标识存储又方便扩展呢？
　　我们试着以一种“特殊标识位”的方式来实现，具体思路如下：
　　a、定义一个标识位 mask 用于标识存储优惠信息；
　　b、mask存储的值并不是直接存1、2、3之类的十进制数字，而是存储一个二进制数转化后的十进制数，这些1、2、3之类的优惠数字表示占二进制数的第几位（从右至左数）；
　　c、具体数据的存储、读取判断通过工具类转换进行。
　　例：
　　int 数据类型4个字节，共32位，除去符号位，可用于标识的位数有31位，即最多可以标识31种优惠情况，而如果是long数据类型的话，能标识的种类就更多了。
　　在以上共5种优惠方式场景中，可按如下标识存储：
　　
　　说明：若用户使用了优惠1,则使用二进制数 00000001 标识，若使用了优惠2，则使用二进制数 00000010 标识，存储到DB时，转换成对应十进制数分别对应1、2；若同时使用了优惠１、优惠2，则使用二进制数 00000011 标识，最终存储到DB的对应十进制数是3。其它优惠项，所占的二进制位依次类推。
　　代码示例如下：
DiscountEnum.java
package com.cndmss;
/**
 * @desc 优惠项枚举
 * @author xiaojiang
 *
 */
public enum DiscountEnum {
    BALANCE(1),     //账户余额
    INTEGRAL(2),    //平台积分
    COIN(3),        //平台币
    COUPON(4),      //满减卷
    FREE_POSTTAGE(5);   //免邮费   
    public int type;    //优惠类型
    private DiscountEnum(int type) {
        this.type = type;
    }
}
DiscountUtil.java
package com.cndmss;
/**
 * @desc 优惠项存储、判断工具类
 * @author xiaojiang
 *
 */
public class DiscountUtil {
    /**
     * 检测是否有使用过该优惠
     * @param type 优惠类型值
     * @param value 存储值
     * @return boolean 判断结果 [true:是  |false:否]
     */
    public static boolean isUseDiscount(int type,int value){
        return (value & (1<< (type-1))) > 0;
    }
    /**
     * 设置使用过的优惠
     * @param type 优惠类型值
     * @param value 存储值
     * @return int 添加优惠类型后的存储值
     */
    public static int setDiscountValue(int type,int value){
        return (value | (1 << (type-1)));
    }
}
MainTest.java
package com.test;
import com.cndmss.DiscountEnum;
import com.cndmss.DiscountUtil;
/**
 * @desc 测试类
 *
 * @author xiaojiang
 *
 */
public class MainTest {
    public static void main(String[] args) {
        System.out.println("-----设置优惠标识值----");
        int n = 0;  //标识位初始值（十进制 ）
        int v1 = DiscountUtil.setDiscountValue(DiscountEnum.BALANCE.type, n);
        int v2 = DiscountUtil.setDiscountValue(DiscountEnum.INTEGRAL.type, v1);
        int v3 = DiscountUtil.setDiscountValue(DiscountEnum.COIN.type, v2);
        int v4 = DiscountUtil.setDiscountValue(DiscountEnum.COUPON.type, v3);
        int v5 = DiscountUtil.setDiscountValue(DiscountEnum.FREE_POSTTAGE.type, v4);
        System.out.println("使用的优惠项" + "\t\t\t|" + "二进制数" + "\t\t|" + "十进制数(最终存储的值)");
        System.out.println("v1:优惠1" + "\t\t\t\t|" + toTwo(v1) + "\t|" + v1);
        System.out.println("v2:优惠1、优惠2" + "\t\t\t|" + toTwo(v2) + "\t|" + v2);
        System.out.println("v3:优惠1、优惠2、优惠3" + "\t\t|" + toTwo(v3) + "\t|" + v3);
        System.out.println("v4:优惠1、优惠2、优惠3、优惠4" + "\t\t|" + toTwo(v4) + "\t|" + v4);
        System.out.println("v5:优惠1、优惠2、优惠3、优惠4、优惠5" + "\t|" + toTwo(v5) + "\t|" + v5);
        System.out.println("-----是否包含优惠判断-----");
        System.out.println("v1:");
        System.out.println("v1是否包含优惠BALANCE: " + DiscountUtil.isUseDiscount(DiscountEnum.BALANCE.type, v1));
        System.out.println("v1是否包含优惠INTEGRAL: " + DiscountUtil.isUseDiscount(DiscountEnum.INTEGRAL.type, v1));
        System.out.println("v1是否包含优惠COIN: " + DiscountUtil.isUseDiscount(DiscountEnum.COIN.type, v1));
        System.out.println("v1是否包含优惠COUPON: " + DiscountUtil.isUseDiscount(DiscountEnum.COUPON.type, v1));
        System.out.println("v1是否包含优惠FREE_POSTTAGE: " + DiscountUtil.isUseDiscount(DiscountEnum.FREE_POSTTAGE.type, v1));
        System.out.println("v2:");
        System.out.println("v2是否包含优惠BALANCE: " + DiscountUtil.isUseDiscount(DiscountEnum.BALANCE.type, v2));
        System.out.println("v2是否包含优惠INTEGRAL: " + DiscountUtil.isUseDiscount(DiscountEnum.INTEGRAL.type, v2));
        System.out.println("v2是否包含优惠COIN: " + DiscountUtil.isUseDiscount(DiscountEnum.COIN.type, v2));
        System.out.println("v2是否包含优惠COUPON: " + DiscountUtil.isUseDiscount(DiscountEnum.COUPON.type, v2));
        System.out.println("v2是否包含优惠FREE_POSTTAGE: " + DiscountUtil.isUseDiscount(DiscountEnum.FREE_POSTTAGE.type, v2));
        System.out.println("v3:");
        System.out.println("v3是否包含优惠BALANCE: " + DiscountUtil.isUseDiscount(DiscountEnum.BALANCE.type, v3));
        System.out.println("v3是否包含优惠INTEGRAL: " + DiscountUtil.isUseDiscount(DiscountEnum.INTEGRAL.type, v3));
        System.out.println("v3是否包含优惠COIN: " + DiscountUtil.isUseDiscount(DiscountEnum.COIN.type, v3));
        System.out.println("v3是否包含优惠COUPON: " + DiscountUtil.isUseDiscount(DiscountEnum.COUPON.type, v3));
        System.out.println("v3是否包含优惠FREE_POSTTAGE: " + DiscountUtil.isUseDiscount(DiscountEnum.FREE_POSTTAGE.type, v3));
        System.out.println("v4:");
        System.out.println("v4是否包含优惠BALANCE: " + DiscountUtil.isUseDiscount(DiscountEnum.BALANCE.type, v4));
        System.out.println("v4是否包含优惠INTEGRAL: " + DiscountUtil.isUseDiscount(DiscountEnum.INTEGRAL.type, v4));
        System.out.println("v4是否包含优惠COIN: " + DiscountUtil.isUseDiscount(DiscountEnum.COIN.type, v4));
        System.out.println("v4是否包含优惠COUPON: " + DiscountUtil.isUseDiscount(DiscountEnum.COUPON.type, v4));
        System.out.println("v4是否包含优惠FREE_POSTTAGE: " + DiscountUtil.isUseDiscount(DiscountEnum.FREE_POSTTAGE.type, v4));
        System.out.println("v5:");
        System.out.println("v5是否包含优惠BALANCE: " + DiscountUtil.isUseDiscount(DiscountEnum.BALANCE.type, v5));
        System.out.println("v5是否包含优惠INTEGRAL: " + DiscountUtil.isUseDiscount(DiscountEnum.INTEGRAL.type, v5));
        System.out.println("v5是否包含优惠COIN: " + DiscountUtil.isUseDiscount(DiscountEnum.COIN.type, v5));
        System.out.println("v5是否包含优惠COUPON: " + DiscountUtil.isUseDiscount(DiscountEnum.COUPON.type, v5));
        System.out.println("v5是否包含优惠FREE_POSTTAGE: " + DiscountUtil.isUseDiscount(DiscountEnum.FREE_POSTTAGE.type, v5));
    }
    /**
     * 将十进制数转成二进制数
     * @param n 待转换的十进制数
     * @return String 装换后的二进制数(8位，不足8位的补齐8位) 
     */
    private static String toTwo(int n){
        String s = Integer.toBinaryString(n);
        return String.format("%08d",Integer.parseInt(s));
    }
}
输出：
-----设置优惠标识值----
使用的优惠项          |二进制数       |十进制数(最终存储的值)
v1:优惠1              |00000001   |1
v2:优惠1、优惠2          |00000011   |3
v3:优惠1、优惠2、优惠3      |00000111   |7
v4:优惠1、优惠2、优惠3、优惠4      |00001111   |15
v5:优惠1、优惠2、优惠3、优惠4、优惠5  |00011111   |31
-----是否包含优惠判断-----
v1:
v1是否包含优惠BALANCE: true
v1是否包含优惠INTEGRAL: false
v1是否包含优惠COIN: false
v1是否包含优惠COUPON: false
v1是否包含优惠FREE_POSTTAGE: false
v2:
v2是否包含优惠BALANCE: true
v2是否包含优惠INTEGRAL: true
v2是否包含优惠COIN: false
v2是否包含优惠COUPON: false
v2是否包含优惠FREE_POSTTAGE: false
v3:
v3是否包含优惠BALANCE: true
v3是否包含优惠INTEGRAL: true
v3是否包含优惠COIN: true
v3是否包含优惠COUPON: false
v3是否包含优惠FREE_POSTTAGE: false
v4:
v4是否包含优惠BALANCE: true
v4是否包含优惠INTEGRAL: true
v4是否包含优惠COIN: true
v4是否包含优惠COUPON: true
v4是否包含优惠FREE_POSTTAGE: false
v5:
v5是否包含优惠BALANCE: true
v5是否包含优惠INTEGRAL: true
v5是否包含优惠COIN: true
v5是否包含优惠COUPON: true
v5是否包含优惠FREE_POSTTAGE: true
　　代码说明：优惠的种类，还是定义一个普通的枚举类，但是该枚举类的优惠种类值，并不是最最终存储到DB的值，而是表示占二进制数的第几位（从右至左数，从1开始）；当需要存储优惠种类到DB中，或者从DB中查询对应的优惠种类时，通过工具类进行运算转换。
　　工具类的运算，其实就是通过位运算来实现：设值时，通过位移、位或来设定所占二进制位数，返回设置占位后的十进制数；判断时，则通过位移、位与来判断优惠项应占位是否有占位，返回判断结果。（关于Java位运算，非本文重点，此处不再详述，有需要的同学可以另行查阅相关资料）
　　通过以上这种“特殊标识位”的方式，在一定程度上就可以达到方便标识存储又方便扩展的目的。
三、特殊标识位优缺点　　由以上分析可知，不同场景下可采用不同的标识方式，其也有相应的优缺点，此处主要说明下特殊标识位方式的优缺点。
　　优点：
　　1、方便标识存储，一个字段就可以标识多种业务场景。
　　2、方便扩展，易于维护；当业务场景迅速扩展时，这种方式可以方便的标识新增的业务场景，数据也易于维护。要知道，在互联网场景下，业务的变化是非常快的，新加字段并不是那么方便。
　　缺点：
　　1、数据的存储、查询需要转换，不够直观；相对普通的标识方式，没接触过的人需要一点时间理解这种使用特殊标识位的方式。
　　2、DB数据查询时，稍显繁琐；如，假如原来订单表[t_order]使用字段discount_type直接标识优惠种类，那么，查询使用了某种优惠的订单，SQL语句为：
SELECT * FROM t_order WHERE discount_type = value;
而改用mask字段这种特殊标识位的方式后，SQL语句为：
SELECT * FROM t_order WHERE (mask & (1<<(value-1))) > 0;
显然，后一种SQL要复杂不少，这在数据量较大的情况下，这并不是一件很好的事情，好在这种查询频率一般不会太高。（SQL中的 value 表示待查询的优惠种类值）
四、总结　　1、使用标识位来标识对象信息时，可结合具体业务场景，选择相应的标识存储方案。
　　2、特殊标识位的方式，除了本文提到的标识订单优惠种类，还可用于标识“用户的角色信息”、“商品品类信息”、“参与的活动、奖励”等业务场景。
　　3、数据库设计，需结合具体业务需求具体分析，灵活变通，找到适合本身业务的一种实现方式，切忌生搬硬套；尽量在满足当前业务场景的前提下，考虑满足未来短期内可能的扩展。
﻿