286 lines
13 KiB
SQL
286 lines
13 KiB
SQL
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Люди
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник людей
|
|||
|
|
CREATE TABLE person (
|
|||
|
|
person_id serial PRIMARY KEY,
|
|||
|
|
-- фамилия
|
|||
|
|
last_name text NOT NULL CHECK (default_word_check (last_name)),
|
|||
|
|
-- имя
|
|||
|
|
first_name text NOT NULL CHECK (default_word_check (first_name)),
|
|||
|
|
-- отчество
|
|||
|
|
middle_name text CHECK (default_word_check (middle_name)),
|
|||
|
|
-- дата рождения
|
|||
|
|
birth_date date NOT NULL CHECK (birth_date <= CURRENT_DATE),
|
|||
|
|
-- пол
|
|||
|
|
gender string NOT NULL,
|
|||
|
|
-- место жительства
|
|||
|
|
address text NOT NULL CHECK (default_str_check (address))
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник пациентов
|
|||
|
|
CREATE TABLE patient (
|
|||
|
|
patient_id int PRIMARY KEY REFERENCES person (person_id),
|
|||
|
|
policy_number text NOT NULL CHECK (policy_number ~ '^[0-9]{16}$')
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник врачей
|
|||
|
|
CREATE TABLE doctor (
|
|||
|
|
doctor_id int PRIMARY KEY REFERENCES person (person_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE registrar (
|
|||
|
|
registrar_id int PRIMARY KEY REFERENCES person (person_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник единиц измерения для числовых результатов
|
|||
|
|
CREATE TABLE unit (
|
|||
|
|
unit_id serial PRIMARY KEY,
|
|||
|
|
name text NOT NULL CHECK (name ~ '^[а-яА-ЯёЁ%^/]+$' AND str_length_between (name, 1, 16)),
|
|||
|
|
UNIQUE (name)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Назначения
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Базовая таблица назначения (TPT)
|
|||
|
|
CREATE TABLE prescription (
|
|||
|
|
prescription_id serial PRIMARY KEY,
|
|||
|
|
-- пациент
|
|||
|
|
patient_id int NOT NULL REFERENCES patient (patient_id) ON DELETE CASCADE,
|
|||
|
|
-- врач
|
|||
|
|
doctor_id int NOT NULL REFERENCES doctor (doctor_id) ON DELETE RESTRICT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Обследования
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник типов обследований (Общий анализ крови", "ЭКГ", "УЗИ печени")
|
|||
|
|
CREATE TABLE examination_type (
|
|||
|
|
examination_type_id serial PRIMARY KEY,
|
|||
|
|
-- название типа обследования (одно слово, без пробелов)
|
|||
|
|
name text NOT NULL CHECK (default_word_check (name)),
|
|||
|
|
-- категория обследования
|
|||
|
|
category text NOT NULL,
|
|||
|
|
UNIQUE (name)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Общая таблица результатов обследований (TPT)
|
|||
|
|
CREATE TABLE result (
|
|||
|
|
result_id serial PRIMARY KEY,
|
|||
|
|
examination_id int NOT NULL REFERENCES examination_type (examination_type_id) ON DELETE CASCADE,
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Журнал фактических обследований пациентов
|
|||
|
|
CREATE TABLE examination_type_prescription (
|
|||
|
|
examination_prescription_id serial PRIMARY KEY,
|
|||
|
|
prescription_id int NOT NULL REFERENCES prescription (prescription_id) ON DELETE CASCADE,
|
|||
|
|
result_id int REFERENCES result (result_id) ON DELETE CASCADE,
|
|||
|
|
-- тип обследования
|
|||
|
|
examination_type_id int NOT NULL REFERENCES examination_type (examination_type_id) ON DELETE CASCADE,
|
|||
|
|
UNIQUE (prescription_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Фактические результаты обследований
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
|
|||
|
|
-- Числовой результат обследования пациента (только один на обследование)
|
|||
|
|
CREATE TABLE numeric_result (
|
|||
|
|
-- обследование пациента
|
|||
|
|
result_id int PRIMARY KEY REFERENCES result (result_id) ON DELETE CASCADE,
|
|||
|
|
-- значение результата
|
|||
|
|
value numeric NOT NULL,
|
|||
|
|
-- единица измерения
|
|||
|
|
unit_id int NOT NULL REFERENCES unit (unit_id) ON DELETE RESTRICT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Нормальные результаты обследований
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Качественный результат обследования пациента (только один на обследование)
|
|||
|
|
CREATE TABLE qualitative_result (
|
|||
|
|
-- обследование пациента
|
|||
|
|
result_id int PRIMARY KEY REFERENCES result (result_id) ON DELETE CASCADE,
|
|||
|
|
-- признак отклонения
|
|||
|
|
value boolean NOT NULL
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Общая таблица для справочника референсных (нормальных) значений (TPT)
|
|||
|
|
CREATE TABLE reference_value (
|
|||
|
|
reference_value_id serial PRIMARY KEY,
|
|||
|
|
examination_type_id int NOT NULL REFERENCES examination_type (examination_type_id) ON DELETE CASCADE,
|
|||
|
|
gender text, -- пол (если норма специфична)
|
|||
|
|
age_min int, -- нижняя граница возраста (если есть зависимость)
|
|||
|
|
age_max int, -- верхняя граница возраста (если есть зависимость)
|
|||
|
|
CHECK (
|
|||
|
|
(age_min IS NULL OR age_max IS NULL) -- если один из параметров не задан, не сравниваем
|
|||
|
|
OR (age_min < age_max) -- если оба заданы, min < max
|
|||
|
|
),
|
|||
|
|
UNIQUE (examination_type_id, gender, age_min, age_max)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник референсных (нормальных) значений для числовых обследований
|
|||
|
|
CREATE TABLE reference_numeric_value (
|
|||
|
|
reference_value_id int PRIMARY KEY REFERENCES reference_value (reference_value_id) ON DELETE CASCADE,
|
|||
|
|
min_value numeric,
|
|||
|
|
max_value numeric,
|
|||
|
|
unit_id int NOT NULL REFERENCES unit (unit_id) ON DELETE RESTRICT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник референсных (нормальных) значений для качественных обследований
|
|||
|
|
CREATE TABLE reference_qualitative_value (
|
|||
|
|
reference_value_id int PRIMARY KEY REFERENCES reference_value (reference_value_id) ON DELETE CASCADE,
|
|||
|
|
description_true text NOT NULL CHECK (default_word_check (description_true)),
|
|||
|
|
description_false text NOT NULL CHECK (default_word_check (description_false)),
|
|||
|
|
UNIQUE (description_true, description_false)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Синдромы
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник синдромов
|
|||
|
|
CREATE TABLE syndrome_type (
|
|||
|
|
syndrome_type_id serial PRIMARY KEY,
|
|||
|
|
name text NOT NULL CHECK (default_word_check (name)),
|
|||
|
|
UNIQUE (name)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Соответствие синдромов и типов обследований (определяет, какое обследование позволяет определить синдром)
|
|||
|
|
CREATE TABLE syndrome_examination_type (
|
|||
|
|
syndrome_id int PRIMARY KEY REFERENCES syndrome_type (syndrome_type_id) ON DELETE CASCADE,
|
|||
|
|
examination_type_id int NOT NULL REFERENCES examination_type (examination_type_id) ON DELETE CASCADE,
|
|||
|
|
UNIQUE (examination_type_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Болезни и диагнозы
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник заболеваний
|
|||
|
|
CREATE TABLE disease (
|
|||
|
|
disease_id serial PRIMARY KEY,
|
|||
|
|
-- русское наименование
|
|||
|
|
name text NOT NULL CHECK (default_str_check (name)),
|
|||
|
|
-- код МКБ в формате A12.3
|
|||
|
|
icd_code text NOT NULL CHECK (icd_code ~ '^[A-Z][0-9]{2}\.[0-9]$'),
|
|||
|
|
UNIQUE (name),
|
|||
|
|
UNIQUE (icd_code)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Соответствие заболеваний и синдромов
|
|||
|
|
CREATE TABLE disease_syndrome (
|
|||
|
|
disease_syndrome_id SERIAL PRIMARY KEY,
|
|||
|
|
disease_id int NOT NULL REFERENCES disease (disease_id) ON DELETE CASCADE,
|
|||
|
|
syndrome_id int NOT NULL REFERENCES syndrome_type (syndrome_type_id) ON DELETE CASCADE,
|
|||
|
|
UNIQUE (disease_id, syndrome_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Госпитализация
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Госпитализация (начало)
|
|||
|
|
CREATE TABLE hospital_admission (
|
|||
|
|
hospital_admission_id serial PRIMARY KEY,
|
|||
|
|
-- пациент
|
|||
|
|
patient_id int NOT NULL REFERENCES patient (patient_id) ON DELETE CASCADE,
|
|||
|
|
-- предварительный диагноз
|
|||
|
|
preliminary_disease_id int NOT NULL REFERENCES disease (disease_id),
|
|||
|
|
-- дата и время поступления
|
|||
|
|
admission_datetime timestamp NOT NULL CHECK (admission_datetime <= CURRENT_TIMESTAMP)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Завершение госпитализации
|
|||
|
|
CREATE TABLE hospital_discharge (
|
|||
|
|
hospital_discharge_id serial PRIMARY KEY,
|
|||
|
|
hospital_admission_id int NOT NULL REFERENCES hospital_admission (hospital_admission_id) ON DELETE CASCADE,
|
|||
|
|
-- дата и время выписки/смерти
|
|||
|
|
discharge_datetime timestamp NOT NULL CHECK (discharge_datetime <= CURRENT_TIMESTAMP),
|
|||
|
|
final_disease_id int NOT NULL REFERENCES disease (disease_id),
|
|||
|
|
-- причина завершения
|
|||
|
|
reason text NOT NULL
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Лекарства
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник способов приёма лекарств
|
|||
|
|
CREATE TABLE drug_intake_method (
|
|||
|
|
drug_intake_method_id serial PRIMARY KEY,
|
|||
|
|
-- наименование способа (например, "перорально", "внутримышечно", "внутривенно", "ингаляционно", "ректально")
|
|||
|
|
name text NOT NULL CHECK (default_word_check (name)),
|
|||
|
|
UNIQUE (name)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Справочник лекарств
|
|||
|
|
CREATE TABLE drug_type (
|
|||
|
|
drug_type_id serial PRIMARY KEY,
|
|||
|
|
international_name_ru text NOT NULL CHECK (default_word_check (international_name_ru)),
|
|||
|
|
international_name_en text CHECK (default_word_check (international_name_en)),
|
|||
|
|
international_name_la text CHECK (default_word_check (international_name_la)),
|
|||
|
|
brand_name_ru text CHECK (default_word_check (brand_name_ru)),
|
|||
|
|
UNIQUE (international_name_ru),
|
|||
|
|
UNIQUE (international_name_en),
|
|||
|
|
UNIQUE (international_name_la),
|
|||
|
|
UNIQUE (brand_name_ru)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Журнал фактических назначений лекарств пациенту
|
|||
|
|
CREATE TABLE drug_type_prescription (
|
|||
|
|
drug_prescription_id serial PRIMARY KEY,
|
|||
|
|
prescription_id int NOT NULL REFERENCES prescription (prescription_id) ON DELETE CASCADE,
|
|||
|
|
drug_type_id int NOT NULL REFERENCES drug_type (drug_type_id) ON DELETE RESTRICT,
|
|||
|
|
-- количество вещества за приём
|
|||
|
|
dose numeric NOT NULL CHECK (dose > 0),
|
|||
|
|
-- единица измерения дозы
|
|||
|
|
dose_unit_id int NOT NULL REFERENCES unit (unit_id) ON DELETE RESTRICT,
|
|||
|
|
-- способ приёма
|
|||
|
|
intake_method_id int NOT NULL REFERENCES drug_intake_method (drug_intake_method_id) ON DELETE RESTRICT,
|
|||
|
|
-- длительность приёма (в днях)
|
|||
|
|
duration_days int NOT NULL CHECK (duration_days > 0),
|
|||
|
|
UNIQUE (prescription_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
-- Процедуры (для пациента)
|
|||
|
|
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|
|||
|
|
--
|
|||
|
|
-- Справочник типов процедур
|
|||
|
|
CREATE TABLE procedure_type (
|
|||
|
|
procedure_type_id serial PRIMARY KEY,
|
|||
|
|
-- наименование процедуры (например, "физиотерапия")
|
|||
|
|
name text NOT NULL CHECK (default_word_check (name)),
|
|||
|
|
UNIQUE (name)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Журнал фактических назначений процедур пациенту
|
|||
|
|
CREATE TABLE procedure_type_prescription (
|
|||
|
|
procedure_prescription_id serial PRIMARY KEY,
|
|||
|
|
prescription_id int NOT NULL REFERENCES prescription (prescription_id) ON DELETE CASCADE,
|
|||
|
|
procedure_type_id int NOT NULL REFERENCES procedure_type (procedure_type_id) ON DELETE RESTRICT,
|
|||
|
|
scheduled_datetime timestamp NOT NULL,
|
|||
|
|
UNIQUE (prescription_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
insert into doctor (doctor_id)
|
|||
|
|
values (1),
|
|||
|
|
(2),
|
|||
|
|
(3);
|
|||
|
|
|
|||
|
|
insert into registrar (registrar_id)
|
|||
|
|
values (4),
|
|||
|
|
(5),
|
|||
|
|
(6);
|