常用的数据表记录
参考资料 数据库表（大全） 这个文档用来记录曾经创建过的表，避免重复造轮子
部门表
CREATE TABLE `tb_dept`
(
`dept_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`dept_name` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门名称（索引）',
`dept_desc` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '部门描述',
`dept_location` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '部门位置',
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`dept_id`),
UNIQUE KEY `tb_dept_dept_name_uindex` (`dept_name`)
) ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
会议表
CREATE TABLE `tb_meeting`
(
`meeting_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`host_id` int(10) unsigned DEFAULT NULL,
`start_time` datetime NOT NULL,
`end_time` datetime DEFAULT NULL,
`record_desc` text COLLATE utf8mb4_unicode_ci,
`record_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '无主题',
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`meeting_id`),
KEY `tb_meeting_tb_user_emp_id_fk` (`host_id`),
CONSTRAINT `tb_meeting_tb_user_emp_id_fk` FOREIGN KEY (`host_id`) REFERENCES `tb_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
菜单项目表
CREATE TABLE `tb_menu_item`
(
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`item_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '菜单每项的名称',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0',
`item_desc` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '菜单功能描述',
`item_path` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`item_group` int(10) unsigned DEFAULT NULL COMMENT '设置分组，标识当前这个 item 属于哪个分组下面的',
PRIMARY KEY (`item_id`),
UNIQUE KEY `tb_menu_item_item_name_uindex` (`item_name`),
KEY `tb_menu_item_tb_menu_item_group_group_id_fk` (`item_group`),
CONSTRAINT `tb_menu_item_tb_menu_item_group_group_id_fk` FOREIGN KEY (`item_group`) REFERENCES `tb_menu_item_group` (`group_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 30
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
角色表
CREATE TABLE `tb_roles`
(
`role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`role_name` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限名称',
`role_desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `tb_roles_role_name_uindex` (`role_name`)
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci COMMENT ='权限表';
任务表
CREATE TABLE `tb_tasks`
(
`task_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`task_desc` text COLLATE utf8mb4_unicode_ci COMMENT '任务描述',
`attendee` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用来存储出席人物的 用户/部门，记录的是 用户/部门的数组，每个值使用;进行分隔',
`is_dept` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用来标识是否于部门有关：要考虑以下情况1. 接收人是一个用户2. 接收人是一个部门 3. 接收人是多个部门 4. 接收人是多个不同部门的人',
`create_time` datetime NOT NULL,
`is_end` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '任务的进度，0 任务中 1 结束',
`host_id` int(10) unsigned DEFAULT NULL COMMENT '任务负责人',
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`task_id`),
KEY `tb_tasks_tb_user_user_id_fk` (`host_id`),
CONSTRAINT `tb_tasks_tb_user_user_id_fk` FOREIGN KEY (`host_id`) REFERENCES `tb_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
用户表
CREATE TABLE `tb_user`
(
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '人员编号',
`user_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '这个是登陆的用户ID，这里就是工号',
`user_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`mgr_id` int(10) unsigned DEFAULT NULL COMMENT '上级领导的编号',
`true_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '真实名称',
`sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 男，1 女',
`hire_date` date DEFAULT NULL COMMENT '入职时间',
`sal` decimal(10, 0) NOT NULL DEFAULT '2000' COMMENT '工资',
`comm` decimal(10, 0) NOT NULL DEFAULT '0' COMMENT '提成，奖金之类的',
`job` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '职位',
`birthday` date DEFAULT NULL COMMENT '生日',
`phone` char(11) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机号',
`photo` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像地址',
`email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱地址',
`last_ip` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后的登陆ip',
`last_time` datetime DEFAULT NULL COMMENT '最后登陆时间',
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1 表示删除了，0 表示未删除',
`dept_id` int(10) unsigned DEFAULT '1' COMMENT '部门id（0 表示无部门）',
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_emp_no_uindex` (`user_name`),
KEY `tb_user_tb_dept_dept_id_fk` (`dept_id`),
CONSTRAINT `tb_user_tb_dept_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `tb_dept` (`dept_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;