Добрый день

Сам я не являюсь гуру в области архитектуры и проектирования БД, да и вообще маловато понимаю в этом, но поставили передо мной задачу и надо ее решить. Поэтому хочу спросить совета, так как чувствую, что изобретаю велосипед.

Стоит задача – хранение базы о связанных субъектах и группа, которые они образуют.

Для понимания приведу картинку.
Кружки – ссылки на субъекты, стрелки - связи между субъектами.

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

Связи появляются в базе не одновременно с субъектами. То есть возможна ситуация, когда в базе есть группа №1 из двух субъектов – A и B. Связь у них A -> B. C течением времени оператор заносит в программу связь вида B -> C. Значит в группе №1 должен появится третий член группы С. А поскольку у C тоже есть связи, значит в группу должны добавится D и E. А группа №2 (C-D-E), соответственно, должна исчезнуть, так как субъект может входить только в одну группу.

Кроме того, может возникнуть ситуация, когда какой-либо из субъектов «исчезает». Поскольку субъекты ведутся в другой системе, моя система может и не знать об этом. Соответственно надо предусмотреть какой-либо механизм для учета таких ситуаций. В принципе, доступ к таблицам есть, так что можно и подумать насчет триггеров, но я пока не раздумывал.


Что нужно:
1.По ID группы получить ее состав на определенную дату
2.По ID субъекта получить состав группы, в которую он входит. Опять же на определенную дату.

Что я нарисовал, по первому размышлению:
Логические объекты системы:
1.Субъект
2.Связь
3.Группа субъектов

Физические объекты системы:
1.Таблица «Связи»
2.Таблица «Группы субъектов»

Параметры объектов:
1.Субъект – ссылка (ID) на запись субъекта в другой системе.
2.Связь – запись в таблице «Связи» с реквизитами:
ID субъекта 1

ID субъекта 2

Тип связи

Дата установления связи

Дата закрытия связи

3.Группа субъектов – записи в таблице «Группа субъектов» с реквизитами:

ID группы

ID субъекта, входящего в группу

Дата, когда субъект вошел в группу

Дата, когда субъект вышел из группы

Для чего я завел таблицу «Группы субъектов»? Для того, чтобы быстрее получать информацию о составе групп. С другой стороны, встает задача поддержания целостности данной таблицы и непротиворечивости данных из таблицы «Группы субъектов» таблице «Связи».

Насколько я понимаю, для поддержания целостности, достаточно отслеживать изменения связей по следующим случаям:

появления связи

удаления связи

изменение даты закрытия связи

И изменения субъектов по следующим случаям

«исчезновение» субъекта

Проблема в том, что помимо того, что даты меняются вручную, может быть ситуация, когда дата закрытия связи была проставлена оператором при заведении связи сразу. Причем дата эта в будущем. То есть 1-го января добавлена связь и указано, что 1-го февраля, она закрывается. Значит 1-го февраля, соответствующая группа должна не содержать того субъекта, на которого указывала данная связь.

Хочу спросить – имеет ли смысл держать такую таблицу как «Группы субъектов» и писать сложную логику отслеживания целостности или накладные расходы на поддержание такой целостности съедят все выгоды? Субъектов могут быть десятки тысяч, и, следовательно, иные группы могут включать в себя сотни субъектов.

И как вообще решаются подобные задачи, наверняка кто-то сталкивался?

Bely2) У вас в логике есть один непонятный момент.
до 1 янв - было две группы: 1 и 2. После 1 янв - появилась связь между элементами этих групп, соответственно - группа схлопнулась.
Какой номер она будет иметь теперь? 1 или 2?
А если 1 фев связь пропала - какие номера будут у групп?

Если вы захотите посмотреть историю не по субъектам, а по группам - то у вас возникнут неоднозначности.

Вы абсолютно правы, тут я не продумал.

Bely3) Время можно разбить на интервалы внутри которых никаких изменений в связях не было.
т.е. если связи менялись 1 янв, 13 янв и 1 фев, то давая запрос "что было 7 января" мы будем точно знать - смотреть надо данные на 1 января.
Соответственно к элементу группы можно прицепить набор интервалов времени, когда он присутствует в группе.
На больших объемах информации - сравнение на присутствие в интервале может оказаться быстрее, чем сравнение даты с полями дат в таблице.

Не уловил разницы с тем, что я описал. У меня же и были интервалы, или я что-то не понимаю?
Можно пример со столбцами таблицы?