user
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); |