实现方案

人员管理数据库设计

文章目录
  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;