人员管理数据库设计

文章目录
  1. 1. 用户管理
    1. 1.1. 用户敏感信息处理:
    2. 1.2. users、accounts、passwords表创建语句:

用户管理

视情况而定,三张表或四张表:

  • users 用户基本信息表,用于存放用户id,姓名、性别、头像等常用信息
  • accounts 用户账号表,users:accounts 1:n,用于存储用户账号、登录时间、登录ip等信息(多类型账号支持)
  • passwords 账号对应的密码 accounts:passwords n:1,多个账号都可以登录使用同一个密码。
  • profiles 用户其他信息,users:profiles 1:1,用于存放一些不常用的信息,比如邮箱,工作,职位等等。

为什么1:1的两个表,不直接合并成一个表?常用信息使用一个表,不常用信息使用一个表,可以提高查询速度,信息如果区分的好,单表的查询要远远多于两个表的关联查询,比如users表和profiles表,“用户名”这样的信息会频繁用到,而“单位”,则不是很常用。需要同时显示的时候,做一下关联查询即可。

用户敏感信息处理:

  1. 密码加密:用户密码进行加密处理
  2. 密码添加salt(一个随机字符串,每个用户有个唯一的salt),确保即使密码明文相同,加密过后的密码也不相同
  3. user_id,通过一个序列算法生成(详见《使用序列生成id》),防止分表分库user_id出现重复,防止恶意爬虫,获取有规律的user_id

一个python中的加密算法:

1
2
3
4
5
6
7
8
def _encrypted_password(password, salt):
"""
* 密码加密
* @param string password 密码
* @param string salt 混淆码
* @return string 加密后的密码
"""
return hashlib.md5(hashlib.md5(password).hexdigest() + salt).hexdigest()

users、accounts、passwords表创建语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '用户id',
`name` varchar(50) NOT NULL COMMENT '姓名/昵称 用户姓名和昵称',
`avatar` varchar(200) NOT NULL,
`gender` char(1) NOT NULL DEFAULT 'N' COMMENT '性别: N-unknown M-man F-female',
`is_locked` tinyint(4) NOT NULL DEFAULT '0',
`created_at` bigint(20) NOT NULL,
`updated_at` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `accounts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '0',
`account_type` varchar(10) NOT NULL,
`name` varchar(80) NOT NULL,
`last_visit_ip` varchar(50) NOT NULL,
`last_login_ip` varchar(50) NOT NULL,
`fail_count` int(255) NOT NULL,
`locked_at` bigint(20) NOT NULL,
`created_at` bigint(20) NOT NULL,
`updated_at` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_accounts_actAc` (`account_type`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `passwords` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`account_id` bigint(20) unsigned NOT NULL,
`hashed_password` varchar(32) NOT NULL,
`salt` varchar(5) NOT NULL,
`created_at` bigint(20) NOT NULL,
`updated_at` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_passwords_aid` (`account_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8mb4;