设计部门和员工表

此处的部门和员工，指的是B2C中那个B的部门和员工，也就是平台方的部门和员工，而不是B2B中第二个B的部门和员工。

-- 部门表
DROP TABLE IF EXISTS `t_department`;
CREATE TABLE `t_department` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门编码',
  `deptname` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门名称',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_deptname`(`deptname`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '部门表';

-- 岗位表
DROP TABLE IF EXISTS `t_job`;
CREATE TABLE `t_job` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '岗位编码',
  `job` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '职位名称',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_job`(`job`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '岗位表';

-- 员工表
DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '员工编码',
  `worknum` VARCHAR(32) NOT NULL COMMENT '工号',
  `empname` VARCHAR(32) NOT NULL COMMENT '姓名',
  `avatar` VARCHAR(128) NOT NULL COMMENT '登记照',
  `gender` CHAR(1) NOT NULL DEFAULT '0' COMMENT '性别 0：女；1：男；2：未知',
  `ismarried` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '婚否 0：未婚；1：已婚；2：离异',
  `education` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '学历 0：大专；1：本科；2：研究生；3：博士；4：其他',
  `mobile` CHAR(11) NOT NULL COMMENT '联系电话',
  `email` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '邮箱',
  `address` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '住址',
  `deptid` INT(11) UNSIGNED NOT NULL COMMENT '部门编码',
  `jobid` INT(11) UNSIGNED NOT NULL COMMENT '岗位编码',
  `mgrid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '上司编码',
  `hiredate` DATE NOT NULL COMMENT '入职日期',
  `termdate` DATE NULL DEFAULT NULL COMMENT '离职日期',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态 0：在职；1：休假；2：离职',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_worknum`(`worknum`) USING BTREE,
  INDEX `idx_worknum`(`worknum`) USING BTREE,
  INDEX `idx_jobid`(`jobid`) USING BTREE,
  INDEX `idx_deptid`(`deptid`) USING BTREE,
  INDEX `idx_mgrid`(`mgrid`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '员工表';

-- 角色表
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色编码',
  `role` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_role`(`role`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '角色表';

-- 用户表
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编码',
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `password` VARCHAR(64) NOT NULL COMMENT '密码',
  `salt` VARCHAR(8) NOT NULL DEFAULT '12345678' COMMENT '密码盐',
  `empid` INT(11) UNSIGNED NOT NULL COMMENT '员工编码',
  `roleid` INT(11) UNSIGNED NOT NULL COMMENT '角色编码',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态 0：启用；1：禁用',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_username`(`username`) USING BTREE,
  INDEX `idx_username`(`username`) USING BTREE,
  INDEX `idx_empid`(`empid`) USING BTREE,
  INDEX `idx_roleid`(`roleid`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '用户表';

---

感谢支持

更多内容，请移步《超级个体》。