Я активен в Переполнении стека. и одна из тем, которая постоянно возвращается, — как мне отслеживать изменения с течением времени в реляционной базе данных?. Другой вариант: Как мне спроектировать мою базу данных, чтобы я мог проверять изменения?.
Эдгар Кодд изобрел реляционную модель, и из всех приобретенных мною навыков программирования реляционные базы данных оказались наиболее полезными. Однако реляционная модель не может четко учитывать изменения с течением времени. Я не видел ответа на этот вопрос ни в одной из книг о шаблонах, поэтому я описываю решение, которое обычно рекомендую.
Это не ракетостроение. Для каждой сущности, изменение которой со временем важно для бизнес-домена или изменения которой вы хотите проверить, вы вводите следующие концепции.
- Каждая запись имеет столбцы «valid_from» и «valid_until». Они имеют типы данных datetime.
- Для каждой сущности текущая истина имеет нулевое значение в столбце valid_until.
- Изменения в этой сущности сохраняются путем создания новых записей, а не обновления этих таблиц. Таким образом, вместо того, чтобы использовать предложение «обновить» для изменения атрибута этой сущности, вы устанавливаете атрибут «valid_until» и вставляете новую запись с «valid_from», указывающим, когда запись становится действительной.
- Вы никогда не удаляете объект — вы используете атрибут статуса, чтобы определить, что запись больше не действительна.
- Если сущность имеет отношения внешнего ключа, вы никогда не удаляете записи из этих сущностей внешнего ключа.
Давайте сделаем это более конкретным.
Мы создадим схему базы данных для моделирования системы продаж. Мы опустим много деталей, чтобы сосредоточиться на том, как мы моделируем изменения с течением времени. Начнем с вещей, которые мы продаем — назовем их «предметы».
create table items (item_id int not null, valid_from datetime not null, valid_until datetime null, name varchar(10) not null, price float not null, is_sellable tinyint not null, primary key (item_id, valid_from));
Поскольку мы ожидаем, что в этой таблице будет более одной строки для каждого «элемента», мы указываем, что первичным ключом должен быть item_id в сочетании с полем valid_from. Это говорит нам о том, что эта комбинация позволяет однозначно идентифицировать каждую запись. В реалистичном сценарии это означает, что нам может понадобиться стратегия для выделения первичных ключей, и это, вероятно, не будет целым числом, но сейчас это не наша забота.
В нашем вымышленном бизнес-домене у элементов есть 3 интересных атрибута — name, price и is_sellable.
Итак, давайте создадим запись для widget и покажем, как наши модели схем меняются со временем.
insert into items values (1, '2018-01-01 00:00:00', null, 'widget', 12.00, 1);
Наш виджет имеет item_id 1, имя «виджет» и цену 12,00; он продается и был создан 1 января 2018 года.
Теперь давайте смоделируем объекты «заказ». Как правило, это делается с помощью таблицы заголовка заказа и таблицы позиций заказа. Заголовок заказа содержит атрибуты, общие для всего заказа, такие как клиент, дата заказа и идентификатор заказа. В этом примере мы не будем моделировать статус или клиента и предположим, что заголовок заказа не обязательно должен иметь измерение времени (на практике «статус», вероятно, должен иметь историю).
create table orders (order_id int not null auto_increment, order_date datetime not null, customer_id int not null, primary key (order_id));
Обратите внимание, что мы не включили в эту таблицу итоговые значения заказов — мы не должны дублировать информацию в нашей схеме, а стоимость заказа можно рассчитать по строкам заказа.
Строки заказа сообщают нам, какие товары были заказаны.
create table order_lines (order_id int not null, item_id int not null, quantity int not null);
Итак, давайте заполним таблицы order и orderline:
insert into orders values (1, ‘2018–01–02 00:00:00’, 1); insert into order_lines values (1, 1, 1);
Итак, у нас есть один заказ с одной строкой заказа для одного «виджета».
Узнаем общую стоимость заказа:
select o.order_id,
sum(i.price * ol.quantity)
from orders o
inner join order_lines ol on o.order_id = ol.order_id
inner join items i on ol.item_id = i.item_id
group by order_id
------------------------------------------------------------------
order_id sum(i.price * ol.quantity) 1 12
Теперь предположим, что цена виджета увеличивается 1 февраля:
update items set valid_until = '2018-02-01 00:00:00' where item_id = 1 and valid_until is null; insert into items values (1, '2018-02-01 00:00:00', null, 'widget', 22.00, 1);
При попытке подсчитать стоимость заказа получаем неверный ответ — 34.
Однако если мы изменим запрос, включив в него измерение времени, мы получим правильный ответ:
select o.order_id,
sum(i.price * ol.quantity)
from orders o
inner join order_lines ol on o.order_id = ol.order_id
inner join items i on ol.item_id = i.item_id
where o.order_date between i.valid_from and i.valid_until
group by order_id
------------------------------------------------------------------
order_id sum(i.price * ol.quantity) 1 12
Сравнивая дату заказа с полями действия, мы можем найти цену товара на момент размещения заказа.
О, но что произойдет, если мы разместим новый заказ после применения повышения цены?
insert into orders values (2, '2018-02-02 00:00:00', 1); insert into order_lines values (2, 1, 1);
Наш запрос не возвращает записи для порядка 2, потому что сравнение «между» ищет значение valid_until, которого не существует.
select o.order_id,
sum(i.price * ol.quantity)
from orders o
inner join order_lines ol on o.order_id = ol.order_id
inner join items i on ol.item_id = i.item_id
where o.order_date between i.valid_from and coalesce(i.valid_until, now())
group by order_id
-------------------------------------------------------------
order_id sum(i.price * ol.quantity) 1 12
Вот где COALESCE помогает:
select o.order_id,
sum(i.price * ol.quantity)
from orders o
inner join order_lines ol on o.order_id = ol.order_id
inner join items i on ol.item_id = i.item_id
where o.order_date between i.valid_from and coalesce(i.valid_until, now())
group by order_id
--------------------------------------------------------------
order_id sum(i.price * ol.quantity) 1 12 2 22
Краткое резюме:
Используя атрибуты valid_from и valid_until для сущности, мы можем отразить состояние этой сущности в произвольные моменты времени.
Для этого требуется стратегия назначения уникальных идентификаторов — столбцы с автоматическим увеличением не работают. Это также требует сравнения дат во всех запросах, что может быть источником ошибок.
Аудит
Если у вас есть потребность в аудите ключевых действий в вашей бизнес-области — «кто закрыл этот заказ?», «кто изменил налоговую ставку и когда?», вы можете добавить столбец «user_id» в эти темпоральные таблицы.
Преимущество этого подхода заключается в том, что конструкция базы данных позволяет легко восстановить данные в том виде, в каком они были на момент внесения изменений. Например, если вы хотите узнать, «кто изменил цену на виджеты 1 февраля и какова была цена в то время?», вы можете легко найти данные в таблице товаров.
Недостатком является дополнительная сложность вашей схемы — если каждая таблица подвергается аудиту, ваша схема становится неуправляемо сложной.
Как насчет производительности?
Когда я рекомендую этот подход, меня часто спрашивают о влиянии на производительность. «Не замедлит ли добавление большого количества записей мою базу данных?», «Не замедлит ли дополнительное сравнение дат мои запросы?». Короткий ответ: «почти наверняка нет».
Производительность базы данных на самом деле не зависит от количества хранимых записей — гораздо большее влияние оказывает стратегия индексирования. Пока ваши запросы могут использовать индекс, количество записей обычно не имеет значения; если ваши запросы не могут использовать и индексировать, то у вас проблемы с производительностью/масштабируемостью; этот подход может ускорить точку, в которой он появляется, но настоящая проблема заключается в том, что вы не можете использовать индекс.
Часто можно использовать комбинацию id, valid_from, valid_until в качестве уникального индекса; это означает, что любой доступ по первичному ключу будет таким же быстрым, как если бы измерения времени не существовало.
Когда этот шаблон влияет на производительность?
- Когда ваши запросы не могут использовать index.
- Когда вы сталкиваетесь с аппаратными ограничениями — может ли ваша база данных больше не хранить данные в памяти?
- Вы не должны использовать кластеризованный индекс для первичного ключа, так как вы будете вставлять строки по всей таблице, а это потребует перетасовки данных на диске.
Другие ограничения
База данных очень легко может стать намного более сложной, если каждый объект отслеживается с течением времени. Вот почему я не рекомендую это в ситуациях, когда необходимо проверять каждое взаимодействие с базой данных.
Если вы используете инфраструктуру объектно-реляционного сопоставления, она почти наверняка не понимает эту модель. Это означает, что логика вашего приложения должна понимать атрибуты времени — вы не можете просто написать order.items.first.price, чтобы получить цену первой строки заказа. Я написал адаптеры для Active Record, чтобы уменьшить эту боль, но так и не избавился от нее.
Аналогичная проблема возникает, когда вам нужно отчитываться по этим данным — большинство решений для создания отчетов не понимают этот шаблон. Чтобы смягчить это, я часто создаю представления, чтобы скрыть временную сложность, обычно в другой схеме/базе данных. Таким образом, в представлении, отображающем строки заказов на элементы, будут отображаться записи строки заказов, применимые на момент размещения заказа.
Альтернативы
Распространенной альтернативой являются таблицы денормализации и «истории», часто реализуемые с помощью триггеров. В приведенном выше примере, например, таблица «заказы» будет включать поле с именем «order_total», таблица позиций будет включать цену и т. д. Таблица «элементов» может иметь таблицу «items_history» с триггером, который копирует существующая строка в элементах в таблицу истории для каждого оператора обновления.
Это решение кажется более простым, и часто его может быть достаточно для удовлетворения потребностей бизнеса. Однако, сохраняя данные о ценах в двух местах, вы провоцируете появление ошибок и аномалий и усложняете работу с срочными бизнес-процессами. Запуск отчета, чтобы увидеть изменения цен с течением времени, мой подход прост и легок. Совместить эти изменения цены с заказом также просто; делать это с таблицей истории быстро становится сложно и некрасиво.
Резюме
Воссоздание состояния базы данных в произвольный момент времени часто необходимо как для бизнеса, так и для требований аудита. Во многих областях бизнеса «время» является критическим параметром, и все же реляционные базы данных не предлагают четкого решения. Подход, описанный в этой статье, представляет собой простое и эффективное решение.
