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

Question

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

Фон

Наша транзакционная база данных MySQL включает таблицы фактов, подобные следующей упрощенной модели:

CREATE TABLE LU_PROJECT(
    PROJECT_ID int,
    PROJECT_DESC varchar(60)
);

CREATE TABLE F_PROJECT_BUDGET(
    PROJECT_ID int,
    BUDGET numeric(15, 2)
);

CREATE TABLE F_TASK_BUDGET(
    TASK_ID int,
    BUDGET numeric(15,2)
);

CREATE TABLE  F_MONEY_USED(
    REPORTED_TIME datetime,
    TASK_ID int,
    MONEY_USED numeric(15, 2)
);

Task это ребенок Project, Идентификаторы задач не являются уникальными, но пары проект / задача уникальны.

Требования

Нам нужно сохранить инвариант, что общий бюджет для всех задач для проекта <= бюджет для проекта.

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

PROJECT_ID, PROJET_DESC, PROJECT_BUDGET, TASK_COUNT, PROJECT_MONEY_USED

вопросы

Мы обеспокоены производительностью. Самое простое решение потребовало бы обновлений для попадания в три таблицы фактов:

1. Проверьте, что добавление к F_MONEY_USED не поставит нас над бюджетом для задачи в F_TASK_BUDGET,
2. Убедитесь, что добавление денег не приведет нас к общему бюджету проекта
3. Написать в F_MONEY_USED,

И запрос, который нам нужен для получения статистики, попадет в три таблицы фактов (в дополнение к таблицам поиска, а не в приведенной выше модели):

1. Присоединиться LU_PROJECT в PROJECT_BUDGET от F_PROJECT_BUDGET группа по PROJECT_ID
2. Присоединиться LU_PROJECT в F_TASK_BUDGET получить TASK_COUNT группа по PROJECT_ID
3. Присоединиться LU_PROJECT к F_MONEY_USED получить PROJECT_MONEY_USED группа по PROJECT_ID
4. ПРИСОЕДИНИТЬСЯ LU_PROJECT к промежуточным результатам выше и получить PROJECT_DESC также.

Проблема состоит в том, что это большое объединение, и чтение и запись будут происходить часто.

Потенциальное решение

Одним из решений, которое мы рассматриваем, является добавление PROJECT_MONEY_USED поле для F_PROJECT_BUDGET это будет обновляться при записи в F_TASK_BUDGET, Это замедлит запись, но ускорит чтение.

• Решение также будет представлять сложности и проблемы целостности данных, так как таблицы фактов больше не будут "в зерне". Это нарушает принципы хранения данных, но я не могу выяснить, является ли это кошерным для транзакционных баз данных.

• Замедление записи может не вызывать особого беспокойства, если мы можем сделать оптимистичный рендеринг в пользовательском интерфейсе, но это вносит большую сложность.

Другие решения рассматриваются

• Для записи мы рассматриваем использование триггеров для сохранения инварианта.
• Для чтения рассчитанные столбцы выглядели многообещающе, но им не разрешено использовать несколько таблиц в MySQL.
• Для чтения, материализованные представления, вероятно, не вариант, данные должны быть свежими в реальном времени.

Резюме

Есть ли лучшее решение для безопасного и простого чтения и записи?