新零售-员工、部门、角色、权限相关数据表

-- 部门表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `department_name` varchar(20) NOT NULL COMMENT '部门名称',
  `parent_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '父部门',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_d_name`(`department_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8  COMMENT = '部门表';

-- 员工职位表
DROP TABLE IF EXISTS `staff_position`;
CREATE TABLE `t_job`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '职位ID',
  `position_name` varchar(20) NOT NULL COMMENT '职位名称',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_position_name`(`position_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '员工职位表';

-- 员工表
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `job_number` varchar(25) NOT NULL COMMENT '工号',
  `name` varchar(20) NOT NULL COMMENT '员工姓名',
  `sex` tinyint(1) NOT NULL COMMENT '员工性别 1-男 2-女',
  `is_married` tinyint(1) NOT NULL COMMENT '婚姻状态 1-未婚 2-已婚',
  `education` tinyint(1) NOT NULL COMMENT '学历 1-大专 2-本科 3-研究生 4-博士 5-其他',
  `tel` char(11) NOT NULL COMMENT '电话',
  `email` varchar(200) NOT NULL DEFAULT '' COMMENT '邮箱',
  `address` varchar(200) NOT NULL DEFAULT '' COMMENT '住址',
  `position_id` int(10) UNSIGNED NOT NULL COMMENT '职位ID，关联staff_position表',
  `department_id` int(10) UNSIGNED NOT NULL COMMENT '部门ID，关联department表',
  `staff_rank` varchar(10) NOT NULL DEFAULT '' COMMENT '员工职级',
  `manager_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '上司ID',
  `hire_date` date NOT NULL COMMENT '入职日期',
  `departure_date` date NULL DEFAULT NULL COMMENT '离职日期',
  `status` tinyint(1) UNSIGNED NOT NULL COMMENT '状态 1-在职 2-休假 3-离职 4-退休',
  `remarks` varchar(256) NOT NULL DEFAULT '' COMMENT '备注信息',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_job_id`(`job_number`) USING BTREE,
  INDEX `idx_position_id`(`position_id`) USING BTREE,
  INDEX `idx_dept_id`(`department_id`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE,
  INDEX `idx_mng_id`(`manager_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '员工表';

-- 后台系统用户表
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(60) NOT NULL COMMENT '密码',
  `salt` varchar(8) NOT NULL DEFAULT '' COMMENT '盐',
  `emp_id` int(10) UNSIGNED NOT NULL COMMENT '员工ID，关联staff表',
  `status` tinyint(1) UNSIGNED NOT NULL COMMENT '账号状态 1-正常 2-禁用',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `last_login_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '上次登录时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_username`(`user_name`) USING BTREE,
  INDEX `idx_emp_id`(`emp_id`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '后台系统用户表';

-- 后台系统角色表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `t_role`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(20) NOT NULL COMMENT '角色名称',
  `sort` int(10) NOT NULL DEFAULT '0' COMMENT '排序值',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_role`(`role_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '角色表';

-- 后台系统用户与角色关联表
DROP TABLE IF EXISTS `admin_role_relation`;
CREATE TABLE `admin_role_relation` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `admin_id` INT(11) NOT NULL COMMENT '用户ID',
  `role_id` INT(11) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '后台系统用户与角色关联表';

-- 后台系统权限表
DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `privilege_name` varchar(255) NOT NULL DEFAULT '' COMMENT '权限名',
  `menu_level` mediumint(5) NOT NULL DEFAULT '1' COMMENT '菜单级别',
  `description` varchar(255) DEFAULT '' COMMENT '权限描述',
  `module` varchar(128) NOT NULL DEFAULT '' COMMENT '模块',
  `controller` varchar(128) NOT NULL DEFAULT '' COMMENT '控制器',
  `action` varchar(128) NOT NULL DEFAULT '' COMMENT '方法',
  `privilege_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '类型 1-menu 2-btn，3-group',
  `parent_id` int(11) unsigned DEFAULT '0' COMMENT '父权限ID',
  `icon` varchar(50) NOT NULL DEFAULT '' COMMENT '权限图标',
  `sort` mediumint(5) unsigned DEFAULT '0' COMMENT '排序',
  `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已经被删除 0-未删除 1-已删除',
  `is_display` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否显示 0-不显示 1-显示',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `update_time` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限表';

-- 后台系统角色与权限关联表
DROP TABLE IF EXISTS `role_privilege_relation`;
CREATE TABLE `role_privilege_relation` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `role_id` INT(11) NOT NULL COMMENT '角色ID',
  `privilege_id` int(11) unsigned NOT NULL COMMENT '权限ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '后台系统用户与角色关联表';
打赏赞