Akina

Когда каскадное обновление это плохо?

Каскадное обновление - в большинстве случаев это... глупо.

Вспомним, что это вообще такое.

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

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

Что же есть каскадное обновление? Это изменение связанного значения в подчинённой таблице, если изменяется значение основной таблицы. Ну то есть если изменяется (вспоминаем сказанное выше) значение первичного ключа или поля, объявленного уникальным. В основной таблице. Ага...

Ну то, что изменение/корректировка значения поля первичного ключа есть bad practice (читай - дурь голимая), хорошо известно, обосновано и весьма логично. Нет, реально возможны ситуации, когда такая операция оправдана и имеет смысл - но такая ситуация абсолютно всегда одноразовая, и есть составная часть административного обслуживания. А если подобная надобность возникла на уровне пользователя, в рабочем процессе - то это гарантия наличия серьёзной ошибки в проектировании БД.

Практически всё то же относится и к корректировке просто уникального поля. За исключением случая, когда выполняется каскадное изменение значения поля, которое в основной таблице получило значение NULL. То есть когда выполняемая операция по смыслу является не обновлением, а "мягким удалением" основной записи с каскадным удалением всех подчинённых. Правда, на вопрос, как отличить мягко каскадно-удалённые подчинённые записи от мягко явно-удалённых, и как определить, с какой основной записью была связана мягко удалённая подчинённая, не залезая в журнал или бэкап, ответа никто не даст. А получается, что даже в случае исключения всё делается через "универсальный интерфейс", то есть косяк в проектировании структуры имеется и в этом случае.

Резюмирую. Если каскадное обновление необходимо, оно скорее всего маскирует недостатки и ошибки проектирования. А плохо это или хорошо - прикрывать дырку костылём,- решайте сами.

В рамках реляционной СУБД для описанной схемы есть несколько принципиально разных подходов.

Первый - одна таблица и NULL в полях, отсутствующих у конкретного типа, её описывает Сергей Соловьев, вариант 2.
Второй - использование EAV. Удобно, динамично, но проблемы с производительностью. Хотя из всех паттернов для реляционных СУБД он походу наиболее применим для фасетного поиска.
Третий - использование сериализованного формата хранения. Например, хранение свойств объекта в формате JSON. Но, поскольку требуется поиск по атрибутам, в этом случае необходимо будет использовать внешний поисковый движок, возможно, даже ориентированный на фасетный поиск, или будет ужас как медленно.

Использование MongoDB на описанном материале (буквально пара типов объектов) мне кажется не очень соответствующим решением. Хотя зависит от планируемого объёма данных.

SELECT table1.*, 
       EXISTS ( SELECT NULL
                FROM table2
                WHERE table2.table1_id = table1.id
                   AND table2.date = @needed_date
                ) AS row_exists
FROM table1

Если точная дата внутри месяца неизвестна, то

WHERE table2.date BETWEEN @needed_month_1st_day AND @needed_month_last_day

Ну вариант первый - это просто хранить данные за день, а всякие укрупнения считать в момент необходимости.

Вариант соответственно второй - считать все укрупнёнки в момент записи текущих данных. Удалять/чистить лучше отдельной эвент-процедурой, если старая укрупнёнка недельку полежит, никто не помрёт. Вопрос отдельных таблиц под разные укрупнёнки - только если их структура различна в зависимости от периода, иначе одна таблица и поле маркер - диапазон периода записи.

При добавлении набора данных в связанные таблицы (а по большому счёту - вообще всегда) следует забыть о существовании INSERT .. VALUES и пользоваться исключительно INSERT .. SELECT. Первый запрос вставляет запись в основную таблицу, второй вставляет в зависимую, получая необходимое для связывания значение из основной по известным критериям отбора (по только что вставленным значениям, либо, в случае MySQL, из LAST_INSERT_ID).

Пример реализации "влоб" (синтаксис MySQL).

Структура:

CREATE TABLE test ( word0 VARCHAR(255),
                    word1 VARCHAR(255),
                    word2 VARCHAR(255),
                    word3 VARCHAR(255)
);

Запрос:

WITH 
cte1 AS (
    SELECT *, ROW_NUMBER() OVER () identity
    FROM test
),
cte2 AS (
    SELECT word0 word, identity FROM cte1 UNION ALL
    SELECT word1 word, identity FROM cte1 UNION ALL
    SELECT word2 word, identity FROM cte1 UNION ALL
    SELECT word3 word, identity FROM cte1 
)
SELECT LEAST(t1.word, t2.word), GREATEST(t1.word, t2.word), COUNT(DISTINCT identity)
FROM cte2 t1
JOIN cte2 t2 USING( identity )
WHERE t1.word > t2.word
GROUP BY 1, 2;

DEMO fiddle

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

Для того, чтобы получить хоть сколько-нибудь вменяемое время обработки, надо, во-первых, выполнить нормализацию (преобразование, которое выполняют оба CTE) в статическую проиндексированную таблицу, во-вторых, получать данные не для всего массива сразу, а для достаточно узкого набора слов.

По-моему, ты накрутил сверх меры. Всё решается куда проще.

Структура таблицы, максимально упрощённая:

CREATE TABLE tasks (
    id PRIMARY KEY,
    definition,
    performer_id REFERENCES performer (id)
    expired_at DATETIME
);

Взятие (параметры - id обработчика и id задачи):

UPDATE tasks
SET performer_id = @performer_idб
    expired_at  = NOW() + INTERVAL 'performing time'
WHERE ( expired_at IS NULL or expired_at < NOW() )
  AND ( id = @task_id )

То есть, задачу можно взять, если её ещё никто не брал, или если время ожидания ответа на задачу истекло. И в качестве бонуса - видно, что либо задачу никто не брал, либо кто-то брал (только последний, если таких было несколько) и прогавал сроки.

performing time может либо поставляться снаружи как параметр, либо быть свойством задачи (с соотв. полем в структуре таблицы).

SELECT id AS servie_id,
       name AS servie_name,
       MAX(TO_TIMESTAMP(created_at))::DATE AS last_trans_date
FROM services
GROUP BY 1,2
HAVING last_trans_date <= CURRENT_DATE - INTERVAL '7 day'