/*
|
SQLyog Ultimate v12.09 (64 bit)
|
MySQL - 5.7.17 : Database - face_discern
|
*********************************************************************
|
*/
|
|
/*!40101 SET NAMES utf8 */;
|
|
/*!40101 SET SQL_MODE=''*/;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
CREATE DATABASE /*!32312 IF NOT EXISTS*/`face_discern` /*!40100 DEFAULT CHARACTER SET utf8 */;
|
|
USE `face_discern`;
|
|
/*Table structure for table `t_calling` */
|
|
DROP TABLE IF EXISTS `t_calling`;
|
|
CREATE TABLE `t_calling` (
|
`ID` int(32) NOT NULL COMMENT '签到事件主键',
|
`callDT` datetime DEFAULT NULL COMMENT '呼叫时间',
|
`callerPersonID` int(32) DEFAULT NULL COMMENT '呼叫人员主键',
|
`calleePersonID` int(32) DEFAULT NULL COMMENT '被呼叫人员主键',
|
PRIMARY KEY (`ID`),
|
KEY `con_calling_callerPersonID` (`callerPersonID`),
|
KEY `con_calling_calleePersonID` (`calleePersonID`),
|
CONSTRAINT `con_calling_calleePersonID` FOREIGN KEY (`calleePersonID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_calling_callerPersonID` FOREIGN KEY (`callerPersonID`) REFERENCES `t_person` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_calling` */
|
|
/*Table structure for table `t_checking` */
|
|
DROP TABLE IF EXISTS `t_checking`;
|
|
CREATE TABLE `t_checking` (
|
`ID` int(32) NOT NULL COMMENT '签到事件主键',
|
`statusID` tinyint(2) DEFAULT NULL COMMENT '签到状态',
|
`statusDT` datetime DEFAULT NULL COMMENT '签到时间',
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
`placeID` int(32) DEFAULT NULL COMMENT '签到位置',
|
PRIMARY KEY (`ID`),
|
KEY `con_checking_personID` (`personID`),
|
KEY `con_checking_placeID` (`placeID`),
|
CONSTRAINT `con_checking_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_checking_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_checking` */
|
|
/*Table structure for table `t_checkingcompany` */
|
|
DROP TABLE IF EXISTS `t_checkingcompany`;
|
|
CREATE TABLE `t_checkingcompany` (
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
`companyID` int(32) DEFAULT NULL COMMENT '公司主键',
|
KEY `con_checkingCompany_personID` (`personID`),
|
KEY `con_checkingCompany_registerCompanyID` (`companyID`),
|
CONSTRAINT `con_checkingCompany_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_checkingCompany_registerCompanyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_checkingcompany` */
|
|
/*Table structure for table `t_company` */
|
|
DROP TABLE IF EXISTS `t_company`;
|
|
CREATE TABLE `t_company` (
|
`ID` int(10) NOT NULL COMMENT '公司主键',
|
`NAME` varchar(255) DEFAULT NULL COMMENT '公司名称',
|
PRIMARY KEY (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_company` */
|
|
/*Table structure for table `t_department` */
|
|
DROP TABLE IF EXISTS `t_department`;
|
|
CREATE TABLE `t_department` (
|
`ID` int(10) NOT NULL COMMENT '部门主键',
|
`label` varchar(40) DEFAULT NULL COMMENT '部门名称',
|
`labelSpell` char(2) DEFAULT NULL COMMENT '部门首字母',
|
PRIMARY KEY (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_department` */
|
|
/*Table structure for table `t_employeeface` */
|
|
DROP TABLE IF EXISTS `t_employeeface`;
|
|
CREATE TABLE `t_employeeface` (
|
`ID` int(32) NOT NULL COMMENT '员工图片主键',
|
`registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
`imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
|
PRIMARY KEY (`ID`),
|
KEY `con_employeeFace_personID` (`personID`),
|
KEY `con_employeeFace_registerCompanyID` (`registerCompanyID`),
|
CONSTRAINT `con_employeeFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_employeeFace_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_employeeface` */
|
|
/*Table structure for table `t_image` */
|
|
DROP TABLE IF EXISTS `t_image`;
|
|
CREATE TABLE `t_image` (
|
`personID` int(10) DEFAULT NULL COMMENT '人员主键',
|
`imageTye` varchar(32) DEFAULT NULL COMMENT '图片类型',
|
`imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
|
KEY `con_image_personID` (`personID`),
|
CONSTRAINT `con_image_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_image` */
|
|
/*Table structure for table `t_person` */
|
|
DROP TABLE IF EXISTS `t_person`;
|
|
CREATE TABLE `t_person` (
|
`ID` int(10) NOT NULL COMMENT '人员主键',
|
`NAME` varchar(40) DEFAULT NULL COMMENT '人员名字',
|
`nameSpell` char(2) DEFAULT NULL COMMENT '人员名首字母',
|
`gender` tinyint(1) DEFAULT NULL COMMENT '性别',
|
`country` char(20) 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 '座机电话',
|
`mobilePhone` char(15) DEFAULT NULL COMMENT '移动电话',
|
`identityType` char(40) DEFAULT NULL COMMENT '证件类型',
|
`identify` varchar(255) DEFAULT NULL COMMENT '证件编号',
|
`companyID` int(10) DEFAULT NULL COMMENT '公司主键',
|
`departmentID` int(10) DEFAULT NULL COMMENT '部门主键',
|
`postID` int(10) DEFAULT NULL COMMENT '职务主键',
|
`remark` varchar(255) DEFAULT NULL COMMENT '人员备注消息',
|
`warning` varchar(255) DEFAULT NULL COMMENT '危险人员警告消息',
|
PRIMARY KEY (`ID`),
|
KEY `con_person_companyID` (`companyID`),
|
KEY `con_person_departmentID` (`departmentID`),
|
KEY `con_person_postID` (`postID`),
|
CONSTRAINT `con_person_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`),
|
CONSTRAINT `con_person_departmentID` FOREIGN KEY (`departmentID`) REFERENCES `t_department` (`ID`),
|
CONSTRAINT `con_person_postID` FOREIGN KEY (`postID`) REFERENCES `t_post` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_person` */
|
|
/*Table structure for table `t_personext` */
|
|
DROP TABLE IF EXISTS `t_personext`;
|
|
CREATE TABLE `t_personext` (
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
KEY `con_personExt_personID` (`personID`),
|
CONSTRAINT `con_personExt_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_personext` */
|
|
/*Table structure for table `t_place` */
|
|
DROP TABLE IF EXISTS `t_place`;
|
|
CREATE TABLE `t_place` (
|
`ID` int(10) NOT NULL COMMENT '设备主键',
|
`companyID` int(10) DEFAULT NULL COMMENT '公司主键',
|
`label` varchar(40) DEFAULT NULL COMMENT '设备地点',
|
`authorizationID` varchar(40) DEFAULT NULL COMMENT '设备授权码',
|
PRIMARY KEY (`ID`),
|
KEY `con_place_companyID` (`companyID`),
|
CONSTRAINT `con_place_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_place` */
|
|
/*Table structure for table `t_post` */
|
|
DROP TABLE IF EXISTS `t_post`;
|
|
CREATE TABLE `t_post` (
|
`ID` int(10) NOT NULL COMMENT '职位主键',
|
`label` varchar(40) DEFAULT NULL COMMENT '职位名称',
|
PRIMARY KEY (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_post` */
|
|
/*Table structure for table `t_visit` */
|
|
DROP TABLE IF EXISTS `t_visit`;
|
|
CREATE TABLE `t_visit` (
|
`ID` int(32) NOT NULL COMMENT '访问事件主键',
|
`visitorPersonID` int(32) DEFAULT NULL COMMENT '访问人员主键',
|
`visiteePersonID` int(32) DEFAULT NULL COMMENT '被访问人员主键',
|
`matterID` int(32) DEFAULT NULL COMMENT '访问事由主键',
|
`remark` varchar(255) DEFAULT NULL COMMENT '访问备注',
|
`lastStatus` int(40) DEFAULT NULL COMMENT '最新的访问状态',
|
`startTime` datetime DEFAULT NULL COMMENT '来访时间',
|
`lastTime` datetime DEFAULT NULL COMMENT '签离时间',
|
`visitorTypeLabel` varchar(32) DEFAULT NULL COMMENT '访客类型',
|
`warning` varchar(255) DEFAULT NULL COMMENT '当前访问的警告状态',
|
PRIMARY KEY (`ID`),
|
KEY `con_visit_visitorPersonID` (`visitorPersonID`),
|
KEY `con_visit_visiteePersonID` (`visiteePersonID`),
|
KEY `con_visit_matterID` (`matterID`),
|
CONSTRAINT `con_visit_matterID` FOREIGN KEY (`matterID`) REFERENCES `t_visitmatter` (`ID`),
|
CONSTRAINT `con_visit_visiteePersonID` FOREIGN KEY (`visiteePersonID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_visit_visitorPersonID` FOREIGN KEY (`visitorPersonID`) REFERENCES `t_person` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visit` */
|
|
/*Table structure for table `t_visitface` */
|
|
DROP TABLE IF EXISTS `t_visitface`;
|
|
CREATE TABLE `t_visitface` (
|
`ID` int(10) NOT NULL COMMENT '访客图片主键',
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
`imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
|
PRIMARY KEY (`ID`),
|
KEY `con_visitFace_personID` (`personID`),
|
CONSTRAINT `con_visitFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visitface` */
|
|
/*Table structure for table `t_visiting` */
|
|
DROP TABLE IF EXISTS `t_visiting`;
|
|
CREATE TABLE `t_visiting` (
|
`ID` int(32) NOT NULL COMMENT '访问事件流水ID',
|
`visitID` int(32) DEFAULT NULL COMMENT '访问事件ID',
|
`statusID` datetime DEFAULT NULL COMMENT '当前访问状态',
|
`statusDT` datetime DEFAULT NULL COMMENT '更新状态时间',
|
`placeID` int(32) DEFAULT NULL COMMENT '设备位置主键',
|
PRIMARY KEY (`ID`),
|
KEY `con_visiting_placeID` (`placeID`),
|
CONSTRAINT `con_visiting_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visiting` */
|
|
/*Table structure for table `t_visitmatter` */
|
|
DROP TABLE IF EXISTS `t_visitmatter`;
|
|
CREATE TABLE `t_visitmatter` (
|
`ID` int(32) NOT NULL COMMENT '访问事由主键',
|
`companyID` int(32) DEFAULT NULL COMMENT '公司主键',
|
`label` varchar(40) DEFAULT NULL COMMENT '访问事由',
|
`available` tinyint(1) DEFAULT NULL COMMENT '是否是通用事由',
|
PRIMARY KEY (`ID`),
|
KEY `con_visitMatter_companyID` (`companyID`),
|
CONSTRAINT `con_visitMatter_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visitmatter` */
|
|
/*Table structure for table `t_visitortype` */
|
|
DROP TABLE IF EXISTS `t_visitortype`;
|
|
CREATE TABLE `t_visitortype` (
|
`ID` int(32) NOT NULL COMMENT '访客类型主键',
|
`label` varchar(32) DEFAULT NULL COMMENT '访客类型',
|
`registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
PRIMARY KEY (`ID`),
|
KEY `con_visitorType_personID` (`personID`),
|
KEY `con_visitorType_registerCompanyID` (`registerCompanyID`),
|
CONSTRAINT `con_visitorType_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_visitorType_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visitortype` */
|
|
/*Table structure for table `t_visitstat` */
|
|
DROP TABLE IF EXISTS `t_visitstat`;
|
|
CREATE TABLE `t_visitstat` (
|
`personID` int(32) DEFAULT NULL COMMENT '人员主键',
|
`registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
|
`totalCount` int(20) DEFAULT NULL COMMENT '来访公司次数',
|
KEY `con_visitStat_personID` (`personID`),
|
KEY `con_visitStat_registerCompanyID` (`registerCompanyID`),
|
CONSTRAINT `con_visitStat_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
|
CONSTRAINT `con_visitStat_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/*Data for the table `t_visitstat` */
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
