|
#-----------------------------我是分割线-------------------------------------------
|
|
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 '国籍',
|
province CHAR(20) COMMENT '省',
|
city 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)
|
)
|
|
|
|
|
|
|
|
|
|
|
|
