数据库设计图-数库大数据

本文核心内容：记录和积累一些库表设计方案和技巧

数据库实体与实体的对应关系

1）数据库表的菜单【类别】设计：如省市协会、图书的一级二级分类。

2）数据库表设计的树形结构表

3）表的简化方案（具体情况，例如用户触发的场景记录表）

4）数据库表设计的购物车，使用Session暂存购物车信息。

实体与实体之间的对应关系

一对一

一对一，一般用来补充主表。 假设A表是一张用户信息表，存储了用户的姓名、性别、年龄等基本信息。 用户的家庭住址信息也属于用户的基本信息。 我们可以选择将用户的家庭住址信息放在用户信息表中，也可以单独建一个表来存储用户的家庭住址信息，并以用户信息表的主键作为关联。

是否需要拆分取决于：表信息的关联程度和表中字段数的限制。

一对多

一对多是最常见的设计。 即A表的一条记录对应B表的多条记录，A表的主键作为B表的外键。举几个例子：

班级表和学生表，一个班级对应多个学生，或者多个学生对应一个班级。 角色表和用户表，一个角色对应多个用户，或者多个用户对应一个角色。 商品表和图片表，一张商品对应多张图片，或者多张图片对应一张商品。

多对多

建立关系表，将两个表关联起来，形成多对多的形式。 例如：

教师表、学生表； 一个学生可以上多位老师的课，一个老师可以同时教多位学生。

--教师表
CREATE TABLE #Teacher(TeacherId int, Name nvarchar(20));
INSERT INTO #Teacher VALUES(1, '张老师'), (2, '王老师');
--学生表
CREATE TABLE #Student(StudentId int, Name nvarchar(20));
INSERT INTO #Student VALUES(1, '小张'), (2, '小赵');

--老师学生关系表
CREATE TABLE #Teacher_Student(StudentId int, TeacherId int);
INSERT INTO #Teacher_Student VALUES(1, 1), (1, 2),(2, 1), (2, 2);

一：数据库表的菜单【类别】设计：如省市关联，图书的主次分类

BookType初级分类：少儿、外语、电脑

BookClass二级分类：少儿[0-2岁、3-6岁、7-10岁、11-14岁、少儿文学]

外语【英、日、韩、俄、德】

计算机【计算机理论、计算机考试、数据库、人工智能、程序设计】

BookInf 书籍详细信息：书籍信息的详细信息字段。 . .

基于以上关系：我们有两种创建表的方法

①：创建三张表，一级分类表，二级分类表，图书明细表

一级分类ID -> 作为二级分类的外键

二级类别 ID -> 外键作为书籍详细信息

这个依赖外键，实体模型比较简单。 （不再赘述）

查询语句：可以使用left join on或者equivalent connection将二级分类的外键和一级分类的主键连接起来进行查询。

②：创建两张表，将一级分类和二级分类合并为一张表

图书明细表（引用 TypeID 作为外键）

TypeID是指一级分类和二级分类的ID（唯一标识，主键）。 序列从 1 开始。

TypeName 指的是一级二级分类的名称

ParentID是指二级分类所属的一级分类的TypeID（如果是一级分类，则填“0”以区别于二级分类）

countNumber 指一级图书包括二级图书的数量

二级分类收录明细书数

数据库查询一级分类信息的SQL

select typeid,typename,parentid,countnumber 
from t_booktype   where parentid='0'

数据查询二级分类信息（使用表自连接）

select  child.typeid,child.typename,
child.parentid,child.countnumber 		
from t_booktype child ,t_booktype  parent 
where child.parentid=parent.typeid

二：在MySQL表中存储树结构数据

受上述设计技巧的启发，如果数据层次结构有多个层次呢？ 简而言之，就像一棵树，我们如何在数据库中存储树形数据。

存储父节点

在数据库中存储最简单直接的方式就是存储每个元素的父节点的ID，即parent_Id->parent node Id。 这种方式插入方便，但是查询在某些情况下会束手无策。 我们可以添加两个字段（deep，is_leaf）来帮助我们查询更快。

deep=1 表示父节点，deep>1 表示子节点。

id
parent_id
deep     //当前树的深度
is_leaf  //是否叶子节点

查询所有父节点deSQL如下：

select * from tree where deep=1

查询一个父节点下的所有子节点：

select * from tree where parent_id="" 

查询某个父节点下的所有后代节点，采用这种库表设计方式数据库设计图，这个需要依赖程序来实现。

存储路径

将存储从根节点到每个节点的路径。 有了这个数据结构，子节点的深度就一目了然了。 插入自己，然后找出父节点的Path，将自己生成的ID更新为path。

如果要查询某个节点下的子节点，只需要根据路径匹配路径即可，比如要查询D节点下的所有子节点。

select * from tree where path like '1/4/%'

总结

我建议存储一个树结构可以将两者结合起来。

id
parent_id
deep     //当前树的深度
path     //根路径
is_leaf  //是否叶子节点

三：MySQL表的简化（具体情况，例如：用户触发的场景记录表）

假设业务中有N个以上的道具，比如用户第一次使用某个道具触发了特效。 根据表中是否有用户相关的item触发记录完成判断。

id
user_id //用户Id
item_id //道具ID
flag    //是否触发过特效 0-1
add_time
update_time

毫无疑问，上面的表结构可以满足和实现我们的需求，但是如果有20种props，那么每个用户最终会有20条数据，数据冗余数据库设计图，如何简化呢？

解决方案：

定义道具枚举值

public enum ItemOnceFlagEnum {
    
    NONE(0),
    
    ITEM_ONE(1),//道具1
    
    ITEM_TWO(1 << 1), //道具2

    
    ITEM_Three(1 << 2), //道具3
    
    ;
    
    private int code;
    
    ItemOnceFlagEnum(int code) {
        this.code = code;
    }
    public int getCode() {
        return code;
    }
    
    public static ItemOnceFlagEnum valueOf(int code) {
        ItemOnceFlagEnum[] values = ItemOnceFlagEnum.values();
        for (ItemOnceFlagEnum flag : values) {
            if (flag.getCode() == code) {
                return flag;
            }
        }
        return NONE;
    }
}

建表

id

user_id //用户Id
flag    //是否触发过特效 2的0次幂、2的1次幂。。。
add_time
update_time
user_id设置为唯一索引

判断道具是否被使用，并添加道具使用记录

    //判断是否使用过某项道具
    public static boolean isHasThisFlag(long flag, ItemOnceFlagEnum itemOnceFlagEnum) {
        return (flag & itemOnceFlagEnum.getCode()) > 0;
    }
    //添加道具使用记录
    public Result addOnceFlag(long userId, itemOnceFlagEnum flag) {
         ItemOnceFlagDO itemOnceFlagDO = itmeOnceFlagService.getOnceFlagMap(Collections.singletonList(userId));
         long calculateFlag = (Objects.isNull(blockOnceFlagDO) ? 0L : itemOnceFlagDO.getFlag()) | flag.getCode();
         itemOnceFlag.setFlag(calculateFlag);
         boolean res = itemOnceFlagService.addOrUpdateOnceFlag(itemOnceFlag);
    }

添加记录SQL

"INSERT INTO xxxx ( ) VALUES ( :1.userId,  :1.flag ) " +
            " ON DUPLICATE KEY UPDATE flag = :1.flag "

某个道具是2值的特定力量（只能保持2个状态，是或否），flag代表所有触发道具的2力量之和。

使用(flag &itemOnceFlagEnum.getCode())>0 判断是否存在某个item

通过 flag|temOnceFlagEnum.getCode() 添加道具记录；

四：购物车模块的库表设计

在电子商务软件中，一个必不可少的模块就是购物车。