Работа с объектами в ракурсе времени

[Sharfik]

Поделитесь секретной информацией, как проектируют БД когда объект ее то удаляется, то добавляется. Допустим должность "Главный начальник". Вот есть она, а потом нет ее, а потом снова есть.. и т.д. И разумеется пользователь захочет удалить ее, когда она не нужна будет. Но это недопустимо в ракурсе целостности данных. Т.е. если хотим увидеть срез прошлого и настоящего, то нужно и то что существовало ранее, и то что есть сейчас. Не знаю как правильно это называется, может статьи на хабре по этой теме были.

[grot]

Сама БД и информация из таблиц никогда не удаляется физически.
В таблици добавь поле вроде isDeleted со значениями 0/1, false/true и т.д.

Надеюсь, мельче "разжевывать" не надо ?

[Sharfik]

Логично, но засада в другом. Вот удалили мы эту единицу, потом добавили, потом удалили. Берем выборку за три года и у нас не одна строка, а три. Ну допустим, что это все можно на уровне верхнем причесать. Но если модератор работает и меняет состав, то он может добавлять/удалять за день несколько раз ставки. И получается что за сутки может оказаться десяток позиций. И в случае классической БД проверить отсутствие зависимостей в соседних таблицах не выйдет, если только с привязкой к известной структуре. Поэтому и спросил, может есть какое то упущение, и такие вещи можно как то проще организовывать. Если на объект другие таблицы завязаны и чтобы не разводить мусор.

[xchgeaxeax]

Так при удалении у вас не добавляется новая строка, а лишь изменяется isDeleted на true, а в выборке вы просто добавляете еще одно условие AND (NOT isDeleted). Таким образом у вас либо будет запись, либо её не будет. И соответственно дальше по связям.

[sts]

нужно делать версионирование данных.
посторонних примеров не встречал, изобретал велосипед разной сложности под задачу, например, получение объекта на дату, т.е. изменение объекта за сутки неважно, хотя сохраняется, или строгое версионирование, типа номер ревизии как в системах версионирования.
основная проблема передать в запрос какой версии ты хочешь видеть данные, т.е. "чистые" запросы "загрязняются" работой с версиями.
еще проблема, искусственный ключ - version_id - идей плюсом к прикладному первичному ключу key_id, все внешние ключи заменяются f1_key_id на f1_version_id, т.е. в момент записи берутся текущие версии объектов на которые есть ссылки, например добавляется сотрудник с такойто должностью, берется текущий version_id должности, вроде все нормально, а потом, допустим возникает необходимость подредактировать реквизиты должности, опечатку исправить, а это уже другая версия, т.е. надо както в интерфейсе обеспечить выбор между просто редактированием и применением новой версии ко всем объектам ссылающимся на нее и созданием действительно новой версии.

[xchgeaxeax]

нужно делать версионирование данных.

Даже звучит жутко...

Достаточно снабдить запись двумя полями. dateWriten и isDeleted. Каждое записывает дату, а isDeleted еще может принимать значение NULL. Тогда для выбора последнего состояния можно выбирать все по условию, но с AND isDeleted = NULL. А для выбора за определенную дату достаточно выбирать по условию с dateWriten <= selectedDate AND (isDeleted = NULL OR isDeleted > selectedDate).

Для более простого случая достаточно isDeleted типа boolean и значением по умолчанию FALSE

Тогда у каждой записи будет свой уникальный key_id и все ссылки на неё будут работать с уникальными записями. При изменении просто ссылаемся на этот key_id еще раз, но каждую запись снабжаем своими копиями полей dateWriten и isDeleted.

[sts]

Достаточно

это самый примитивный уровень, не всегда достаточный для обеспечения целостности данных

Добавлено спустя 1 минуту 10 секунд:
OR не рекомендуется использовать, запрос тяжелеет

[xchgeaxeax]

это самый примитивный уровень, не всегда достаточный для обеспечения целостности данных

Ну, например, когда это вам не даст сохранить целостность при выполнении условия последовательной записи?

[sts]

Ну, например, когда это вам не даст сохранить целостность при выполнении условия последовательной записи?

целостность обеспечивается механизмами бд, внешние ключи и индексы

Добавлено спустя 2 минуты 9 секунд:
по хорошему нужно сделать так чтобы нельзя было сослаться на версию внешнего объекта который не существует для версии текущего объекта

[Sharfik]

Так при удалении у вас не добавляется новая строка, а лишь изменяется isDeleted на true, а в выборке вы просто добавляете еще одно условие AND (NOT isDeleted). Таким образом у вас либо будет запись, либо её не будет. И соответственно дальше по связям.

нужно делать версионирование данных.

Не годный вариант, из-за возможности порчи в настоящем информации которая связана с прошлым. sts прав с использованием термина версионности.
Пример:
в январе 20 года сотрудник "ведущий инженер" делает работу. Потом эту должность из подразделение убирают. Потом через век снова добавляют и ставят на нее Васю. Потом Васю повышают с "Ведущего", до "СуперВедушего". И тут тонкая грань, либо с дуру переименуют объект "ведущий инженер" на "суперведущий инженер", либо перекинут юзера из одного объекта в другой. Если переименуют, тогда ж...а. При просмотре работ выполненных в прошлом будет светится что их делал СуперВедущий, а не Ведущий.
**Пока писал подумал о "таймаутах". Фиксированном времени, в течение которого можно полностью менять данные, например сутки. А по истечении - создавай дядя новый объект бд.

нужно делать версионирование данных.

спасибо, а то уперся мыслями в одну структуру и другой взгляд то что нужно.


***

[xchgeaxeax]

И тут тонкая грань, либо с дуру переименуют объект "ведущий инженер" на "суперведущий инженер", либо перекинут юзера из одного объекта в другой. Если переименуют, тогда ж...а. При просмотре работ выполненных в прошлом будет светится что их делал СуперВедущий, а не Ведущий.

Переименуют это уже UPDATE, а в вас ограничил только INSERT'ом

[sts]

в январе 20 года сотрудник "ведущий инженер" делает работу.

а пример, на примере должности это просто пример или решается задача учета кадров?, просто в кадрах еще в бумажные времена решили проблему версионирования, т.е. можно просто реализовать как положено

[Sharfik]

а пример, на примере должности это просто пример или решается задача учета кадров?, просто в кадрах еще в бумажные времена решили проблему версионирования, т.е. можно просто реализовать как положено

Нет, не кадров. У меня проблема с планированием работ по ресурсам исполнительным в подразделении на работе. Ждать пока руководство серьезно займется этим без толку. Все системы планирования, что можно купить, ориентированы на отдельные проекты и не позволяют загрузку исполнительных ресурсов организовать. Вот и экспериментирую. На базе органайзера своего сделал решение, но есть косяки.

[MaratIsk]

ракурс времени - это не клизма времени? или мозга?! может для начала лучше терминологию реляционных баз освоить?!

[sts]

Даже звучит жутко...

исходная диспозиция

Код: Выделить всё

---------------------------------
pers_t
  pers_id pers_fio
    1       иванова
    2       сидоров   
---------------------------------   
doc_t   
  doc_id write_date
    1      01.01.2024
---------------------------------   
doc_sp_t   
  pos_id doc_id pers_id
    1      1      1
    2      1      2
---------------------------------

устраивается петров, иванова меняет фамилию на петрова, документ 1 должен сохранить ссылку на иванову, петрова же для новых документов

Код: Выделить всё

---------------------------------
pers_t
  pers_id pers_fio
    1       петрова
    2       сидоров
    3       петров
---------------------------------
doc_t
  doc_id write_date
    1      01.01.2024
    2      01.01.2025
---------------------------------
doc_sp_t
  pos_id doc_id pers_id
    1      1      1
    2      1      2
    3      2      1
    4      2      2
    5      2      3
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, sp.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.doc_id = d.doc_id INNER JOIN pers_t p ON p.pers_id = sp.pers_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       петрова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------


Код: Выделить всё

/*
DROP TABLE doc_sp_t
/
DROP TABLE doc_t
/
DROP TABLE pers_t
/
*/

CREATE TABLE pers_t (
  pers_id NUMBER PRIMARY KEY,
  pers_fio VARCHAR2(1000) NOT NULL
)     
/

CREATE TABLE doc_t (
  doc_id NUMBER PRIMARY KEY,
  write_date DATE NOT NULL   
)
/     
     
CREATE TABLE doc_sp_t (
  pos_id  NUMBER PRIMARY KEY,
  doc_id  NUMBER NOT NULL REFERENCES doc_t (doc_id),
  pers_id NUMBER NOT NULL REFERENCES pers_t (pers_id)
)
/     
/*
DELETE FROM doc_sp_t
/
DELETE FROM doc_t
/
DELETE FROM pers_t
/
*/

INSERT INTO pers_t (pers_id, pers_fio)   
VALUES (1, 'иванова')
/

INSERT INTO doc_t (doc_id, write_date)   
VALUES (1, '01.01.2024')
/

INSERT INTO doc_sp_t (pos_id, doc_id, pers_id)   
VALUES (1, 1, 1)
/

INSERT INTO pers_t (pers_id, pers_fio)   
VALUES (2, 'сидоров')
/

INSERT INTO doc_sp_t (pos_id, doc_id, pers_id)   
VALUES (2, 1, 2)
/
---------------------------------
pers_t
  pers_id pers_fio
    1       иванова
    2       сидоров   
---------------------------------   
doc_t   
  doc_id write_date
    1      01.01.2024
---------------------------------   
doc_sp_t   
  pos_id doc_id pers_id
    1      1      1
    2      1      2
---------------------------------

INSERT INTO pers_t (pers_id, pers_fio)   
VALUES (3, 'петров')
/

INSERT INTO doc_t (doc_id, write_date)   
VALUES (2, '01.01.2025')
/

INSERT INTO doc_sp_t (pos_id, doc_id, pers_id)   
VALUES (3, 2, 1)
/

INSERT INTO doc_sp_t (pos_id, doc_id, pers_id)   
VALUES (4, 2, 2)
/

INSERT INTO doc_sp_t (pos_id, doc_id, pers_id)   
VALUES (5, 2, 3)
/

UPDATE pers_t SET pers_fio = 'петрова' WHERE pers_id = 1
/

---------------------------------
pers_t
  pers_id pers_fio
    1       петрова
    2       сидоров
    3       петров
---------------------------------
doc_t
  doc_id write_date
    1      01.01.2024
    2      01.01.2025
---------------------------------
doc_sp_t
  pos_id doc_id pers_id
    1      1      1
    2      1      2
    3      2      1
    4      2      2
    5      2      3
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, sp.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.doc_id = d.doc_id INNER JOIN pers_t p ON p.pers_id = sp.pers_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       петрова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------


Добавлено спустя 7 минут 10 секунд:
рассматриваем вариант с ревизиями (а не датами)
ревизия глобальный счетчик

идея в двух полях, начало существования записи (beg_id) и конец существования (end_id), содержащие ревизии.
при создании объекта вставляется запись где в beg_id новая ревизия, в end_id признак отсутствия ограничения существования,
при редактировании объекта вставляется запись аналогично созданию и у записи предыдущей версии заполняется end_id значением согласно beg_id новой,
при удалении объекта заполняется поле end_id последней версии, ограничивая существование.

Код: Выделить всё

pers_t
  vpers_id beg_id end_id pers_id pers_fio
    1       1       3(4 п4) 1       иванова
    2       2       ?      2       сидоров   
    3       3       ?      3       петров
    4       4       ?      1       петрова   


тут возникают неоднозначности, первая, каким должен быть признак отсутствия ограничения существования в end_id, это NULL (п1), это некая максимальная ревизия 999 (п2),
вторая, что конкретно помещать в end_id, ревизию до которой существует включительно (п3) и тогда можно использовать
rev_id BETWEEN beg_id AND end_id или ревизию в которой ее уже не существует (п4) и тогда условие будет rev_id >= beg_id AND rev_id < end_id

п1 - NULL плох всякими OR, NVL, COALESCE, типа rev_id BETWEEN beg_id AND COALESCE(end_id, rev_id)
п2 - 999 плох тем что достигнув его нужно добавить девятку и обновить все объекты отодвинув границу
п3 - плох тем что надо определять end_id для предыдущей версии, нельзя просто из новой ревизии вычесть один 4 - 1 = 3, 3 может не существовать
теряется связанность как в п4 где end_id предыдущей версии содержит ревизию следующей версии - 4
п4 - плох условием rev_id >= beg_id AND rev_id < COALESCE(end_id, rev_id +1) хотя нормальный движек бд должен оптимизировать константы

выбираем п1 и п4

Код: Выделить всё

rev_t
  rev_id
    1
    2
    3
    4
---------------------------------
pers_t
  vpers_id beg_id end_id pers_id pers_fio
    1       1       4      1       иванова
    2       2       ?      2       сидоров   
    3       3       ?      3       петров
    4       4       ?      1       петрова   
---------------------------------   
doc_t   
  vdoc_id beg_id end_id doc_id write_date
    1       1      ?      1      01.01.2024 
    2       3      ?      2      01.01.2025   
---------------------------------   
doc_sp_t   
  vpos_id beg_id end_id pos_id vdoc_id vpers_id
    1       1      ?      1      1       1
    2       2      ?      2      1       2
    3       3      4      3      2       1
    4       3      ?      4      2       2
    5       3      ?      5      2       3
    6       4      ?      3      2       4
---------------------------------   

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 4
  AND r.rev_id >= d.beg_id AND r.rev_id < COALESCE(d.end_id, r.rev_id +1)
  AND r.rev_id >= sp.beg_id AND r.rev_id < COALESCE(sp.end_id, r.rev_id +1)
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров


Код: Выделить всё

/*
DROP TABLE doc_sp_t
/
DROP TABLE doc_t
/
DROP TABLE pers_t
/
DROP TABLE rev_t
/
*/

CREATE TABLE rev_t (
  rev_id NUMBER PRIMARY KEY
)     
/

CREATE TABLE pers_t (
  vpers_id NUMBER PRIMARY KEY,
  beg_id  NUMBER NOT NULL REFERENCES rev_t (rev_id),
  end_id  NUMBER REFERENCES rev_t (rev_id),
  pers_id NUMBER NOT NULL,
  pers_fio VARCHAR2(1000) NOT NULL
)     
/
CREATE INDEX ix_pers_t ON pers_t (beg_id, end_id, pers_id)
/

CREATE TABLE doc_t (
  vdoc_id NUMBER PRIMARY KEY,
  beg_id  NUMBER NOT NULL REFERENCES rev_t (rev_id),
  end_id  NUMBER REFERENCES rev_t (rev_id),
  doc_id NUMBER NOT NULL,
  write_date DATE NOT NULL   
)
/     
CREATE INDEX ix_doc_t ON doc_t (beg_id, end_id, doc_id)
/
     
CREATE TABLE doc_sp_t (
  vpos_id  NUMBER PRIMARY KEY,
  beg_id  NUMBER NOT NULL REFERENCES rev_t (rev_id),
  end_id  NUMBER REFERENCES rev_t (rev_id),
  pos_id  NUMBER NOT NULL,
  vdoc_id  NUMBER NOT NULL REFERENCES doc_t (vdoc_id),
  vpers_id NUMBER NOT NULL REFERENCES pers_t (vpers_id)
)
/     
CREATE INDEX ix_doc_sp_t ON doc_sp_t (beg_id, end_id, pos_id)
/
/*
DELETE FROM doc_sp_t
/
DELETE FROM doc_t
/
DELETE FROM pers_t
/
DELETE FROM rev_t
/
*/

INSERT INTO rev_t (rev_id)
VALUES (1)
/

INSERT INTO pers_t (vpers_id, beg_id, end_id, pers_id, pers_fio)   
VALUES (1, 1, null, 1, 'иванова')
/

INSERT INTO doc_t (vdoc_id, beg_id, end_id, doc_id, write_date)   
VALUES (1, 1, null, 1, '01.01.2024')
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (1, 1, null, 1, 1, 1)
/

INSERT INTO rev_t (rev_id)
VALUES (2)
/

INSERT INTO pers_t (vpers_id, beg_id, end_id, pers_id, pers_fio)   
VALUES (2, 2, null, 2, 'сидоров')
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (2, 2, null, 2, 1, 2)
/

---------------------------------
rev_t
  rev_id
    1
    2
---------------------------------
pers_t
  vpers_id beg_id end_id pers_id pers_fio
    1       1       ?      1       иванова
    2       2       ?      2       сидоров
---------------------------------   
doc_t   
  vdoc_id beg_id end_id doc_id write_date
    1       1      ?      1      01.01.2024
---------------------------------   
doc_sp_t   
  vpos_id beg_id end_id pos_id vdoc_id vpers_id
    1       1      ?      1      1       1
    2       2      ?      2      1       2
---------------------------------

INSERT INTO rev_t (rev_id)
VALUES (3)
/

INSERT INTO pers_t (vpers_id, beg_id, end_id, pers_id, pers_fio)   
VALUES (3, 3, null, 3, 'петров')
/

INSERT INTO doc_t (vdoc_id, beg_id, end_id, doc_id, write_date)   
VALUES (2, 3, null, 2, '01.01.2025')
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (3, 3, null, 3, 2, 1)
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (4, 3, null, 4, 2, 2)
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (5, 3, null, 5, 2, 3)
/

INSERT INTO rev_t (rev_id)
VALUES (4)
/

INSERT INTO pers_t (vpers_id, beg_id, end_id, pers_id, pers_fio)   
VALUES (4, 4, null, 1, 'петрова')
/

UPDATE pers_t SET end_id = 4 WHERE vpers_id = 1
/

INSERT INTO doc_sp_t (vpos_id, beg_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (6, 4, null, 3, 2, 4)
/

UPDATE doc_sp_t SET end_id = 4 WHERE vpos_id = 3
/
---------------------------------
rev_t
  rev_id
    1
    2
    3
    4
---------------------------------
pers_t
  vpers_id beg_id end_id pers_id pers_fio
    1       1       4      1       иванова
    2       2       ?      2       сидоров   
    3       3       ?      3       петров
    4       4       ?      1       петрова   
---------------------------------   
doc_t   
  vdoc_id beg_id end_id doc_id write_date
    1       1      ?      1      01.01.2024 
    2       3      ?      2      01.01.2025   
---------------------------------   
doc_sp_t   
  vpos_id beg_id end_id pos_id vdoc_id vpers_id
    1       1      ?      1      1       1
    2       2      ?      2      1       2
    3       3      4      3      2       1
    4       3      ?      4      2       2
    5       3      ?      5      2       3
    6       4      ?      3      2       4
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       иванова
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 4
  AND r.rev_id >= d.beg_id AND r.rev_id < COALESCE(d.end_id, r.rev_id +1)
  AND r.rev_id >= sp.beg_id AND r.rev_id < COALESCE(sp.end_id, r.rev_id +1)
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 3
  AND r.rev_id >= d.beg_id AND r.rev_id < COALESCE(d.end_id, r.rev_id +1)
  AND r.rev_id >= sp.beg_id AND r.rev_id < COALESCE(sp.end_id, r.rev_id +1)
  AND d.doc_id = 2
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  2      01.01.2025   3      1       иванова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------



Добавлено спустя 7 минут 23 секунды:
можно объединить поля v*_id и beg_id и получить сквозное версионирование всех изменений внутри одной ревизии

Код: Выделить всё

/*
DROP TABLE doc_sp_t
/
DROP TABLE doc_t
/
DROP TABLE pers_t
/
DROP TABLE ver_t
/
DROP TABLE rev_t
/
*/

CREATE TABLE rev_t (
  rev_id NUMBER PRIMARY KEY,
  ver_id NUMBER
)     
/
CREATE INDEX ix_rev_t ON rev_t (rev_id, ver_id)
/

CREATE TABLE ver_t (
  ver_id NUMBER PRIMARY KEY,
  rev_id NUMBER REFERENCES rev_t (rev_id)
)     
/

CREATE TABLE pers_t (
  vpers_id NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER REFERENCES ver_t (ver_id),
  pers_id NUMBER NOT NULL,
  pers_fio VARCHAR2(1000) NOT NULL
)     
/
CREATE INDEX ix_pers_t ON pers_t (vpers_id, end_id, pers_id)
/

CREATE TABLE doc_t (
  vdoc_id NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER REFERENCES ver_t (ver_id),
  doc_id NUMBER NOT NULL,
  write_date DATE NOT NULL   
)
/     
CREATE INDEX ix_doc_t ON doc_t (vdoc_id, end_id, doc_id)
/
     
CREATE TABLE doc_sp_t (
  vpos_id  NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER REFERENCES ver_t (ver_id),
  pos_id  NUMBER NOT NULL,
  vdoc_id  NUMBER NOT NULL REFERENCES doc_t (vdoc_id),
  vpers_id NUMBER NOT NULL REFERENCES pers_t (vpers_id)
)
/     
CREATE INDEX ix_doc_sp_t ON doc_sp_t (vpos_id, end_id, pos_id)
/
/*
DELETE FROM doc_sp_t
/
DELETE FROM doc_t
/
DELETE FROM pers_t
/
DELETE FROM ver_t
/
DELETE FROM rev_t
/
*/

INSERT INTO rev_t (rev_id)
VALUES (1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (1, 1)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (1, null, 1, 'иванова')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (2, 1)
/

INSERT INTO doc_t (vdoc_id, end_id, doc_id, write_date)   
VALUES (2, null, 1, '01.01.2024')
/

INSERT INTO rev_t (rev_id)
VALUES (2)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (3, 2)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (3, null, 1, 2, 1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (4, 2)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (4, null, 2, 'сидоров')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (5, 2)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (5, null, 2, 2, 4)
/

---------------------------------
rev_t
  rev_id
    1
    2
---------------------------------
pers_t
  vpers_id end_id pers_id pers_fio
    1       ?      1       иванова
    4       ?      2       сидоров
---------------------------------   
doc_t   
  vdoc_id end_id doc_id write_date
    2       ?      1      01.01.2024
---------------------------------   
doc_sp_t   
  vpos_id end_id pos_id vdoc_id vpers_id
    3       ?      1      2       1
    5       ?      2      2       4
---------------------------------

INSERT INTO rev_t (rev_id)
VALUES (3)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (6, 3)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (6, null, 3, 'петров')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (7, 3)
/

INSERT INTO doc_t (vdoc_id, end_id, doc_id, write_date)   
VALUES (7, null, 2, '01.01.2025')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (8, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (8, null, 3, 7, 1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (9, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (9, null, 4, 7, 4)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (10, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (10, null, 5, 7, 6)
/

UPDATE rev_t SET ver_id = 10 WHERE rev_id = 3
/

INSERT INTO rev_t (rev_id)
VALUES (4)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (11, 4)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (11, null, 1, 'петрова')
/

UPDATE pers_t SET end_id = 11 WHERE vpers_id = 1
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (12, 4)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (12, null, 3, 7, 11)
/

UPDATE doc_sp_t SET end_id = 12 WHERE vpos_id = 8
/

UPDATE rev_t SET ver_id = 12 WHERE rev_id = 4
/

---------------------------------
rev_t
  rev_id
    1
    2
    3
    4
---------------------------------
pers_t
  vpers_id end_id pers_id pers_fio
    1        11     1       иванова
    4        ?      2       сидоров   
    6        ?      3       петров
    11       ?      1       петрова   
---------------------------------   
doc_t   
  vdoc_id end_id doc_id write_date
    2       ?      1      01.01.2024 
    7       ?      2      01.01.2025   
---------------------------------   
doc_sp_t   
  vpos_id end_id pos_id vdoc_id vpers_id
    3       ?      1      2       1
    5       ?      2      2       4
    8       12     3      7       1
    9       ?      4      7       4
    10      ?      5      7       6
    12      ?      3      7       11
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       иванова
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 4
  AND r.ver_id >= d.vdoc_id AND r.ver_id < COALESCE(d.end_id, r.ver_id +1)
  AND r.ver_id >= sp.vpos_id AND r.ver_id < COALESCE(sp.end_id, r.ver_id +1)
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 3
  AND r.ver_id >= d.vdoc_id AND r.ver_id < COALESCE(d.end_id, r.ver_id +1)
  AND r.ver_id >= sp.vpos_id AND r.ver_id < COALESCE(sp.end_id, r.ver_id +1) 
  AND d.doc_id = 2
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  2      01.01.2025   3      1       иванова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------




Добавлено спустя 3 минуты 8 секунд:
выбираем п2 и п3, поля v*_id и beg_id объединены

Код: Выделить всё

/*
DROP TABLE doc_sp_t
/
DROP TABLE doc_t
/
DROP TABLE pers_t
/
DROP TABLE ver_t
/
DROP TABLE rev_t
/
*/

CREATE TABLE rev_t (
  rev_id NUMBER PRIMARY KEY,
  ver_id NUMBER
)     
/
CREATE INDEX ix_rev_t ON rev_t (rev_id, ver_id)
/

CREATE TABLE ver_t (
  ver_id NUMBER PRIMARY KEY,
  rev_id NUMBER REFERENCES rev_t (rev_id)
)     
/

CREATE TABLE pers_t (
  vpers_id NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER NOT NULL REFERENCES ver_t (ver_id),
  pers_id NUMBER NOT NULL,
  pers_fio VARCHAR2(1000) NOT NULL
)     
/
CREATE INDEX ix_pers_t ON pers_t (vpers_id, end_id, pers_id)
/

CREATE TABLE doc_t (
  vdoc_id NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER NOT NULL REFERENCES ver_t (ver_id),
  doc_id NUMBER NOT NULL,
  write_date DATE NOT NULL   
)
/     
CREATE INDEX ix_doc_t ON doc_t (vdoc_id, end_id, doc_id)
/
     
CREATE TABLE doc_sp_t (
  vpos_id  NUMBER PRIMARY KEY REFERENCES ver_t (ver_id),
  end_id  NUMBER NOT NULL REFERENCES ver_t (ver_id),
  pos_id  NUMBER NOT NULL,
  vdoc_id  NUMBER NOT NULL REFERENCES doc_t (vdoc_id),
  vpers_id NUMBER NOT NULL REFERENCES pers_t (vpers_id)
)
/     
CREATE INDEX ix_doc_sp_t ON doc_sp_t (vpos_id, end_id, pos_id)
/
/*
DELETE FROM doc_sp_t
/
DELETE FROM doc_t
/
DELETE FROM pers_t
/
DELETE FROM ver_t
/
DELETE FROM rev_t
/
*/

INSERT INTO rev_t (rev_id)
VALUES (1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (999, 1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (1, 1)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (1, 999, 1, 'иванова')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (2, 1)
/

INSERT INTO doc_t (vdoc_id, end_id, doc_id, write_date)   
VALUES (2, 999, 1, '01.01.2024')
/

UPDATE rev_t SET ver_id = 2 WHERE rev_id = 1
/

INSERT INTO rev_t (rev_id)
VALUES (2)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (3, 2)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (3, 999, 1, 2, 1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (4, 2)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (4, 999, 2, 'сидоров')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (5, 2)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (5, 999, 2, 2, 4)
/

UPDATE rev_t SET ver_id = 5 WHERE rev_id = 2
/
---------------------------------
rev_t
  rev_id
    1
    2
---------------------------------
pers_t
  vpers_id end_id pers_id pers_fio
    1       999    1       иванова
    4       999    2       сидоров
---------------------------------   
doc_t   
  vdoc_id end_id doc_id write_date
    2       999    1      01.01.2024
---------------------------------   
doc_sp_t   
  vpos_id end_id pos_id vdoc_id vpers_id
    3       999    1      2       1
    5       999    2      2       4
---------------------------------

INSERT INTO rev_t (rev_id)
VALUES (3)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (6, 3)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (6, 999, 3, 'петров')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (7, 3)
/

INSERT INTO doc_t (vdoc_id, end_id, doc_id, write_date)   
VALUES (7, 999, 2, '01.01.2025')
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (8, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (8, 999, 3, 7, 1)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (9, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (9, 999, 4, 7, 4)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (10, 3)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (10, 999, 5, 7, 6)
/

UPDATE rev_t SET ver_id = 10 WHERE rev_id = 3
/

INSERT INTO rev_t (rev_id)
VALUES (4)
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (11, 4)
/

INSERT INTO pers_t (vpers_id, end_id, pers_id, pers_fio)   
VALUES (11, 999, 1, 'петрова')
/

UPDATE pers_t SET end_id = 10 WHERE vpers_id = 1
/

INSERT INTO ver_t (ver_id, rev_id)
VALUES (12, 4)
/

INSERT INTO doc_sp_t (vpos_id, end_id, pos_id, vdoc_id, vpers_id)   
VALUES (12, 999, 3, 7, 11)
/

UPDATE doc_sp_t SET end_id = 11 WHERE vpos_id = 8
/

UPDATE rev_t SET ver_id = 12 WHERE rev_id = 4
/

---------------------------------
rev_t
  rev_id
    1
    2
    3
    4
---------------------------------
pers_t
  vpers_id end_id pers_id pers_fio
    1        10     1       иванова
    4        999    2       сидоров   
    6        999    3       петров
    11       999    1       петрова   
---------------------------------   
doc_t   
  vdoc_id end_id doc_id write_date
    2       999    1      01.01.2024 
    7       999    2      01.01.2025   
---------------------------------   
doc_sp_t   
  vpos_id end_id pos_id vdoc_id vpers_id
    3       999    1      2       1
    5       999    2      2       4
    8       11     3      7       1
    9       999    4      7       4
    10      999    5      7       6
    12      999    3      7       11
   
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       иванова
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 4
  AND r.ver_id BETWEEN d.vdoc_id AND d.end_id
  AND r.ver_id BETWEEN sp.vpos_id AND sp.end_id
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  1      01.01.2024   1      1       иванова
  1      01.01.2024   2      2       сидоров
  2      01.01.2025   3      1       петрова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------

SELECT d.doc_id, d.write_date, sp.pos_id, p.pers_id, p.pers_fio FROM doc_t d INNER JOIN doc_sp_t sp ON sp.vdoc_id = d.vdoc_id INNER JOIN pers_t p ON p.vpers_id = sp.vpers_id, rev_t r
WHERE r.rev_id = 3
  AND r.ver_id BETWEEN d.vdoc_id AND d.end_id
  AND r.ver_id BETWEEN sp.vpos_id AND sp.end_id
  AND d.doc_id = 2
ORDER BY d.doc_id, sp.pos_id

doc_id write_date   pos_id pers_id pers_fio
  2      01.01.2025   3      1       иванова
  2      01.01.2025   4      2       сидоров
  2      01.01.2025   5      3       петров
---------------------------------


Добавлено спустя 20 минут 20 секунд:
скрипты оракл