/*
|
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;
|
