核酸检测系统数据库设计

表设计

受检人员表

CREATE TABLE CheckedUser  (
  ch_id varchar(20),
  ch_account varchar(20),
  ch_password varchar(20),
  ch_check_status integer,
  ch_health_status integer,
  ch_name varchar(255),
  ch_identity_type integer ,
  ch_identity varchar(50),
  ch_face varchar(255),
  ch_QRcode varchar(255) ,
  ch_entry_time datetime,
  PRIMARY KEY (ch_id) 
)

高级人员表

CREATE TABLE StaffUser  (
  st_id varchar(20),
  st_account varchar(20),
  st_password varchar(20),
  st_post varchar(10),
  st_status integer,
  st_name varchar(255),
  st_identity_type integer ,
  st_identity varchar(50),
  st_phone integer,
  st_address varchar(255),
  PRIMARY KEY (st_id) 
)

实验室表

CREATE TABLE Laboratory  (
  lab_id varchar(20),
  lab_name varchar(255),
  lab_address varchar(255),
  PRIMARY KEY (lab_id) 
)

核酸检测点表

CREATE TABLE PCRPoint  (
  po_id varchar(20),
  po_name varchar(255),
  po_address varchar(255),
  po_status integer,
  PRIMARY KEY (po_id) 
)

核酸安排表

CREATE TABLE TaskArrangement  (
  te_id varchar(20),
  te_time varchar(255),
  te_address varchar(255),
  te_tube_numbser integer,
  te_medical_id varchar(20),
  te_manager_id varchar(20),
  te_point_id varchar(20),
  te_delivery_id varchar(20),
  te_laboratory_id varchar(20),
  PRIMARY KEY (te_id) 
)

核酸安排与医护人员对照表

CREATE TABLE TasktMedicalContrast  (
  con_id  varchar(20),
  tesk_id  varchar(20),
  medical_id  varchar(20),
  PRIMARY KEY (con_id) 
)

采样信息表

CREATE TABLE PCRSample  (
  sam_id varchar(20),
  tube_id varchar(20),
  tube_production_date date,
  sam_type  integer,
  sam_mixed integer,
  sam_test_number  integer,
  sam_collection_time datetime,
  medical_id  varchar(20),
  sam_result  integer ,
  sam_result_time datetime,
  inspector_id varchar(20),
  logistics_id  varchar(20),
  PRIMARY KEY (sam_id) 
)

采样信息与人员对照表

CREATE TABLE SampleCheckedContrast  (
  con_id  varchar(20),
  ch_id  varchar(20),
  sam_id  varchar(20),
  PRIMARY KEY (con_id) 
)

物流信息表

CREATE TABLE Logistics  (
  logi_id  varchar(20),
  logi_status varchar(20),
  delivery_id varchar(20),
  point_id varchar(20),
  logi_point_time datetime, 
  laboratory_id varchar(20),
  inspector_id varchar(20),
  logi_lab_time datetime,
  PRIMARY KEY (logi_id) 
)

核酸统计结果表

CREATE TABLE PCRStatisticalResults  (
  stati_id  varchar(20),
  stati_date date, 
  stati_area varchar(255),
  stati_total_number integer,
  stati_positive_number integer,
  PRIMARY KEY (stati_id) 
)

消息表

CREATE TABLE PCRMessages  (
  mes_id  varchar(20),
  mes_manager_id  varchar(20),
  mes_time datetime,
  mes_content varchar(255),
  mes_status integer,
  PRIMARY KEY (mes_id) 
)

设置索引

消息表内容处

use PCRWork
go
create index index_content on PCRMessages(mes_content)
go
exec sp_helpindex PCRMessages
go

核酸检测点名称处

use PCRWork
go
create unique index  index_PCRPoint_name on PCRPoint(po_name)
go
exec sp_helpindex PCRPoint
go

采样信息核酸结果处

use PCRWork
go
create index  index_PCRSample_res on PCRSample(sam_result)
go
exec sp_helpindex PCRSample
go

视图

阳性病例信息

use PCRWork
go
create view V_positive_user
as
select u.ch_name,u.ch_identity,u.ch_identity_type,u.ch_check_status,u.ch_health_status
from CheckedUser u,PCRSample s
where s.sam_result=1
go

select *from V_positive_user
go

政府系统管理人员的信息

use PCRWork
go
create view V_manager_user
as
select *from StaffUser where st_post='1' 
go

select *from V_manager_user
go

医护人员的信息

create view V_medical_user
as
select *from StaffUser where st_post='2'

物流人员的信息

create view V_delivery_user
as
select *from StaffUser where st_post='3'

实验室检测人员的信息

create view V_inspector_user
as
select *from StaffUser where st_post='4'

存储过程

查看今日的核酸结果为阳性的采样信息


-- 查看今日的核酸结果为阳性的采样信息
CREATE PROCEDURE Pstu_positive_sample
AS
SELECT *FROM PCRSample
WHERE CONVERT(varchar(10),sam_collection_time,120) =CONVERT(varchar(10),GETDATE(),120) 
      AND sam_result=1
GO
-- 执行
exec Pstu_positive_sample

输入试管编号，查看该试管的受检人员

CREATE PROCEDURE Pstu_sample_checked
@tube_id varchar(20)
AS
SELECT u.ch_name,u.ch_identity,u.ch_identity_type,u.ch_check_status,u.ch_health_status
FROM PCRSample  s,SampleCheckedContrast  c,CheckedUser  u
WHERE c.sam_id=s.sam_id and c.ch_id=u.ch_id
      and s.tube_id=@tube_id
go
-- 执行
exec Pstu_sample_checked @tube_id='XXXXX'

触发器

CREATE TRIGGER Tri_auto_update_positive
ON PCRSample  
AFTER UPDATE
AS
BEGIN
  IF UPDATE(sam_result)
    BEGIN
    DECLARE @sam_result integer
	DECLARE @sam_test_number integer
	DECLARE @sam_collection_time datetime 
    SELECT @sam_result=sam_result ,@sam_test_number=sam_test_number ,@sam_collection_time=sam_collection_time FROM inserted
    IF @sam_result=1
      BEGIN 
        UPDATE PCRStatisticalResults 
        SET stati_total_number = stati_total_number+@sam_test_number
        WHERE stati_date  =CONVERT(varchar(10),@sam_collection_time,120) 
      END
    END
END

禁止修改受检人员的个人二维码

CREATE   TRIGGER Tri_QRcode
ON CheckedUser  
AFTER  UPDATE
AS
IF UPDATE(ch_QRcode)
    BEGIN
      RAISERROR('不能修改个人信息二维码',16,2)
      ROLLBACK
    END
GO

数据库用户与角色

系统根据以下典型场景：
1.核酸检测点，2.受检者及物流人员个人设备，3.医院实验室，4.政府机关
将用户分为5类，角色及其权限如下：

  1. 受检人员：查看核酸信息、核酸检测点信息、消息发布记录，管理身份信息、受检人员表 。
  2. 政府机关管理人员：可以查看所有表，不可修改身份信息，样本人员关联表，物流信息表，其余表均可修改。
  3. 医护人员：管理采样信息表，管理医护人员表，查看核酸任务表，更新样本物流表。
  4. 物流人员：查看核酸安排表、检测站点表、实验室表、核酸安排和物流人员对照表，管理物流信息表、物流人员表。
  5. 实验室检测人员：查看实验室表、核酸统计结果表，管理采样信息表、实验室检测人员表，更新物流信息表。
posted @ 2022-12-18 11:58  kingwzun  阅读(179)  评论(0)  编辑  收藏  举报