From 1a5e4d619ac1c1f79b01cb34f5ee07391b89ddcf Mon Sep 17 00:00:00 2001 From: dupengyue <dupengyue@454eff88-639b-444f-9e54-f578c98de674> Date: 星期五, 03 三月 2017 16:09:20 +0800 Subject: [PATCH] 建表SQL --- VisitFace/script/face_discern.sql | 184 ++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 184 insertions(+), 0 deletions(-) diff --git a/VisitFace/script/face_discern.sql b/VisitFace/script/face_discern.sql new file mode 100644 index 0000000..b12c953 --- /dev/null +++ b/VisitFace/script/face_discern.sql @@ -0,0 +1,184 @@ + +#-----------------------------鎴戞槸鍒嗗壊绾�------------------------------------------- + +CREATE TABLE t_company( +ID VARCHAR(32) PRIMARY KEY COMMENT '鍏徃涓婚敭', +NAME VARCHAR(255) COMMENT '鍏徃鍚嶇О' +) + + +CREATE TABLE t_place( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁惧涓婚敭', +companyID VARCHAR(32) COMMENT '鍏徃涓婚敭', +label VARCHAR(40) COMMENT '璁惧鍦扮偣', +authorizationID VARCHAR(40) COMMENT '璁惧鎺堟潈鐮�', +CONSTRAINT con_place_companyID FOREIGN KEY(companyID) REFERENCES t_company(ID) +) + +CREATE TABLE t_department( +ID VARCHAR(32) PRIMARY KEY COMMENT '閮ㄩ棬涓婚敭', +label VARCHAR(40) COMMENT '閮ㄩ棬鍚嶇О', +labelSpell CHAR(2) COMMENT '閮ㄩ棬棣栧瓧姣�' +) + + +CREATE TABLE t_post( +ID VARCHAR(32) PRIMARY KEY COMMENT '鑱屼綅涓婚敭', +label VARCHAR(40) COMMENT '鑱屼綅鍚嶇О' +) + +CREATE TABLE t_person( +ID VARCHAR(32) PRIMARY KEY COMMENT '浜哄憳涓婚敭', +NAME VARCHAR(40) COMMENT '浜哄憳鍚嶅瓧', +nameSpell CHAR(2) COMMENT '浜哄憳鍚嶉瀛楁瘝', +gender TINYINT(1) COMMENT '鎬у埆', +country CHAR(20) COMMENT '鍥界睄', +birthday DATE COMMENT '鍑虹敓鏃ユ湡', +phone CHAR(12) COMMENT '搴ф満鐢佃瘽', +mobilePhone CHAR(15) COMMENT '绉诲姩鐢佃瘽', +identityType CHAR(40) COMMENT '璇佷欢绫诲瀷', +identify VARCHAR(255) COMMENT '璇佷欢缂栧彿', +companyID VARCHAR(32) COMMENT '鍏徃涓婚敭', +departmentID VARCHAR(32) COMMENT '閮ㄩ棬涓婚敭', +postID VARCHAR(32) COMMENT '鑱屽姟涓婚敭', +remark VARCHAR(255) COMMENT '浜哄憳澶囨敞娑堟伅', +warning VARCHAR(255) COMMENT '鍗遍櫓浜哄憳璀﹀憡娑堟伅', +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) +) + + +CREATE TABLE t_image( +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +imageTye VARCHAR(32) COMMENT '鍥剧墖绫诲瀷', +imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', +CONSTRAINT con_image_personID FOREIGN KEY(personID) REFERENCES t_person(ID) +) + +CREATE TABLE t_visitFace( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁垮鍥剧墖涓婚敭', +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', +CONSTRAINT con_visitFace_personID FOREIGN KEY(personID) REFERENCES t_person(ID) +) + + +DROP TABLE t_employeeFace + +CREATE TABLE t_employeeFace( +ID VARCHAR(32) PRIMARY KEY COMMENT '鍛樺伐鍥剧墖涓婚敭', +registerCompanyID VARCHAR(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', +CONSTRAINT con_employeeFace_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_employeeFace_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) +) + +DROP TABLE t_visitorType + +CREATE TABLE t_visitorType( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁垮绫诲瀷涓婚敭', +label VARCHAR(32) COMMENT '璁垮绫诲瀷', +registerCompanyID VARCHAR(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +CONSTRAINT con_visitorType_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_visitorType_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) +) + +CREATE TABLE t_visitStat( +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +registerCompanyID VARCHAR(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', +totalCount INTEGER(20) COMMENT '鏉ヨ鍏徃娆℃暟', +CONSTRAINT con_visitStat_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_visitStat_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) +) + +CREATE TABLE t_checkingCompany( +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +companyID VARCHAR(32) COMMENT '鍏徃涓婚敭', +CONSTRAINT con_checkingCompany_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_checkingCompany_registerCompanyID FOREIGN KEY(companyID) REFERENCES t_company(ID) +) + + +CREATE TABLE t_personExt( +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +CONSTRAINT con_personExt_personID FOREIGN KEY(personID) REFERENCES t_person(ID) +) + + + +CREATE TABLE t_checking( +ID VARCHAR(32) PRIMARY KEY COMMENT '绛惧埌浜嬩欢涓婚敭', +statusID INTEGER(2) COMMENT '绛惧埌鐘舵��', +statusDT DATETIME COMMENT '绛惧埌鏃堕棿', +personID VARCHAR(32) COMMENT '浜哄憳涓婚敭', +placeID VARCHAR(32) COMMENT '绛惧埌浣嶇疆', +CONSTRAINT con_checking_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_checking_placeID FOREIGN KEY(placeID) REFERENCES t_place(ID) +) + + +CREATE TABLE t_calling( +ID VARCHAR(32) PRIMARY KEY COMMENT '绛惧埌浜嬩欢涓婚敭', +callDT DATETIME COMMENT '鍛煎彨鏃堕棿', +callerPersonID VARCHAR(32) COMMENT '鍛煎彨浜哄憳涓婚敭', +calleePersonID VARCHAR(32) COMMENT '琚懠鍙汉鍛樹富閿�', +CONSTRAINT con_calling_callerPersonID FOREIGN KEY(callerPersonID) REFERENCES t_person(ID), +CONSTRAINT con_calling_calleePersonID FOREIGN KEY(calleePersonID) REFERENCES t_person(ID) +) + + + +#----------------------------鍚庨潰涓変釜------------------------------------------------- + + +CREATE TABLE t_visitMatter( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁块棶浜嬬敱涓婚敭', +companyID VARCHAR(32) COMMENT '鍏徃涓婚敭', +label VARCHAR(40) COMMENT '璁块棶浜嬬敱', +available TINYINT(1) COMMENT '鏄惁鏄�氱敤浜嬬敱', +CONSTRAINT con_visitMatter_companyID FOREIGN KEY(companyID) REFERENCES t_company(ID) +) + + + +CREATE TABLE t_visit( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁块棶浜嬩欢涓婚敭', +visitorPersonID VARCHAR(32) COMMENT '璁块棶浜哄憳涓婚敭', +visiteePersonID VARCHAR(32) COMMENT '琚闂汉鍛樹富閿�', +matterID VARCHAR(32) COMMENT '璁块棶浜嬬敱涓婚敭', +remark VARCHAR(255) COMMENT '璁块棶澶囨敞', +lastStatus INTEGER(40) COMMENT '鏈�鏂扮殑璁块棶鐘舵��', +startTime DATETIME COMMENT '鏉ヨ鏃堕棿', +lastTime DATETIME COMMENT '绛剧鏃堕棿', +visitorTypeLabel VARCHAR(32) COMMENT '璁垮绫诲瀷', +warning VARCHAR(255) COMMENT '褰撳墠璁块棶鐨勮鍛婄姸鎬�', +CONSTRAINT con_visit_visitorPersonID FOREIGN KEY(visitorPersonID) REFERENCES t_person(ID), +CONSTRAINT con_visit_visiteePersonID FOREIGN KEY(visiteePersonID) REFERENCES t_person(ID), +CONSTRAINT con_visit_matterID FOREIGN KEY(matterID) REFERENCES t_visitMatter(ID) +) + + + +CREATE TABLE t_visiting( +ID VARCHAR(32) PRIMARY KEY COMMENT '璁块棶浜嬩欢娴佹按ID', +visitID INTEGER(2) COMMENT '璁块棶浜嬩欢ID', +statusID DATETIME COMMENT '褰撳墠璁块棶鐘舵��', +statusDT DATETIME COMMENT '鏇存柊鐘舵�佹椂闂�', +placeID VARCHAR(32) COMMENT '璁惧浣嶇疆涓婚敭', +CONSTRAINT con_visiting_personID FOREIGN KEY(personID) REFERENCES t_person(ID), +CONSTRAINT con_visiting_placeID FOREIGN KEY(placeID) REFERENCES t_place(ID) +) + + + + + + + + + + + -- Gitblit v1.8.0