| New file |
| | |
|---|
| | | /*
|
|---|
| | | Navicat MySQL Data Transfer
|
|---|
| | |
|
|---|
| | | Source Server : 192.168.1.51
|
|---|
| | | Source Server Version : 50714
|
|---|
| | | Source Host : 192.168.1.51:3306
|
|---|
| | | Source Database : face_discern
|
|---|
| | |
|
|---|
| | | Target Server Type : MYSQL
|
|---|
| | | Target Server Version : 50714
|
|---|
| | | File Encoding : 65001
|
|---|
| | |
|
|---|
| | | Date: 2017-03-16 10:54:29
|
|---|
| | | */
|
|---|
| | |
|
|---|
| | | SET FOREIGN_KEY_CHECKS=0;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for calling
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `calling`;
|
|---|
| | | CREATE TABLE `calling` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '签到事件主键',
|
|---|
| | | `call_dt` datetime DEFAULT NULL COMMENT '呼叫时间',
|
|---|
| | | `caller_person_id` int(10) DEFAULT NULL COMMENT '呼叫人员主键',
|
|---|
| | | `callee_person_id` int(10) DEFAULT NULL COMMENT '被呼叫人员主键',
|
|---|
| | | `place_id` int(10) DEFAULT NULL COMMENT '呼叫发生所在设备(地点)',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `caller_person_id` (`caller_person_id`),
|
|---|
| | | KEY `callee_person_id` (`callee_person_id`),
|
|---|
| | | KEY `calling_ibfk_3` (`place_id`),
|
|---|
| | | CONSTRAINT `calling_ibfk_1` FOREIGN KEY (`caller_person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `calling_ibfk_2` FOREIGN KEY (`callee_person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `calling_ibfk_3` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for checking
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `checking`;
|
|---|
| | | CREATE TABLE `checking` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '签到事件主键',
|
|---|
| | | `status_id` tinyint(2) DEFAULT NULL COMMENT '签到状态',
|
|---|
| | | `status_dt` datetime DEFAULT NULL COMMENT '签到时间',
|
|---|
| | | `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
|
|---|
| | | `place_id` int(10) DEFAULT NULL COMMENT '签到位置',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `person_id` (`person_id`),
|
|---|
| | | KEY `place_id` (`place_id`),
|
|---|
| | | CONSTRAINT `checking_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `checking_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for checking_company
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `checking_company`;
|
|---|
| | | CREATE TABLE `checking_company` (
|
|---|
| | | `person_id` int(10) NOT NULL COMMENT '人员主键',
|
|---|
| | | `company_id` int(10) NOT NULL COMMENT '公司主键',
|
|---|
| | | PRIMARY KEY (`person_id`,`company_id`),
|
|---|
| | | KEY `company_id` (`company_id`),
|
|---|
| | | CONSTRAINT `checking_company_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `checking_company_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for company
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `company`;
|
|---|
| | | CREATE TABLE `company` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '公司主键',
|
|---|
| | | `name` varchar(50) DEFAULT NULL COMMENT '公司名称',
|
|---|
| | | PRIMARY KEY (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for department
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `department`;
|
|---|
| | | CREATE TABLE `department` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '部门主键',
|
|---|
| | | `label` varchar(40) DEFAULT NULL COMMENT '部门名称',
|
|---|
| | | `label_spell` char(20) DEFAULT NULL COMMENT '部门首字母',
|
|---|
| | | PRIMARY KEY (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for face_image
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `face_image`;
|
|---|
| | | CREATE TABLE `face_image` (
|
|---|
| | | `face_id` int(10) NOT NULL COMMENT '员工图片主键',
|
|---|
| | | `register_company_id` int(10) NOT NULL COMMENT '注册公司主键',
|
|---|
| | | `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
|
|---|
| | | `image_path` varchar(255) DEFAULT NULL COMMENT '图片地址',
|
|---|
| | | `image_type_id` int(10) DEFAULT NULL COMMENT '图片类型',
|
|---|
| | | PRIMARY KEY (`face_id`,`register_company_id`),
|
|---|
| | | KEY `register_company_id` (`register_company_id`),
|
|---|
| | | KEY `person_id` (`person_id`),
|
|---|
| | | CONSTRAINT `face_image_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`),
|
|---|
| | | CONSTRAINT `face_image_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for image
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `image`;
|
|---|
| | | CREATE TABLE `image` (
|
|---|
| | | `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
|
|---|
| | | `image_type` int(10) DEFAULT NULL COMMENT '图片类型',
|
|---|
| | | `image_path` varchar(255) DEFAULT NULL COMMENT '图片地址',
|
|---|
| | | KEY `person_id` (`person_id`),
|
|---|
| | | CONSTRAINT `image_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for person
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `person`;
|
|---|
| | | CREATE TABLE `person` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '人员主键',
|
|---|
| | | `name` varchar(40) DEFAULT NULL COMMENT '人员名字',
|
|---|
| | | `name_spell` char(2) DEFAULT NULL COMMENT '人员名首字母',
|
|---|
| | | `gender` tinyint(1) DEFAULT NULL COMMENT '性别',
|
|---|
| | | `country` char(3) DEFAULT NULL COMMENT '国籍',
|
|---|
| | | `province` char(20) DEFAULT NULL COMMENT '省',
|
|---|
| | | `city` char(20) DEFAULT NULL COMMENT '市',
|
|---|
| | | `birthday` date DEFAULT NULL COMMENT '出生日期',
|
|---|
| | | `phone` char(12) DEFAULT NULL COMMENT '座机电话',
|
|---|
| | | `mobile_phone` char(15) DEFAULT NULL COMMENT '移动电话',
|
|---|
| | | `identity_type` int(10) DEFAULT NULL COMMENT '证件类型',
|
|---|
| | | `identify` varchar(100) DEFAULT NULL COMMENT '证件编号',
|
|---|
| | | `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
|
|---|
| | | `department_id` int(10) DEFAULT NULL COMMENT '部门主键',
|
|---|
| | | `post_id` int(10) DEFAULT NULL COMMENT '职务主键',
|
|---|
| | | `remark` varchar(255) DEFAULT NULL COMMENT '人员备注消息',
|
|---|
| | | `warning` tinyint(1) DEFAULT NULL COMMENT '危险人员警告消息',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `company_id` (`company_id`),
|
|---|
| | | KEY `post_id` (`post_id`),
|
|---|
| | | KEY `department_id` (`department_id`),
|
|---|
| | | CONSTRAINT `person_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
|
|---|
| | | CONSTRAINT `person_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
|
|---|
| | | CONSTRAINT `person_ibfk_4` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for person_ext
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `person_ext`;
|
|---|
| | | CREATE TABLE `person_ext` (
|
|---|
| | | `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
|
|---|
| | | KEY `person_id` (`person_id`),
|
|---|
| | | CONSTRAINT `person_ext_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for place
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `place`;
|
|---|
| | | CREATE TABLE `place` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '设备主键',
|
|---|
| | | `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
|
|---|
| | | `label` varchar(40) DEFAULT NULL COMMENT '设备地点',
|
|---|
| | | `authorization_id` varchar(100) NOT NULL COMMENT '设备授权码',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `company_id` (`company_id`),
|
|---|
| | | CONSTRAINT `place_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for post
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `post`;
|
|---|
| | | CREATE TABLE `post` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '职位主键',
|
|---|
| | | `label` varchar(40) DEFAULT NULL COMMENT '职位名称',
|
|---|
| | | PRIMARY KEY (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visit
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visit`;
|
|---|
| | | CREATE TABLE `visit` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事件主键',
|
|---|
| | | `visitor_person_id` int(10) DEFAULT NULL COMMENT '访问人员主键',
|
|---|
| | | `visitee_person_id` int(10) DEFAULT NULL COMMENT '被访问人员主键',
|
|---|
| | | `matter_id` int(10) DEFAULT NULL COMMENT '访问事由主键',
|
|---|
| | | `remark` varchar(255) DEFAULT NULL COMMENT '访问备注',
|
|---|
| | | `last_status` int(10) DEFAULT NULL COMMENT '最新的访问状态',
|
|---|
| | | `start_time` datetime DEFAULT NULL COMMENT '来访时间',
|
|---|
| | | `last_time` datetime DEFAULT NULL COMMENT '签离时间',
|
|---|
| | | `visitor_company_id` int(10) DEFAULT NULL,
|
|---|
| | | `visitee_company_id` int(10) DEFAULT NULL,
|
|---|
| | | `visitor_type_id` int(10) DEFAULT NULL COMMENT '访客类型',
|
|---|
| | | `warning` tinyint(1) DEFAULT NULL COMMENT '当前访问的警告状态',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `visitor_person_id` (`visitor_person_id`),
|
|---|
| | | KEY `visitee_person_id` (`visitee_person_id`),
|
|---|
| | | KEY `matter_id` (`matter_id`),
|
|---|
| | | KEY `visit_ibfk_4` (`visitor_type_id`),
|
|---|
| | | KEY `visitor_company_id` (`visitor_company_id`),
|
|---|
| | | KEY `visitee_company_id` (`visitee_company_id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_1` FOREIGN KEY (`visitor_person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_2` FOREIGN KEY (`visitee_person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_3` FOREIGN KEY (`matter_id`) REFERENCES `visit_matter` (`id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_4` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_5` FOREIGN KEY (`visitor_company_id`) REFERENCES `company` (`id`),
|
|---|
| | | CONSTRAINT `visit_ibfk_6` FOREIGN KEY (`visitee_company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visiting
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visiting`;
|
|---|
| | | CREATE TABLE `visiting` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事件流水id',
|
|---|
| | | `visit_id` int(10) DEFAULT NULL COMMENT '访问事件id',
|
|---|
| | | `status_id` tinyint(2) DEFAULT NULL COMMENT '当前访问状态',
|
|---|
| | | `status_dt` datetime DEFAULT NULL COMMENT '更新状态时间',
|
|---|
| | | `place_id` int(10) DEFAULT NULL COMMENT '设备位置主键',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `visit_id` (`visit_id`),
|
|---|
| | | KEY `place_id` (`place_id`),
|
|---|
| | | CONSTRAINT `visiting_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visit` (`id`),
|
|---|
| | | CONSTRAINT `visiting_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visitor_type
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visitor_type`;
|
|---|
| | | CREATE TABLE `visitor_type` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访客类型主键',
|
|---|
| | | `label` varchar(32) DEFAULT NULL COMMENT '访客类型',
|
|---|
| | | `register_company_id` int(10) DEFAULT NULL COMMENT '注册公司主键',
|
|---|
| | | `available` tinyint(1) DEFAULT NULL COMMENT '是否是可用',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `register_company_id` (`register_company_id`),
|
|---|
| | | CONSTRAINT `visitor_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visit_matter
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visit_matter`;
|
|---|
| | | CREATE TABLE `visit_matter` (
|
|---|
| | | `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事由主键',
|
|---|
| | | `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
|
|---|
| | | `label` varchar(40) NOT NULL COMMENT '访问事由',
|
|---|
| | | `available` tinyint(1) DEFAULT NULL COMMENT '是否是可用',
|
|---|
| | | PRIMARY KEY (`id`),
|
|---|
| | | KEY `company_id` (`company_id`),
|
|---|
| | | CONSTRAINT `visit_matter_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visit_person_type
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visit_person_type`;
|
|---|
| | | CREATE TABLE `visit_person_type` (
|
|---|
| | | `register_company_id` int(10) NOT NULL,
|
|---|
| | | `person_id` int(10) NOT NULL,
|
|---|
| | | `visitor_type_id` int(10) NOT NULL,
|
|---|
| | | PRIMARY KEY (`register_company_id`,`person_id`,`visitor_type_id`),
|
|---|
| | | KEY `person_id` (`person_id`) USING BTREE,
|
|---|
| | | KEY `visitor_type_id` (`visitor_type_id`) USING BTREE,
|
|---|
| | | CONSTRAINT `visit_person_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`),
|
|---|
| | | CONSTRAINT `visit_person_type_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `visit_person_type_ibfk_3` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
| | |
|
|---|
| | | -- ----------------------------
|
|---|
| | | -- Table structure for visit_stat
|
|---|
| | | -- ----------------------------
|
|---|
| | | DROP TABLE IF EXISTS `visit_stat`;
|
|---|
| | | CREATE TABLE `visit_stat` (
|
|---|
| | | `person_id` int(10) NOT NULL COMMENT '人员主键',
|
|---|
| | | `register_company_id` int(10) NOT NULL COMMENT '注册公司主键',
|
|---|
| | | `total_count` int(10) DEFAULT NULL COMMENT '来访公司次数',
|
|---|
| | | PRIMARY KEY (`person_id`,`register_company_id`),
|
|---|
| | | KEY `person_id` (`person_id`),
|
|---|
| | | KEY `register_company_id` (`register_company_id`),
|
|---|
| | | CONSTRAINT `visit_stat_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
|
|---|
| | | CONSTRAINT `visit_stat_ibfk_2` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`)
|
|---|
| | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|---|
