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

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'

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-антитрипсин';

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

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

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 'Исследования - их содержание';

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