SQLRU.net
Разработка приложений баз данных

Начало » Использование СУБД » Firebird, HQbird, InterBase » Нужна помощь с проектированием базы
Нужна помощь с проектированием базы [сообщение #353] Mon, 22 August 2022 15:25 Переход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
Комрады, нужна помощь. Есть табля с пациентами
CREATE TABLE PEOPLE (
    ID   INTEGER NOT NULL,
    FIO  VARCHAR(50)
);
ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY (ID);
и табли с определенными видами анализов (в реальном проекте их может быть пару десятков)
с примерно такой структурой:
CREATE TABLE LAB_1 (
    ID          INTEGER NOT NULL,
    FK_PEOLPLE  SMALLINT,
    FLD_DATE    TIMESTAMP DEFAULT current_timestamp NOT NULL,
    FLD_STR1    VARCHAR(10)
);
ALTER TABLE LAB_1 ADD CONSTRAINT PK_LAB_1 PRIMARY KEY (ID);
ALTER TABLE LAB_1 ADD CONSTRAINT FK_LAB_1_1 FOREIGN KEY (FK_PEOLPLE) REFERENCES PEOPLE (ID);
которые ссылаются на таблицу с пациентами.

Вопрос такой: можно ли спроектировать текущую схему таким образом, чтобы для текущего пациента я мог выбирать не только по определенному виду анализа, а показать суммарно все анализы, которые есть в базе для этого пациента?

Скрипт с базой прилагаю

Upd: положил рядом непожатый файл (у кого архив не открывается)
  • Вложение: test.zip
    (Размер: 1.14KB, Загружено 73 раза)
  • Вложение: test.sql
    (Размер: 4.46KB, Загружено 66 раз)


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Mon, 22 August 2022 15:59]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #354 является ответом на сообщение #353] Mon, 22 August 2022 15:45 Переход к предыдущему сообщениюПереход к следующему сообщению
pastor в настоящее время не в онлайне  pastor
Сообщений: 3
Зарегистрирован: June 2022
Географическое положение: Калуга
Junior Member
Скрипт пока не смотрел, может ночером.

По-бырому:

Сущности
- Человеки
- Тип анализа
- Обследования (Дата)
- Деталировка обследования (Тип анализа, значение результата, отметки (норм/ахтунг))

Связки
Человек->Обследование->Деталировка <-Тип анализа



Re: Нужна помощь с проектированием базы [сообщение #355 является ответом на сообщение #353] Mon, 22 August 2022 15:47 Переход к предыдущему сообщениюПереход к следующему сообщению
ggreggory в настоящее время не в онлайне  ggreggory
Сообщений: 26
Зарегистрирован: July 2022
Junior Member
imho, FK_PEOLPLE должно быть не smallint, а integer и быть not null, не бывает анализа без пациента.
Re: Нужна помощь с проектированием базы [сообщение #356 является ответом на сообщение #355] Mon, 22 August 2022 15:55 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
ggreggory писал(а) Mon, 22 August 2022 15:47
imho, FK_PEOLPLE должно быть не smallint, а integer и быть not null, не бывает анализа без пациента.
да-да, на скорую руку делал...


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik
Re: Нужна помощь с проектированием базы [сообщение #357 является ответом на сообщение #354] Mon, 22 August 2022 15:56 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
pastor писал(а) Mon, 22 August 2022 15:45
Скрипт пока не смотрел, может ночером.

По-бырому:

Сущности
- Человеки
- Тип анализа
- Обследования (Дата)
- Деталировка обследования (Тип анализа, значение результата, отметки (норм/ахтунг))

Связки
Человек->Обследование->Деталировка <-Тип анализа
хм, если можно, лучше скрЫптом. Че-та не соображу пока о_О


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik
Re: Нужна помощь с проектированием базы [сообщение #358 является ответом на сообщение #355] Mon, 22 August 2022 15:56 Переход к предыдущему сообщениюПереход к следующему сообщению
pastor в настоящее время не в онлайне  pastor
Сообщений: 3
Зарегистрирован: June 2022
Географическое положение: Калуга
Junior Member
ggreggory писал(а) Mon, 22 August 2022 15:47
imho, FK_PEOLPLE должно быть не smallint, а integer и быть not null, не бывает анализа без пациента.
bigint, однако
Re: Нужна помощь с проектированием базы [сообщение #359 является ответом на сообщение #357] Mon, 22 August 2022 16:10 Переход к предыдущему сообщениюПереход к следующему сообщению
pastor в настоящее время не в онлайне  pastor
Сообщений: 3
Зарегистрирован: June 2022
Географическое положение: Калуга
Junior Member
create domain PK bigint not null;
create domain D_NAME varchar(100) not null;
create domain D_EXAM_VALUE varchar(100) not null;
create domain D_EXAM_WARN integer;
create domain D_COMMENT varchar(200);
create domain D_DATE timestamp not null;


create table PEOPLES( -- Пациенты
ID PK,
LNAME D_NAME,
FNAME D_NAME,
MNAME D_NAME,
constraint PEOPLES
primary key(ID)
);


create table EXAM_TYPES( -- Типы анализов
ID PK,
NAME D_NAME,
LOW_RANGE D_EXAM_VALUE,
HIGH_RANGE D_EXAM_VALUE,
constraint EXAM_TYPES
primary key(ID)
);

create table SURVEYS( -- Обследования
ID PK,
ID_PEOPLE PK,
NUM D_NAME,
DATE_SUR D_DATE,
constraint SURVEYS
primary key(ID),
constraint SURVEYS_PEOPLE
foreign key (ID_PEOPLE)
references PEOPLES(ID)
);

create table SURVEYS_EXAMS( -- Результаты обследования
ID PK,
ID_SURVEYS PK,
ID_EXAM_TYPE PK,
EXAM_VALUE D_EXAM_VALUE,
constraint SURVEYS_EXAMS
primary key(ID),
constraint SURVEYS_EXAMS_SUR
foreign key (ID_SURVEYS)
references SURVEYS(ID),
constraint SURVEYS_EXAMS_TYPES
foreign key (ID_EXAM_TYPE)
references EXAM_TYPES(ID)
);

ЗЫ домен D_EXAM_WARN лучше сделать boolean.

[Обновления: Mon, 22 August 2022 16:11]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #360 является ответом на сообщение #359] Mon, 22 August 2022 16:31 Переход к предыдущему сообщениюПереход к следующему сообщению
sim_84 в настоящее время не в онлайне  sim_84
Сообщений: 84
Зарегистрирован: June 2022
Member
ИХМО. Анализ может быть без обследования (просто человек пришёл сдать анализ для другой клиники), но без пациента быть не может.
У всех анализов нужна дата-время, причем несколько. Дата взятия, дата-результата. Возможно ещё какие-нибудь отметки.
Обследование это далеко не только анализы, там ещё осмотры у специалистов, УЗИ, томография и много чего может быть, а может не быть
Re: Нужна помощь с проектированием базы [сообщение #362 является ответом на сообщение #360] Mon, 22 August 2022 22:51 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
sim_84 писал(а) Mon, 22 August 2022 16:31
ИХМО. Анализ может быть без обследования (просто человек пришёл сдать анализ для другой клиники), но без пациента быть не может.
У всех анализов нужна дата-время, причем несколько. Дата взятия, дата-результата. Возможно ещё какие-нибудь отметки.
Обследование это далеко не только анализы, там ещё осмотры у специалистов, УЗИ, томография и много чего может быть, а может не быть
все верно. Анализы (лабораторные) отдельно, исследования- отдельно. Причем и те, и другие - могут проводится абсолютно вне приема.

Сижу-думаю. Неужели на клиенте придется тупо перебирать все табли и результат в мемдатасет пихать? Sad

Update: интересно, наверное можно "достать" все нужные табли запросом через RDB$RELATION_FIELDS?


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Mon, 22 August 2022 23:37]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #363 является ответом на сообщение #362] Tue, 23 August 2022 00:28 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
Поэкпериментировал с системными таблицами
SELECT
      RF.RDB$RELATION_NAME AS "tbl_name"
      , R.RDB$DESCRIPTION AS "tbl_comment"
--      , RF.RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS RF JOIN RDB$RELATIONS R
  on R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
WHERE RDB$FIELD_NAME = 'FK_PEOPLE'
В принципе можно на клиента вытащить и другие поля (напр, дату взятия/изготовления), а детали конкретной записи показывать по доп. запросу. Пойду дальше мануалы курить и думать-думать о_О


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik
Re: Нужна помощь с проектированием базы [сообщение #364 является ответом на сообщение #363] Tue, 23 August 2022 09:26 Переход к предыдущему сообщениюПереход к следующему сообщению
sim_84 в настоящее время не в онлайне  sim_84
Сообщений: 84
Зарегистрирован: June 2022
Member
Я не совсем понимаю в чём сложность вопроса. Все анализы любых типов это одна таблица. Атрибуты - другая. Вытащить всё это по пациенту не вижу сложностей
Re: Нужна помощь с проектированием базы [сообщение #365 является ответом на сообщение #364] Tue, 23 August 2022 09:50 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
sim_84 писал(а) Tue, 23 August 2022 09:26
Я не совсем понимаю в чём сложность вопроса. Все анализы любых типов это одна таблица. Атрибуты - другая. Вытащить всё это по пациенту не вижу сложностей
я туплю, сам себе удивляюсь. Даже пытаюсь нарисовать схему на бумаге - не выходит (перетаскал за последние 2 мес 25 тонн дробленого бетона - может, поэтому?). Confused

Не совсем понимаю, что подразумевается под словом "аттрибуты". И почему все анализы должны быть в одной табле? в каждом виде анализов великое множество полей. Например:
CREATE TABLE TBL_LS_BIOCH_CLINIC (
    ID                    INTEGER NOT NULL,
    ISSUE_DATE            DMN_DATETIME,
    CGA                   DMN_STRING_100,
    A1A                   DMN_STRING_100,
    A1AT                  DMN_STRING_100,
    A1AT_NOTE             DMN_BLOBTXT,
    <skiped> 
);

COMMENT ON COLUMN TBL_LS_BIOCH_CLINIC.CGA IS
'Хромогранин А - универсальный маркер нейроэндокринной ткани и различных нейроэндокринных опухолей, чувств 10-100% (< 3 нмоль/л)';

COMMENT ON COLUMN TBL_LS_BIOCH_CLINIC.A1A IS
'A1A - фенотип при ХОБЛ (1 - тяжелая альфа-1-антитрипсиновая недостаточность – PiZZ, Null; 2 - предрасположенность – PiMZ, PiSZ; 3 - не влияют на прогноз - PiMM, PiMS)';

COMMENT ON COLUMN TBL_LS_BIOCH_CLINIC.A1AT IS
'дефицит А1АТ коррелирует с высоким риском развития патологии легких (900-2000 мг/л (медиана около 1300 мг/л) для фенотипа PiMM) ';

COMMENT ON COLUMN TBL_LS_BIOCH_CLINIC.A1AT_NOTE IS
'комментарий к анализу  альфа-1-антитрипсин';
В каждой табле от полусотни до сотни полей. И таких таблей более 30 шт.


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik
Re: Нужна помощь с проектированием базы [сообщение #366 является ответом на сообщение #365] Tue, 23 August 2022 10:11 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Если анализы так разнообразны то в каком интересно виде должно выглядеть
> "суммарно все анализы, которые есть в базе для этого пациента"?
Я как-то себе это слабо представляю...

Зато нормально представляется такое

- Выбираем пациента из списка
- Получаем список его анализов БЕЗ ДЕТАЛЕЙ, только общие поля, а именно
- - Дата (даты)
- - Вид анализа (из которого проистекает в какой таблице хранятся показатели по этому анализу)

Ну и уже далее, когда выбрали нужный анализ - открываем отдельным запросом результаты этого анализа, которые зависят от...
И, соответственно, на клиенте под каждый тип анализа (под каждую таблицу с деталями) делаем отдельные запросы на получение и форматирование результатов именно этой таблицы.

Еще бы знать - эта структура уже существует и наполнена данными, или пока только схема БД проектируется, а данных нет?

[Обновления: Tue, 23 August 2022 10:30]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #367 является ответом на сообщение #366] Tue, 23 August 2022 10:38 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
fraks писал(а) Tue, 23 August 2022 10:11
Если анализы так разнообразны то в каком интересно виде должно выглядеть
> "суммарно все анализы, которые есть в базе для этого пациента"?
Я как-то себе это слабо представляю...
Все просто: списочек в виде таблички/плоского дерева с двумя колонками - дата анализа и название анализа

fraks писал(а) Tue, 23 August 2022 10:11
Еще бы знать - эта структура уже существует и наполнена данными, или пока только схема БД проектируется, а данных нет?
Пока проектируется. Так что простор для фантазии ничем не ограничен Smile

fraks писал(а) Tue, 23 August 2022 10:11
Зато нормально представляется такое

- Выбираем пациента из списка
- Получаем список его анализов БЕЗ ДЕТАЛЕЙ, только общие поля, а именно
- - Дата (даты)
- - Вид анализа (из которого проистекает в какой таблице хранятся показатели по этому анализу)
Я так в предыдущих постах так и предположил Smile

Если воткнуть между таблицами еще одного посредника примерно такой структуры
CREATE TABLE LAB_CASE (
    ID         INTEGER NOT NULL,
    FK_PEOPLE  INTEGER NOT NULL,
    FK_LAB1    INTEGER,
    FK_LAB2    INTEGER
);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_1 FOREIGN KEY (FK_PEOPLE) REFERENCES PEOPLE (ID);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_2 FOREIGN KEY (FK_LAB1) REFERENCES LAB_1 (ID);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_3 FOREIGN KEY (FK_LAB2) REFERENCES LAB_2 (ID);
то запрос типа
SELECT 
    P.FIO FIO
    , L2.FLD_DATE DATE_LAB2
    , L2.FLD_STR2
    , L1.FLD_DATE DATE_LAB1
    , L1.FLD_STR1
FROM (LAB_CASE LC
   JOIN PEOPLE P ON (LC.FK_PEOPLE = P.ID))
   LEFT JOIN LAB_1 L1 ON (LC.FK_LAB1 = L1.ID)
   LEFT JOIN LAB_2 L2 ON (LC.FK_LAB2 = L2.ID)
WHERE FIO CONTAINING 'иванов'
все равно вернет неудобоваримый набор с планом
PLAN JOIN (JOIN (JOIN (P NATURAL, LC INDEX (FK_LAB_CASE_1)), L1 INDEX (PK_LAB_1)), L2 INDEX (PK_LAB_2))
, который придется конвертировать на клиенте.

При текущей архитектуре базы я и подумал про системные таблицы. Вообщем, пока и идей других нет Sad


Пс. Интересно, а движок форума не предоставляет возможности форматированный в таблицу текст вставлять?
  • Вложение: scr_090.png
    (Размер: 7.43KB, Загружено 67 раз)


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Tue, 23 August 2022 10:40]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #368 является ответом на сообщение #365] Tue, 23 August 2022 10:38 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Док писал(а) Tue, 23 August 2022 13:50
И почему все анализы должны быть в одной табле? в каждом виде анализов великое множество полей. Например:
Если хочется одним запросом получать все анализы - то они должны быть в одной табле.
Фокус с заранее неизвестным количеством таблиц нормально не прокатит.
Можно только с клиента тащить из каждой таблицы отдельно.

Собрать на клиенте execute block по мотивам системных таблиц рано или поздно упрется в ограничение количества контекстов в одном запросе (грубо говоря, количества упоминаний таблиц). Или на размер самого запроса.
Путь чреват тупиком.
Re: Нужна помощь с проектированием базы [сообщение #369 является ответом на сообщение #368] Tue, 23 August 2022 10:46 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
fraks писал(а) Tue, 23 August 2022 10:38
Если хочется одним запросом получать все анализы - то они должны быть в одной табле.
Фокус с заранее неизвестным количеством таблиц нормально не прокатит.
Можно только с клиента тащить из каждой таблицы отдельно.

Собрать на клиенте execute block по мотивам системных таблиц рано или поздно упрется в ограничение количества контекстов в одном запросе (грубо говоря, количества упоминаний таблиц). Или на размер самого запроса.
Путь чреват тупиком.
Ага, подобную засаду я подозревал :-/

Что ж, наверное придется на клиенте в цикле тупо перебирать таблицы с анализами и либо "собирать" результаты в мемдатасет, либо делать по типу мастер-деталь, как ты/я и предложили выше Smile


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Tue, 23 August 2022 11:02]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #374 является ответом на сообщение #369] Wed, 24 August 2022 00:31 Переход к предыдущему сообщениюПереход к следующему сообщению
S.PR в настоящее время не в онлайне  S.PR
Сообщений: 3
Зарегистрирован: August 2022
Junior Member
Я бы скорее всего дерево строил с иерархией от первого обращения, ветки-параметры анализов, а пациент-справочник...
Re: Нужна помощь с проектированием базы [сообщение #379 является ответом на сообщение #367] Wed, 24 August 2022 04:28 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Док писал(а) Tue, 23 August 2022 14:38
fraks писал(а) Tue, 23 August 2022 10:11
Если анализы так разнообразны то в каком интересно виде должно выглядеть
> "суммарно все анализы, которые есть в базе для этого пациента"?
Я как-то себе это слабо представляю...
Все просто: списочек в виде таблички/плоского дерева с двумя колонками - дата анализа и название анализа
Хм...а тогда в чем проблема? Почему в базе нет именной такой таблички?

Док писал(а) Tue, 23 August 2022 14:38

fraks писал(а) Tue, 23 August 2022 10:11
Зато нормально представляется такое

- Выбираем пациента из списка
- Получаем список его анализов БЕЗ ДЕТАЛЕЙ, только общие поля, а именно
- - Дата (даты)
- - Вид анализа (из которого проистекает в какой таблице хранятся показатели по этому анализу)
Я так в предыдущих постах так и предположил Smile

Если воткнуть между таблицами еще одного посредника примерно такой структуры
CREATE TABLE LAB_CASE (
    ID         INTEGER NOT NULL,
    FK_PEOPLE  INTEGER NOT NULL,
    FK_LAB1    INTEGER,
    FK_LAB2    INTEGER
);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_1 FOREIGN KEY (FK_PEOPLE) REFERENCES PEOPLE (ID);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_2 FOREIGN KEY (FK_LAB1) REFERENCES LAB_1 (ID);
ALTER TABLE LAB_CASE ADD CONSTRAINT FK_LAB_CASE_3 FOREIGN KEY (FK_LAB2) REFERENCES LAB_2 (ID);
Посредник неполноценный. Почему в нем нету даты?

Док писал(а) Tue, 23 August 2022 14:38
то запрос типа
SELECT 
    P.FIO FIO
    , L2.FLD_DATE DATE_LAB2
    , L2.FLD_STR2
    , L1.FLD_DATE DATE_LAB1
    , L1.FLD_STR1
FROM (LAB_CASE LC
   JOIN PEOPLE P ON (LC.FK_PEOPLE = P.ID))
   LEFT JOIN LAB_1 L1 ON (LC.FK_LAB1 = L1.ID)
   LEFT JOIN LAB_2 L2 ON (LC.FK_LAB2 = L2.ID)
WHERE FIO CONTAINING 'иванов'
все равно вернет неудобоваримый набор с планом
PLAN JOIN (JOIN (JOIN (P NATURAL, LC INDEX (FK_LAB_CASE_1)), L1 INDEX (PK_LAB_1)), L2 INDEX (PK_LAB_2))
, который придется конвертировать на клиенте.

При текущей архитектуре базы я и подумал про системные таблицы. Вообщем, пока и идей других нет Sad
С этим запросом тоже непонятно что ты имел ввиду.
Ибо,
1 - ты клеишь лабы в ширину, по твоим словам этих лаб может быть штук 50, в итоге какая получится таблица? Она же необозримая!
2 - т.к. кол-во таблиц - не константа, то запрос придется переписывать при добавлении очередной лабы
3 - ограничение на количество контекстов никто не отменял

А по плану запроса - непонятно какие претензии к плану. Для этого запроса план нормальный, я бы сказал что даже идеальный.
Непонятно только зачем одновременно искать пациентов и сразу клеить к ним лабы. Пациентов с фамилией иванов наверняка будет много, нужно сначала выбрать пациента, получить его ID и уж потом лезть дальше.
Соответственно, раз есть containing - то в плане имеется P NATURAL, а все остальные таблицы джойнятся по первичному ключу, максимально эффективно.
Другой вопрос что сама структура таблиц для такого запроса неидеальна...
Re: Нужна помощь с проектированием базы [сообщение #380 является ответом на сообщение #379] Wed, 24 August 2022 06:56 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Добавляем справочник типов исследований и список исследований.
Получаем ту самую таблицу по которой видим все исследования Иванова, точнее видим перечень исследований, и их даты, но не содержание.

CREATE TABLE LAB_SPR (
    ID    INTEGER NOT NULL,
    NAME  VARCHAR(50)
);

ALTER TABLE LAB_SPR ADD CONSTRAINT PK_LAB_SPR PRIMARY KEY (ID);

COMMENT ON TABLE LAB_SPR IS 'Справочник вариантов исследований (анализов)';

/******************************************************************************/

CREATE GENERATOR GEN_LAB_LST_ID;

CREATE TABLE LAB_LST (
    ID          INTEGER NOT NULL,
    ID_LAB_SPR  INTEGER NOT NULL,
    ID_PEOPLE   INTEGER NOT NULL,
    DT          TIMESTAMP NOT NULL
);

ALTER TABLE LAB_LST ADD CONSTRAINT PK_LAB_LST    PRIMARY KEY (ID);
ALTER TABLE LAB_LST ADD CONSTRAINT FK_LAB_PEOPLE FOREIGN KEY (ID_PEOPLE ) REFERENCES PEOPLE  (ID);
ALTER TABLE LAB_LST ADD CONSTRAINT FK_LAB_SPR    FOREIGN KEY (ID_LAB_SPR) REFERENCES LAB_SPR (ID);


SET TERM ^ ;
CREATE OR ALTER TRIGGER LAB_LST_BI FOR LAB_LST
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_lab_lst_id,1);
end
^

SET TERM ; ^

COMMENT ON TABLE LAB_LST IS 'Список исследований, по всем типам';


INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (1, 1, 1, '22-AUG-2022 15:21:37');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (2, 1, 1, '16-AUG-2022 00:00:00');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (3, 1, 2, '22-AUG-2022 15:22:08');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (4, 1, 2, '3-AUG-2022 00:00:00');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (5, 1, 3, '22-AUG-2022 15:22:33');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (6, 2, 3, '22-AUG-2022 15:22:48');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (7, 2, 3, '17-AUG-2022 00:00:00');
INSERT INTO LAB_LST (ID, ID_LAB_SPR, ID_PEOPLE, DT) VALUES (8, 2, 1, '26-AUG-2022 00:00:00');
COMMIT;
И вот так вот получаем искомое - список исследований пациента:
select
  people.fio,
  lab_lst.dt,
  lab_spr.name,
  lab_lst.id

from lab_lst
  left join lab_spr on (lab_spr.id = lab_lst.id_lab_spr)
  left join people  on (people.id  = lab_lst.id_people )

where (id_people = 1) -- Иванов

order by
  lab_lst.dt
+--------+---------------------+-------+----+
| FIO    | DT                  | NAME  | ID |
+--------+---------------------+-------+----+
| Иванов | 16.08.2022 00:00:00 | LAB_1 | 2  |
| Иванов | 22.08.2022 15:21:37 | LAB_1 | 1  |
| Иванов | 26.08.2022 00:00:00 | LAB_2 | 8  |
+--------+---------------------+-------+----+
Ну а далее, соответственно, таблицы с исследованиями должны содержать вместо такого
CREATE TABLE LAB_1 (
    ID          INTEGER NOT NULL,
    FK_PEOPLE   SMALLINT,
    FLD_STR1    VARCHAR(10)
);
должны быть такими
CREATE TABLE LAB_1 (
    ID          INTEGER NOT NULL,
    ID_LAB_LST  INTEGER NOT NULL,
    FLD_STR1    VARCHAR(10)
);
ALTER TABLE LAB_1 ADD CONSTRAINT FK_LAB1_LST FOREIGN KEY (ID_LAB_LST ) REFERENCES LAB_LST (ID);
Но при этом придется для просмотра результатов исследований делать процедуру отдельно для каждой таблицы.
Re: Нужна помощь с проектированием базы [сообщение #381 является ответом на сообщение #380] Wed, 24 August 2022 07:20 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Можно пойти дальше

CREATE TABLE DAT_SPR (
    ID    INTEGER NOT NULL,
    NAME  VARCHAR(50), --  краткое название параметра
    MSG   VARCHAR(200) -- развернутое описание параметра и т.п.
);

ALTER TABLE DAT_SPR ADD CONSTRAINT PK_DAT_SPR PRIMARY KEY (ID);

COMMENT ON TABLE DAT_SPR IS 'Справочник параметров в исследованиях';
CREATE TABLE LAB_DAT (
    ID          INTEGER NOT NULL,
    ID_LAB_LST  INTEGER NOT NULL,
    ID_DAT_SPR  INTEGER NOT NULL,
    VAL         VARCHAR(10)
);

ALTER TABLE LAB_DAT ADD CONSTRAINT FK_LAB_DAT_LST FOREIGN KEY (ID_LAB_LST) REFERENCES LAB_LST (ID);
ALTER TABLE LAB_DAT ADD CONSTRAINT FK_LAB_DAT_SPR FOREIGN KEY (ID_DAT_SPR) REFERENCES DAT_SPR (ID);

COMMENT ON TABLE LAB_DAT IS 'Исследования - их содержание';
И при таким подходе результаты всех исследований хранятся в одной таблице и получаются одним запросоом, сколько бы параметров и исследований не было бы.
При вводе в работу новых исследований и параметров запросы не меняются.
Re: Нужна помощь с проектированием базы [сообщение #389 является ответом на сообщение #381] Fri, 26 August 2022 08:19 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
@fraks

Леш, спасибо за кучу идей. Пытаюсь переварить (че-та моск пока не заводится, искра куда-то пропала Rolling Eyes )

Собрал воедино предложенное тобой, вот, что получилось (в виде графов)

https://i.imgur.com/l3cPvv2.png

Хотел уточнить: LAB_DAT.VAL - это что?


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik
Re: Нужна помощь с проектированием базы [сообщение #390 является ответом на сообщение #389] Fri, 26 August 2022 09:58 Переход к предыдущему сообщениюПереход к следующему сообщению
fraks в настоящее время не в онлайне  fraks
Сообщений: 59
Зарегистрирован: June 2022
Географическое положение: Новосибирск
Member
Док писал(а) Fri, 26 August 2022 12:19
@fraks
Леш, спасибо за кучу идей.
Я вообще-то не Леша, а совсем даже Вова Smile

Док писал(а) Fri, 26 August 2022 12:19

Собрал воедино предложенное тобой, вот, что получилось (в виде графов)
Хотел уточнить: LAB_DAT.VAL - это что?

Да... донести идею я так и не смог.
Не владею мед. терминами, попробую на пальцах.

-- Таблицы -----------------------------
PEOPLE - справочник пациентов
LAB_SPR - справочник какого типа бывают анализы (ОАК, ОАМ, БиохимияКрови и т.п.)
DAT_SPR - справочник какого типа бывают параметры в анализах (Билирубин общий, билирубин прямой, кол-во лейкоцитов и т.д.)

LAB_LST - шапки анализов (листик, на котором: в такую-то дату, пациент Иванов, сдал анализы на БиохимиюКрови)
LAB_DAT - тела анализов (строки на листике: в сданных анализах LAB_LST.ID параметр DAT_SPR.ID имеет значение LAB_DAT.VAL)
----------------------------------------

Соответственно, даты имеют смысл только в таблице LAB_LST
Таблицы LAB_1 и т.п. - не нужны. Эти метаданные записываются в виде записей в таблицах LAB_SPR и DAT_SPR.
Появляются новые исследования - добавляем записи в справочник анализов LAB_SPR и в справочник параметров анализов DAT_SPR.
При этом не меняются ни количество таблиц ни запросы.

Единственное неудобство - если у параметров значения измерений не укладываются в какой-либо тип, то придется либо привести его к строке (то самый LAB_DAT.VAL) либо сделать несколько полей под разные типы, например LAB_DAT.VAL_INT INTEGER, LAB_DAT.VAL_NUM NUMERIC(X, Y) и т.п.
При этом так же можно приводить каждое из значений к строке, что бы простой запрос выдавал это в виде текста сразу, из VAL, для любых типов параметров, а поля конкретных типов использовать только когда нужно сравнить значения на больше/меньше/равно.

На схеме еще не хватает поля и связи, что бы было удобно смотреть в каких анализах какие параметры воззможны, и по каким проведено исследование а по каким не делали. (Например в биохимии, есть параметр билирубин непрямой, можно сделать а можно и не делать. И т.п.)
DAT_SPR.ID_LAB_SPR -> LAB_SPR.ID

[Обновления: Fri, 26 August 2022 10:00]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #391 является ответом на сообщение #390] Fri, 26 August 2022 10:41 Переход к предыдущему сообщениюПереход к следующему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
Прости, Володя. Торможу на каждом шагу Rolling Eyes

Насчет доходчивости объяснений - это целиком моя вина. Огромное спасибо за терпение. Все, что тобой написано, я внимательно читаю и пытаюсь осмыслить - пока не получается.

Щас, чуть воспряну духом и возьмусь. Поглядывай в топик, плз. Ты один остался Very Happy

Update: все, кажись въехал о_О

Щас причешу табли, наполню данными, выложу сюда графы связей и результаты для ревизии. Спасибо!


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Fri, 26 August 2022 11:39]

Известить модератора

Re: Нужна помощь с проектированием базы [сообщение #392 является ответом на сообщение #391] Fri, 26 August 2022 15:08 Переход к предыдущему сообщению
Док в настоящее время не в онлайне  Док
Сообщений: 59
Зарегистрирован: June 2022
Member
Володя, огромное спасибо за помощь! Принцип понял. Сделал так

https://i.imgur.com/9qfDLsB.png

на скорую руку автоматически сформировал запрос в билдере
SELECT 
    LS.NAME_LAB,
    LL.SEARCH_DATE,
    P.FIO,
    LD.VAL,
    TS.NAME,
    DS.DESCRIPT,
    DS.NAME
FROM PEOPLE P
   INNER JOIN LAB_LST LL ON (P.ID = LL.FK_PEOPLE)
   INNER JOIN LAB_SPR LS ON (LL.FK_LAB_SPR = LS.ID)
   INNER JOIN LAB_DAT LD ON (LL.ID = LD.FK_LAB_LST)
   INNER JOIN DAT_SPR DS ON (LD.FK_DAT_SPR = DS.ID)
   INNER JOIN TISSUE_SPR TS ON (DS.FK_TISSUE = TS.ID)

--------------------------------------------------------------------------------
PLAN JOIN (LL NATURAL, LD INDEX (FK_LAB_DAT_1), P INDEX (PK_PEOPLE), LS INDEX (PK_LAB_SPR), DS INDEX (PK_DAT_SPR), TS INDEX (PK_TISSUE_SPR))
Получил желаемое

https://i.imgur.com/tqTDqwK.png

Самый большой затык - неинформативные названия таблей Smile Пошел править основную базу. Скрипт приложил
  • Вложение: test.sql
    (Размер: 8.13KB, Загружено 55 раз)


FPC/Lazarus (trunk) | Win10 x64 Ultim/Debian 11 amd64/Darwin x86_64 Monterey | Firebird 3.0.10 x64 | IBX by Rik

[Обновления: Fri, 26 August 2022 15:12]

Известить модератора

Предыдущая тема: Изменения оптимизатора?
Следующая тема: UDR engine Java
Переход к форуму:
  


Текущее время: Sun Dec 04 02:34:22 MSK 2022

Общее время, затраченное на создание страницы: 0.01331 секунд