多账号数据库表如何设计？

Z时代
2024-02-08
分类：IT

账号表

CREATE TABLE `account`
(
    `id`            BIGINT       NOT NULL COMMENT '主键ID',
    `user_id`       BIGINT       NOT NULL COMMENT '用户ID',
    `account`       VARCHAR(64)  NOT NULL COMMENT '账号',
    `password`      VARCHAR(128) NOT NULL COMMENT '密码',
    `account_type` int DEFAULT 1 COMMENT '账号类型 {[1:邮箱] [2:手机] [3:工号]}'
    ........)

用户表

CREATE TABLE `user`
(
    `id`     BIGINT       NOT NULL COMMENT '主键ID',
    `name`   VARCHAR(128) NOT NULL COMMENT '姓名',
    `avatar` VARCHAR(255) default null comment '头像',
    `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注'
    ........)

员工信息表

CREATE TABLE `employee`
(
    `id`          BIGINT NOT NULL COMMENT '主键ID',
    `user_id`     BIGINT NOT NULL COMMENT '用户ID',
    `employee_no` int    NOT NULL COMMENT '工号',
    `join_date`   date   NOT NULL COMMENT '入职时间'
    ........)

user与account是一对多
user与employee是一对一

系统需要支持以工号、邮箱、手机号登录(手机验证码或密码)
在上述表设计中会存在一下问题

查询用户信息的时候需要显示邮箱、手机号、工号等信息，如果用户表中没有这些信息，则需要通过关联查询账号表，但是如果用户表中存在这些信息，则需要冗余这些字段，并且修改账号表数据时需要同步修改冗余字段
用户表与账号表是一对多，同一个用户多个账号的密码是相同的，修改密码就需要同步修改多个账号
查询用户信息的时候表需要跨多张表
有些情况用户不一定是员工，可能是临时用户所有区分了用户表和员工表
如果user冗余(邮箱、手机号、工号)的话是需要加密存储的，这种情况账号表需要加密吗，常规做法是怎么样的

根据以上情况，这些表改如何优化

回答：

感觉直接去掉 account 表，相关字段放回 user 表就解决问题了

1 邮箱、手机号、工号等都在 user 表里了
2 密码只有一个在 user 表里
3 查询用户信息的时候不跨表
4 没有 employee 信息的就是临时用户

登录的时候根据用户表单输入或者选择的类型，直接从 account 里查找也没啥问题

回答：

身份验证信息（邮箱、手机号、密码这些）放在一个表里，然后把用户详细信息（姓名、头像这些）放在另一个表里。用单一密码字段，如果说一个用户的所有账号共享相同的密码的话，那只要在账号表里存一个密码字段就行。

CREATE TABLE `user`
(
    `id`     BIGINT       NOT NULL COMMENT '主键ID',
    `name`   VARCHAR(128) NOT NULL COMMENT '姓名',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像',
    `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
    `email`  VARCHAR(255) DEFAULT NULL COMMENT '邮箱',
    `phone`  VARCHAR(16)  DEFAULT NULL COMMENT '手机号',
    `employee_no` INT     DEFAULT NULL COMMENT '工号'
    ........
)
CREATE TABLE `auth`
(
    `id`            BIGINT       NOT NULL COMMENT '主键ID',
    `user_id`       BIGINT       NOT NULL COMMENT '用户ID',
    `identity`      VARCHAR(255) NOT NULL COMMENT '标识（可以是邮箱、手机号或工号）',
    `password`      VARCHAR(128) NOT NULL COMMENT '密码',
    `identity_type` INT          DEFAULT 1 COMMENT '账号类型 {[1:邮箱] [2:手机] [3:工号]}'
    ........
)
CREATE TABLE `employee`
(
    `id`          BIGINT NOT NULL COMMENT '主键ID',
    `user_id`     BIGINT NOT NULL COMMENT '用户ID',
    `join_date`   DATE   NOT NULL COMMENT '入职时间'
    ........
)

回答：

从你的描述问题2来看，账户与密码、账户与用户都是多对一，用户和密码是一对一，所以登录密码应该是用户级别的属性而非账户的属性。登录的过程就变成：根据提交的账户名称和账户类型查找用户级别的信息，如果密码符合用户的密码就可以登录；这样就避免了你问题3中提到的需要同步改多条账户记录的问题。
不过，我觉得除非你们需要考虑一个用户可以绑多个邮箱、多个手机号这种情况，不然不太需要将同一个人的账户拆成多个，而是将不同账户类型的账户名放在不同字段里，比如

CREATE TABLE `account`
(
    `id`            BIGINT       NOT NULL COMMENT '主键ID',
    `user_id`       BIGINT       NOT NULL COMMENT '用户ID',
    `email_account`       VARCHAR(64)  COMMENT '邮箱账号',
    `mobile_account`       VARCHAR(64) COMMENT '手机号码账号',
    `employee_no_account`       VARCHAR(64) COMMENT '员工工号账号',
    `password`      VARCHAR(128) NOT NULL COMMENT '密码',
    ........)

然后根据用户登录时前端传递过来的账号类型选择查询account表的哪一列作为账号名。如果没有绑定邮箱、或者改用户不是员工，那么account表中对应的字段就是空的。需要绑定邮箱或绑定员工信息时，将account表同步更新。
关于查询用户信息时需要显示用户的邮箱、手机号等信息，以及同步修改，我认为联表查询和跨表修改都是合理的，没有什么不可接受的地方。而且你用户表和员工表是明显分开的，如果要查用户的时候显示员工信息就是不可避免的连表查询。

回答：

保留 user表 和 account表，可以理解为 account表 是 user表 的子表；
请问，临时员工和正式员工区别的作用是什么？如果是为了方便后续权限控制的话，直接在user 表加标识字段type即可。以下是具体的数据表设计：

user表

CREATE TABLE `user`
(
    `id`     BIGINT       NOT NULL COMMENT '主键ID',
    `name`   VARCHAR(128) NOT NULL COMMENT '姓名',
    `avatar` VARCHAR(255) default null comment '头像',
    `employee_no` int    NOT NULL COMMENT '工号',
    `join_date`   date   NOT NULL COMMENT '入职时间',
    `password`    VARCHAR(128) NOT NULL COMMENT '密码',
    `email`  VARCHAR(255) DEFAULT NULL COMMENT '邮箱',
    `employee_no` INT  DEFAULT NULL COMMENT '工号',
    `phone`  VARCHAR(16)  DEFAULT NULL COMMENT '手机号',
    `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
    `type` bit(1) NULL DEFAULT 1 COMMENT '是否正式员工，0临时,1正式'
    ......)

account表

CREATE TABLE `account`
(
    `id`            BIGINT       NOT NULL COMMENT '主键ID',
    `user_id`       BIGINT       NOT NULL COMMENT '用户ID',
    `account`       VARCHAR(64)  NOT NULL COMMENT '账号',
    `account_type` int DEFAULT 1 COMMENT '账号类型 {[1:邮箱] [2:手机] [3:工号]}'
    ........)

请问，账号的account_type的作用是什么呢？只是为了标识，还是用户登录的时候需要填写对应的信息传递给后端呢？

如果登录的时候用户只需要填写账号和密码，和account_type无关的话，就是常规的登录逻辑：用户填写账号和密码 -->后端根据账号，查询对应用户，核对密码是否正确......