
#-----------------------------我是分割线-------------------------------------------

CREATE TABLE t_company(
ID INT(10) PRIMARY KEY COMMENT '公司主键',
NAME VARCHAR(255) COMMENT '公司名称'
)


CREATE TABLE t_place(
ID INT(10) PRIMARY KEY COMMENT '设备主键',
companyID INT(10) 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 INT(10) PRIMARY KEY COMMENT '部门主键',
label VARCHAR(40) COMMENT '部门名称',
labelSpell CHAR(2) COMMENT '部门首字母'
)


CREATE TABLE t_post(
ID INT(10) PRIMARY KEY COMMENT '职位主键',
label VARCHAR(40) COMMENT '职位名称'
)

CREATE TABLE t_person(
ID INT(10) 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 INT(10) COMMENT '公司主键',
departmentID INT(10) COMMENT '部门主键',
postID INT(10) 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 INT(10) 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 INT(10) PRIMARY KEY COMMENT '访客图片主键',
personID INT(32) COMMENT '人员主键',
imagePath VARCHAR(255) COMMENT '图片地址',
CONSTRAINT con_visitFace_personID FOREIGN KEY(personID) REFERENCES t_person(ID)
)



CREATE TABLE t_employeeFace(
ID INT(32) PRIMARY KEY COMMENT '员工图片主键',
registerCompanyID INT(32) COMMENT '注册公司主键',
personID INT(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)
)


CREATE TABLE t_visitorType(
ID INT(32) PRIMARY KEY COMMENT '访客类型主键',
label VARCHAR(32) COMMENT '访客类型',
registerCompanyID INT(32) COMMENT '注册公司主键',
personID INT(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 INT(32) COMMENT '人员主键',
registerCompanyID INT(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 INT(32) COMMENT '人员主键',
companyID INT(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 INT(32) COMMENT '人员主键',
CONSTRAINT con_personExt_personID FOREIGN KEY(personID) REFERENCES t_person(ID)
)



CREATE TABLE t_checking(
ID INT(32) PRIMARY KEY COMMENT '签到事件主键',
statusID TINYINT(2) COMMENT '签到状态',
statusDT DATETIME COMMENT '签到时间',
personID INT(32) COMMENT '人员主键',
placeID INT(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 INT(32) PRIMARY KEY COMMENT '签到事件主键',
callDT DATETIME COMMENT '呼叫时间',
callerPersonID INT(32) COMMENT '呼叫人员主键',
calleePersonID INT(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 INT(32) PRIMARY KEY COMMENT '访问事由主键',
companyID INT(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 INT(32) PRIMARY KEY COMMENT '访问事件主键',
visitorPersonID INT(32) COMMENT '访问人员主键',
visiteePersonID INT(32) COMMENT '被访问人员主键',
matterID INT(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 INT(32) PRIMARY KEY COMMENT '访问事件流水ID',
visitID INT(32) COMMENT '访问事件ID',
statusID DATETIME COMMENT '当前访问状态',
statusDT DATETIME COMMENT '更新状态时间',
placeID INT(32) COMMENT '设备位置主键',
CONSTRAINT con_visiting_placeID FOREIGN KEY(placeID) REFERENCES t_place(ID)
)











