由web项目引发的对mysql建表时的思考和注意

背景

之前ISC这边接手了一个开发项目，做一个志愿活动服务平台，因为开发经验不够，拿到手建表只考虑了用户信息和角色信息的关联，~~猪脑过载~~没想到活动这块儿，可想而知，接口写着写着就发现写不下去，表得重写（悲）

建表过程

1. 角色分区

考虑到关系逻辑，我干脆把用户信息和角色信息绑定，直接把区分区分角色建表：volunteer、organizer、regulator三张角色表
然后每个角色有最基本的用户信息id（各表主键）,name（唯一键）,password（加密存储）,phone,email,avatar（存储存放头像文件的路径）

根据不同的角色再细化特性字段：

volunteer设置了志愿积分、参与活动数目统计、参与活动的最大限制数量、志愿状态
建表语句：

create table volunteer
 (
   id             int auto_increment
       primary key,
   name           char(32)      not null,
   password       varchar(64)   null,
   phone          char(12)      null,
   email          varchar(32)   null,
   avatar         varchar(64)   null,
   score          int default 0 null,
   activity_count int default 0 null,
   activity_max   int default 0 null comment '限制最大参与活动数',
   status         int default 0 not null,
   deleted        int default 0 null,
   constraint volunteer_u
     unique (name)
 )
   comment '志愿者表';

organizer比较特殊的是一个限制最大活动发布数
建表语句：

   create table organizer
(
    id           int auto_increment
        primary key,
    name         char(32)      not null,
    password     varchar(64)   not null,
    phone        char(12)      not null,
    eamil        varchar(32)   null,
    avatar       varchar(64)   null,
    activity_max int default 0 null,
    deleted      int default 0 null,
    constraint organizer_u
        unique (name)
)
    comment '组织者表';

regulator的话有个等级字段（打算的是陆续开放冻结用户、删除用户权限）
建表：

   create table regulator
(
    id       int auto_increment
        primary key,
    name     char(32)      null,
    password varchar(64)   not null,
    phone    char(12)      not null,
    email    varchar(32)   null,
    avatar   varchar(64)   null,
    rate     int default 1 null comment '管理等级',
    deleted  int default 0 null,
    constraint regulator_pk
        unique (name)
)
    comment '管理者表'

注意：

varchar和char的使用：varchar是可变长，char则是定长，存储时varchar省空间一点儿，但是查询效率不及char（数据量特别大时差距会明显一点儿），我这儿把name和phone设为char是后续要借助这俩字段来查重
字段deleted用于mybatis-plus的逻辑删除处理（即虚假删除），事务逻辑删除开启后，经过虚假删除的记录在数据库会继续存在，但是查询的时候是自动忽略的（~~所以不要以为账户注销就万事大吉辽~~）。

2.活动管理

活动表activity的建立及与organizer表的关联

按照日常对于志愿活动的理解，设置了这些字段：id（主键），name(唯一键，活动不重名是为了用户查看历史信息时不会混淆), theme, organizer_id, data_time, location, volunteer_min, volunteer_max, volunteer_current_number, status, description, deleted

建表：

create table activity
(
    id                       int auto_increment
        primary key,
    organizer_id             int           null,
    name                     char(64)      null,
    theme                    varchar(32)   null,
    data_time                datetime      null,
    location                 varchar(128)  null,
    volunteer_min            int default 0 null comment '所需最小的志愿者数量',
    volunteer_max            int           null comment '所需志愿者最大数',
    volunteer_current_number int default 0 not null,
    status                   int default 0 not null comment '活动状态',
    description              text          null comment '活动描述',
    deleted                  int default 0 null,
    constraint activity_u
        unique (name),
    constraint activity_organizer_fk
        foreign key (organizer_id) references organizer (id)
);

注：

其中organizer_id是关联organizer表中id的外键，与organizer表做到数据统一
status字段表示活动状态，不同整数代表不同状态，比如准备中、召集中、进行中、已完成等
description属于text数据类型，长文本哈，我的预期是把活动描述的字数控制在400字以内，这样也不会造成太大的查询效率影响
当参与志愿者数位于目标区间之内时，活动方可正式进行

activity与volunteer之间的关联

其实刚开始我想的是在志愿者表里面直接设置一些列，比如10列来存放活动id，和活动表关联起来，但是细想觉得很浪费空间，而且没有好好利用mysql这种关系型数据库的设计模式

所以我就新建了一个关系表activity_volunteer_relation，用来记录志愿者对于活动的参与情况：

create table activity_volunteer_relation
(
    id           int auto_increment
        primary key,
    activity_id  int null,
    volunteer_id int null,
    constraint fk1
        foreign key (activity_id) references activity (id),
    constraint fk2
        foreign key (volunteer_id) references volunteer (id)
)
    comment '记录志愿者参与活动情况';

字段id为主键，activity_id是关联activity的外键字段，volunteer_id是关联volunteer的外键字段，这样后面查询的时候直接根据活动id和志愿者id就能把记录查询出来，也不用担心volunteer的活动数改变的问题

思考

关于数据表建表：

数据类型选择。根据空间和效率来看哪个数据类型更合适放在这条记录中，日后开发高并发、高性能的项目时这点肯定会很重要（比如这次的varchar和char）
灵活处理关系。当对象关系比较杂乱的时候，分离出类来（没错，就是面向对象设计中的类），这本身也和java的观念吻合，实体类这一层也是这个思想。从对象之间关系的角度来设计表（类似volunteer和activity)

雷点（可能是日后项目里面的坑）：

有的字段可能还是不够合理，比方说activity的name，实际上应该允许存在不同主题下存在同名活动（~~问题倒也不大~~）
还有一些字段可能需要加，比如volunteer的等级、organizer的等级、信誉等等，随着等级的提升来对活动数目限制进行更改（这个改倒是方便，~~也不算大坑~~）

结语

大致这些罢，SQL这边玩儿的还不多，命令行下手写语句还是很容易打错，可视化界面（尤其像DataGrip、IDEA这些大赞）下倒还好。

还有约束条件、手撸多表、分页查询也不熟，mysql的函数更是没用多少，耳易老师会慢慢~~水水字~~更，慢慢学

Mysql（1）