You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
183 lines
7.4 KiB
183 lines
7.4 KiB
create table department
|
|
(
|
|
department_id varchar(20) not null,
|
|
phone varchar(20),
|
|
dept_type varchar(30),
|
|
dept_name varchar(50) not null unique,
|
|
address varchar(100),
|
|
primary key (department_id)
|
|
);
|
|
create table doctor
|
|
(
|
|
gender varchar(10),
|
|
department_id varchar(20),
|
|
doctor_id varchar(20) not null,
|
|
employee_no varchar(20) unique,
|
|
title varchar(30),
|
|
name varchar(50) not null,
|
|
username varchar(50) unique,
|
|
password_hash varchar(100) not null,
|
|
schedule varchar(200),
|
|
specialty varchar(200),
|
|
primary key (doctor_id)
|
|
);
|
|
create table emr
|
|
(
|
|
created_at timestamp(6),
|
|
updated_at timestamp(6),
|
|
archived_status varchar(20),
|
|
emr_id varchar(20) not null,
|
|
patient_id varchar(20),
|
|
record_id varchar(20) unique,
|
|
signer varchar(100),
|
|
content jsonb,
|
|
primary key (emr_id)
|
|
);
|
|
create table examination_item
|
|
(
|
|
price numeric(10, 2),
|
|
department_id varchar(20),
|
|
item_id varchar(20) not null,
|
|
item_type varchar(30),
|
|
item_name varchar(100) not null,
|
|
applicable_disease varchar(200),
|
|
description TEXT,
|
|
primary key (item_id)
|
|
);
|
|
create table examination_result
|
|
(
|
|
exam_time timestamp(6),
|
|
report_time timestamp(6),
|
|
item_id varchar(20),
|
|
patient_id varchar(20),
|
|
record_id varchar(20),
|
|
report_status varchar(20),
|
|
request_doctor_id varchar(20),
|
|
result_id varchar(20) not null,
|
|
view_status varchar(20),
|
|
abnormal_hint varchar(50),
|
|
reference_range varchar(100),
|
|
data TEXT,
|
|
primary key (result_id)
|
|
);
|
|
create table medical_record
|
|
(
|
|
visit_time timestamp(6) not null,
|
|
doctor_id varchar(20),
|
|
patient_id varchar(20),
|
|
record_id varchar(20) not null,
|
|
reservation_id varchar(20) unique,
|
|
status varchar(20),
|
|
visit_department_id varchar(20),
|
|
chief_complaint varchar(200),
|
|
diagnosis varchar(200),
|
|
advice TEXT,
|
|
past_history TEXT,
|
|
present_illness TEXT,
|
|
primary key (record_id)
|
|
);
|
|
create table medicine
|
|
(
|
|
expire_date date,
|
|
stock integer,
|
|
stock_threshold integer,
|
|
unit_price numeric(10, 2),
|
|
medicine_id varchar(20) not null,
|
|
dosage_form varchar(30),
|
|
spec varchar(50),
|
|
manufacturer varchar(100),
|
|
name varchar(100) not null,
|
|
contraindication varchar(200),
|
|
description TEXT,
|
|
primary key (medicine_id)
|
|
);
|
|
create table patient
|
|
(
|
|
age integer,
|
|
registered_at timestamp(6),
|
|
gender varchar(10),
|
|
id_card varchar(18) unique,
|
|
patient_id varchar(20) not null,
|
|
phone varchar(20),
|
|
medical_card varchar(30),
|
|
name varchar(50) not null,
|
|
username varchar(50) unique,
|
|
password_hash varchar(100) not null,
|
|
primary key (patient_id)
|
|
);
|
|
create table payment
|
|
(
|
|
amount numeric(10, 2) not null,
|
|
insurance_amount numeric(10, 2),
|
|
self_pay_amount numeric(10, 2),
|
|
paid_at timestamp(6),
|
|
business_id varchar(20),
|
|
business_type varchar(20),
|
|
patient_id varchar(20),
|
|
payment_id varchar(20) not null,
|
|
status varchar(20),
|
|
method varchar(30),
|
|
details jsonb,
|
|
primary key (payment_id)
|
|
);
|
|
create table prescription
|
|
(
|
|
created_at timestamp(6),
|
|
doctor_id varchar(20),
|
|
patient_id varchar(20),
|
|
prescription_id varchar(20) not null,
|
|
record_id varchar(20),
|
|
status varchar(20),
|
|
remark varchar(200),
|
|
guidance TEXT,
|
|
primary key (prescription_id)
|
|
);
|
|
create table prescription_detail
|
|
(
|
|
quantity integer,
|
|
detail_id varchar(20) not null,
|
|
medicine_id varchar(20),
|
|
prescription_id varchar(20),
|
|
dose varchar(30),
|
|
duration varchar(30),
|
|
frequency varchar(30),
|
|
timing_hint varchar(50),
|
|
primary key (detail_id)
|
|
);
|
|
create table reservation
|
|
(
|
|
reservation_date date not null,
|
|
cancel_time timestamp(6),
|
|
department_id varchar(20),
|
|
doctor_id varchar(20),
|
|
patient_id varchar(20),
|
|
reservation_id varchar(20) not null,
|
|
reservation_type varchar(20),
|
|
status varchar(20) not null,
|
|
channel varchar(30),
|
|
timeslot varchar(30) not null,
|
|
reschedule_log TEXT,
|
|
primary key (reservation_id)
|
|
);
|
|
alter table if exists doctor add constraint FK75x47tyyeco3xj4cmlhj8v6ta foreign key (department_id) references department;
|
|
alter table if exists emr add constraint FKavap0qdm6nen0m7ni56886tdk foreign key (record_id) references medical_record;
|
|
alter table if exists emr add constraint FKjky6fib8ygnqxp8ksx1v2pn5c foreign key (patient_id) references patient;
|
|
alter table if exists examination_item add constraint FKs9vqfpmu12uxg9q4jgvbkiden foreign key (department_id) references department;
|
|
alter table if exists examination_result add constraint FKigjygc2wxytikb59fjgn6gynq foreign key (item_id) references examination_item;
|
|
alter table if exists examination_result add constraint FKhrbbffp9vgoke6ef8dl2nywo7 foreign key (record_id) references medical_record;
|
|
alter table if exists examination_result add constraint FKd8d66gm6uv0a0tl0j4016b7g foreign key (patient_id) references patient;
|
|
alter table if exists examination_result add constraint FKat5uar9yuqny0ssjn56wr35qh foreign key (request_doctor_id) references doctor;
|
|
alter table if exists medical_record add constraint FKmommgymv6rayvbje0hp4c6g8w foreign key (doctor_id) references doctor;
|
|
alter table if exists medical_record add constraint FKt0lf3feuiurr73bpln2n6x0v foreign key (patient_id) references patient;
|
|
alter table if exists medical_record add constraint FKoob1ft0iq1dkhp4590kx26874 foreign key (reservation_id) references reservation;
|
|
alter table if exists medical_record add constraint FK963d8pucvml6d2utcu0bo8qrh foreign key (visit_department_id) references department;
|
|
alter table if exists payment add constraint FK8t7hyujfhrl2jneu9jayv89tq foreign key (patient_id) references patient;
|
|
alter table if exists prescription add constraint FK1ppr8greedyrey8nchpr0v4dn foreign key (doctor_id) references doctor;
|
|
alter table if exists prescription add constraint FK1omitb6d3jfxbtcl9h3ufpvpu foreign key (record_id) references medical_record;
|
|
alter table if exists prescription add constraint FKqrlh184tfvdi95erwl65p4xj3 foreign key (patient_id) references patient;
|
|
alter table if exists prescription_detail add constraint FKik2mm986ygebo4kq2kvl3hcdu foreign key (medicine_id) references medicine;
|
|
alter table if exists prescription_detail add constraint FKdq2yagbksdomo6t1o1b8g3poe foreign key (prescription_id) references prescription;
|
|
alter table if exists reservation add constraint FK4aescfu1w2acmdn04t0kfqnmp foreign key (department_id) references department;
|
|
alter table if exists reservation add constraint FKmh96fg24x7cdhex42l051fmhp foreign key (doctor_id) references doctor;
|
|
alter table if exists reservation add constraint FKrrjvkskqqxgliwmqgbl3ijc4n foreign key (patient_id) references patient;
|