数据库设计之多选状态设计

一、引言

​ 某个商家支持多种会员卡打折，如金卡、银卡、其他卡，多选状态。通常的做法是在商家表中建立三个字段，

gold_card(tinyint类型，0，支持；1，不支持)，
silver_card(tinyint类型，0，支持；1，不支持)，
other_card(tinyint类型，0，支持；1，不支持)

这种用多个字段满足多种状态方案的确满足了项目需求，但是存在两个缺点，

（1）增加了数据字段冗余和增加存储空间；

（2）可拓展性差，比如增加一种钻石卡，只能在数据库中，增加一个字段，这样多项目改动是较大的。

基于此问题，本文探索多选字段的设计。

二、多选字段设计解决方案

（1）字符串表达

​ 这种方法就是将多个状态标识字段通过字符串拼接表示成一个字段。如：

card_status(varchar,1表示金卡；2表示银卡；3表示其他卡；）
-- 字符串拼接表示多选，如1,2表示支持金卡，银卡；1,2,3表示都支持。。。

​ 优点：

（1）消除冗余状态字段；

（2）增加可拓展性，增加新的会员卡类别时，不需增加新的字段，增加一种新字符就行。

​ 缺点：

（1）查询效率低，想查询支持银卡的商家，首先遍历整个数据库，然后对状态字符串进行分隔，最后才能查询；

查询用FIND_IN_SET(str,strlist)函数；

（2）字符串类型的字段在查询效率和存储空间上不如整型字段。

（2）“位”表示

​ 二进制的“位”本来就有表示状态的作用。在这里，采用不同的位表示卡支持状态，如：

card_status(int）
1(0001)：表示支持金卡；
2（0010）：表示支持银卡；
4（0100）：表示支持其他卡；
3（0001+0010=0011）：表示支持金卡、银卡；
5（0001+0100=0101）：表示支持金卡、其他卡；
7(0001+0010+0100=0111):表示支持所有卡；

​ 如果想拓展增加一种卡，则用8（1000）表示，再增加一种则用16（10000）表示，2的n次方。

​ 当想查询时，通过位的与运算即可以查询出想要的数据：

-- 查询支持银卡打折的商家信息： 
select * from seller where card_status & b'0010'  
-- 或者：  
Select * from seller where card_status & 2 

​ 优点：

（1）无字段冗余，节省存储空间；

（2）可拓展性强；

（3）查询效率高。

​ 缺点：

​ 如果种类过多，2的n次方的数值也会越大，根本就无法存储和运算。

（3）“位”表示–优化

这个方案进行一些改进：

1. 每一个值都用一个二进制的字符串表示 ，如1，10，100，1000，这里它们都是字符串。

2. 如拥有以上两个，比如权限值为1010，这里需要自己写一个函数来进行字符串的拼接和替换。

3. 查询的时候，需要自己写一个类似的bitand的函数（Oracle的），处理字符串的匹配。

   bitnd函数的语法为以下：

   bitand(a , b)

   若 a= 2; b=3

   a 的 二进制为 0100

   b 的 二进制为 0110

   若相对应的位置都为1，则为 1，否则为0

   所以 bitand(2 , 3) = 0100 = 2

   例如，A表存放3个通知类型，其值分别是2的0次幂、1次幂、2次幂，

   id  name    value
   1   短信      1
   2   邮件      2
   3   即时通讯   4
   

   B表存放功能对应的通知类型的值之和，

   id   func_name   code
   1    会议管理      3  -- 通知方式:短信、邮件
   2    公告通知      7  -- 通知方式:短信、邮件、即时通讯
   3    信息报送      2  -- 通知方式:邮件
   

   查询：

   • 查询通知方式为短信、邮件的（两者相加是3）

     select b.* from B b where bitand(to_number(b.code), 3)>0 ;
     

   • 查询B表中”会议管理“的通知类型：（通过B表中code=3查询出所含的类型）

     select a.name  from A a where bitand(to_number(a.value), 3) = to_number(a.value);
     

     分析：

     ​ 3: 0011

     ​ 1: 0001 结果1 1=1

     ​ 2: 0010 结果2 2=2

     ​ 4: 1000 结果0 0！= 4

     所以，查询的是短信、和邮件。

​ 优点：解决(2)用int类型存取的值过大的问题。

​ 缺点：bitand函数是Oracle中的，其他数据库需要自定义。

（4）关系表（或：数据字典）

​ 如果种类过多，就得创建种类表保存信息，关系表保存主表和次表的关系。

例如，教师表：

-- teacher
id  name
1	张三
2	李四

​ 科目表：

-- subject
id  name
1	语文
2	数学
3	体育

​ 关系表：(众所周知，数学是体育老师教的哈哈)

-- teacher_subject_rel
id	teacher_id	subject_id
1		1			2
2		1			3
3		2			1

缺点：新增了一张表，就需要多关联一张中间表，可能代码量也会增加。

如有不恰当之处，望诸君即时指出，谢谢。
摘抄：
https://chunsoft.blog.csdn.net/article/details/82356664