У нас есть база данных, созданная с помощью sqlalchemy с этими таблицами (среди прочего):
users alarms alarm_history
+---------+-----------+ +----------+------------+ +----------+-----------+------------+
| user_id | user_name | | alarm_id | alarm_name | | alarm_id | timestamp | alarm_data |
+---------+-----------+ +----------+------------+ +----------+-----------+------------+
Возможно, стоит отметить, что в этих таблицах есть много других данных, это сокращенная версия, а alarm_id связан с внешним ключом. Есть также много других таблиц, которые имеют отношения.
Таблица аварийных сигналов похожа на основной список аварийных сигналов, подлежащих мониторингу, таблица истории представляет собой просто список исторических изменений в данных аварийных сигналов, полученных от внешней службы мониторинга, а таблица пользователей не требует пояснений.
Что нам нужно сделать, так это иметь список тревог по умолчанию, на которые подписывается пользователь (из-за группы, к которой он принадлежит), но также иметь возможность выбирать, на какие тревоги подписаться или отказаться от подписки. Это должно быть управляемо на лету — они могут изменить это в любое время через API.
Как я должен хранить эти конфигурации в базе данных? Идея заключалась бы в том, чтобы иметь столбец «подписка» в пользовательской таблице, указывающий на конфигурацию, которую необходимо использовать для определения набора аварийных сигналов, которые пользователь должен отслеживать.
IN(<list>)
, но вы получаете ошибку памяти, когда список длиннее ~ 900 элементов, поэтому вам нужно пакетировать запрос. Кроме того, это не совсем реляционно, поэтому оно медленное.Любые указатели на то, как обойти эту проблему?
Другой вариант — просто выполнить запрос к таблице истории аварийных сигналов, отсортированной по дате (по убыванию) и запросив только уникальные идентификаторы аварийных сигналов, чтобы он возвращал по одному для каждого аварийного сигнала, а затем запускать дополнительный фильтр для возвращенного результата. Ни один из них не справляется с возможностью иметь в общей сложности 100 000 сигналов тревоги, когда пользователя A интересуют только 20 000 из них, а пользователя B — 30 000, которые не пересекаются.
Массивы редко используются в СУБД. Причина очевидна: система реляционной базы данных уже хранит отношения в таблицах, так зачем добавлять массивы, которые представляют только отношения 1:n, без преимуществ базы данных в виде согласованности внешнего ключа и быстрого поиска по индексу?
Вы говорите, что пользователь принадлежит к группе или даже группам (это не ясно). Так что я ожидаю таблицу групп. Если пользователь принадлежит к одной группе, вы добавите group_id
в таблицу пользователей. Если пользователь может принадлежать к нескольким группам, вместо этого вы добавите таблицу мостов user_group
.
Группа имеет будильники по умолчанию. Поскольку у одной группы может быть несколько будильников по умолчанию, а один будильник, вероятно, может быть по умолчанию для многих групп, у вас будет отношение m:n, что означает сводную таблицу group_alarm
.
Наконец, пользователь решает, на какие сигналы тревоги подписаться. Еще одно отношение m:n, еще одна таблица мостов: user_alarm
.
Пока что для структуры базы данных, которая достаточно проста.
Теперь к логике: вы хотите, чтобы будильники были подписаны по умолчанию. Это будет означать, что вы назначаете пользователю тревоги по умолчанию, когда назначаете пользователю группу. Это можно сделать с помощью триггера, который копирует будильники из group_alarm
в user_alarm
, или с помощью приложения. Триггер делает это автоматически и бесшумно. Приложение также может делать это автоматически или предлагать список флажков, поэтому подписка/отмена подписки может быть выполнена явно.
Когда будильник добавляется по умолчанию в группу, я полагаю, вы хотите, чтобы ничего не происходило. Я полагаю, это повлияет только на новые ассоциации пользователей/групп. Или вы хотите добавить этот будильник к пользовательским будильникам? Если это так, снова может быть уместным триггер.
Я не знаю SQLAlchemy. Это ORM, а ORM, как правило, пытаются преобразовать сеть отношений, предлагаемых СУБД, в древовидные структуры, что должно привести к компромиссам, включая менее удобный и более медленный доступ к данным. Внезапно вы вынуждены иметь дело с простым языком программирования для запроса данных, а не иметь доступ к языку запросов 4GL. Так что, хотя упомянутая выше структура и является подходящей базой данных, я не знаю, как работать с ней в SQLAlchemy.
Спасибо @Торстен. Вы правы, пользователь принадлежит к группе, и эта группа имеет набор сигналов тревоги по умолчанию. У меня уже есть таблица user_groups
(не показана), и пользователи уже связаны с группой через group_id
. Я просто не понимаю, как в таблице user_alarm
хранятся будильники, на которые подписывается пользователь. Если я неправильно понимаю термин «бридж-таблица»?
Итак, если я правильно понимаю, то, что на первый взгляд кажется неуправляемым дублированием данных, помещенных в другую таблицу (возможно, создание очень большой таблицы, которая может масштабироваться до n_alarms x m_alarm_subscription_lists
), на самом деле является разумным и элегантным решением, а также быстрым. Это явно решение, даже если оно кажется уродливым, оно определенно выполняет свою работу и позволяет выполнять сверхбыстрые запросы.
Коллега предложил добавить дополнительную таблицу
current_alarms
, в которой содержится самая актуальная информация для каждого будильника. По мере получения новых данных о сигналах тревоги он добавляет новую строку в таблицу сигналов тревогиalarm_history
, но затем переопределяет любые измененные данные в таблицеcurrent_alarms
. Затем можно запросить всю эту таблицу, а затем быстро обработать результат для удаления нежелательных данных (либо на стороне сервера, либо на стороне клиента). Затем будет таблицаconfigs
с идентификатором и строковым представлением тревог, которые отправляются пользователю при входе в систему.