首先新建三张表:t_user,t_user_role_relation,t_role
CREATE TABLE t_user
(
uid INT NOT NULL AUTO_INCREMENT COMMENT 'user id',
uname VARCHAR(32) NOT NULL COMMENT 'user name, real chinese name ',
mobile VARCHAR(15) NOT NULL COMMENT 'phone number',
type INT NOT NULL COMMENT '0:普通用户(巡防员,联防员), 1:治安管理人员(WEB端,警长,各级治安管理人员), 10:信息员,11:备勤人员,',
password VARCHAR(128) NOT NULL COMMENT 'encrypt password',
org_id INT COMMENT 'the organization of user. -1:none',
id_number VARCHAR(30) NOT NULL COMMENT 'id card number',
note VARCHAR(255),
status INT COMMENT '0:normal, 1:wait approve, 2:freeze , 9:disable',
avatar VARCHAR(64) COMMENT '头像ID',
create_time TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (uid),
UNIQUE IXUK_t_user_mobile(mobile),
INDEX IXFK_t_user_t_organization (org_id ASC),
INDEX IX_t_user_uname (uname ASC)
) COMMENT='用户表,临时用户表t_user_temp和t_user一致,用于记录临时用户或者注册未通过用户。临时用户对应t_user表中一条临时用户的记录'
;
CREATE TABLE t_user_role_relation
(
uid INT NOT NULL,
role_id INT NOT NULL,
PRIMARY KEY (uid, role_id),
INDEX IXFK_t_user_role_relation_t_role (role_id ASC),
INDEX IXFK_t_user_role_relation_t_user (uid ASC)
)
;
CREATE TABLE t_role
(
role_id INT NOT NULL AUTO_INCREMENT,
parent_id INT NOT NULL,
role_name VARCHAR(50) NOT NULL,
note VARCHAR(255),
create_time TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (role_id)
)
;
其中,t_user_role_r