Представим: есть одна большая главная таблица в базе данных. В эту таблицу пихают самые разные сущности: телеметрические атрибуты, ширину колонок отчётов, ссылки на справочники транслитерации, перечень команд станков ЧПУ, пути к иконкам для кнопок и пр. Это пример того, как делать не нужно.

Но если тема одной сверх-таблицы всё-таки всплывает в обсуждениях, может это всё-таки где-то применимо? Принцип одной таблицы чаще предлагается, когда пишут про такие штуки, как Activity Scheme и Event Sourcing.

Статьи чаще содержат только теоретические рассуждения этих концепций, иногда пишутся с позиции системного аналитика. Сейчас рассмотрим примеры с позиции программиста. Я не люблю абстрактные вузовские примеры, поэтому будут два сценария использования с моих мест работы: обработка маркированного товара (привет, «Честный знак»!) и телеметрия сети вендинговых устройств.

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

Упомянутые Activity Scheme и Event Sourcing — это просто способы моделирования данных, коих много. В них таблица хранит в себе разнородные сущности. Это плохо? Да. Например, есть сущность «клиент» и сущность «автомобиль». Для них лучше сделать отдельные таблицы customer и car. Здравомыслящему человеку не придёт в голову сочетать это вместе. Люди, которые заедают доширак чёрной икрой, чем вы руководствуетесь?

Но если не следовать правилу «только одна таблица» совсем уж строго, а иметь ещё несколько связанных «справочников», то иногда идея имеет право на жизнь. Два примера я приведу, а закономерность новички пусть найдут сами.

Первый пример, где это оправдано. Маркировка товаров

Для тех, кто не знает, что такое маркировка, я описал её предельно кратко и очень упрощённо под спойлером — там всё просто.

Это полезно знать ещё и для борьбы с контрафактом

Есть товар Х, который на законодательном уровне завод-изготовитель обязан как бы «пронумеровать». Эта «нумерация» глобальна по всей стране и не повторяется у разных производителей товара Х. Когда маркированный товар поступает к нам на склад от любого поставщика, мы обязаны сообщить государству все «номера» пачек этого товара, которые оказались у нас. В роли «номеров» выступают красивые штрихкоды. Типа такого:

Эти штрихкоды называются datamatrix. Да, это не QR-код! Это разные штрихкоды!

Если мы израсходовали упаковку товара — сообщаем об этом государству. Если кому-то перепродали — тоже. Каждый новый собственник отчитывается о полученных им упаковках. Так достигается полный поштучный учёт. Если кто-то попробует своровать — его проще вычислить. Если в кучу товара подбросят идентичные подделки — это легче заметить. Если попробовать продать товар дважды — обратят внимание.

В идеале у каждой упаковки прослеживается весь жизненный путь от конвейера у изготовителя аж до самой кассы у продавца.

Поштучный учёт товара хорошо ложится на идею event sourcing. В одной таблице хранятся записи обо всех событиях, произошедших с каждым штрихкодом каждой упаковки, которая когда-либо была в наших руках. При этом события:

1. бывают непохожими друг на друга

2. часто не связаны между собой

3. происходят в разное время

4. могут повторяться и даже зацикливаться

5. иногда завершаются неудачей

6. имеют самые разные причины возникновения

7. могут менять статус упаковки, а могут и нет

Посмотрите на таблицу, которую обсудим в следующем абзаце:

В моём случае таблица называется datamatrix_event. Кроме неё, естественно, есть отдельная таблица, где хранятся сами штрихкоды, которые мы когда-либо сканировали. Эта таблица безобразно проста:

Во всех остальных таблицах, где нам нужно хранить штрихкод, на самом деле хранится его идентификатор из этой таблицы. Например, из datamatrix_event на неё внешним ключём ссылается поле datamatrix_id. Также есть внешние ключи с поля event_type на крошечную таблицу-справочник событий и с поля user_id на сами понимаете что.

Давайте рассмотрим историю одного случайно взятого штрихкода (он имеет внутренний номер 74816758). Для этого можно выполнить очень простой запрос:

SELECT *
FROM datamatrix_event
WHERE barcode_id = 74816758

Мы получили вот такой результат (для простоты я убрал столбцы id и datamatrix_id, а идентификатор события event_type и user_id заменил на понятные человеку названия):

Что мы теперь знаем про наш штрихкод? А всё:

1. Он появился у нас 17.12.2022 в 12:20:47 во время приёмки, которую осуществлял сотрудник Иванов. Принятый товар был занесён им в документ приёмки ПР20734. Там, разумеется, перечислен вообще весь товар, который им был в тот момент принят.

2. При приёмке было создано задание #70014 для планировщика. Цель задания — узнать в системе регулятора товарооборота сведения о принимаемом товаре: легальный он, или контрафакт, не истёк ли срок годности, приложены ли сертификаты, не отозван ли производителем? Планировщик забрал его в 17.12.2022 в 12:25:00.

3. В 13:00:00 17.12.2022 планировщик обработал задание #70191, в котором нашёл ответ из регулирующего органа. Только после этого мы приняли решение о том, что согласны принять этот товар.

4. В 13:04:00 17.12.2022 планировщик обработал запрос, что мы согласны с приёмкой товара, который мы при этом включили в заказ покупки ЗПК20442.

5. В 14:00:01 17.12.2022 начали приходить ответы от регулятора, в которых обнаружено, что он одобрил переход товара из заказа ЗПК20442 в нашу собственность.

6. Немного полежав на складе, товар вызвал интерес у одного из наших покупателей и он у нас его купил, оформив заказ продажи ЗПР209473. При получении покупатель, посредством того же регулятора, уведомил о том, что товар теперь находится у него и направил запрос о том, что товар теперь должен числиться за ним. 21.21.2022 в 08:27:14 мы получили этот запрос от покупателя.

7. В 08:30:00 21.21.2022 снова отработал планировщик, который отправил сообщение о том, что мы согласны с тем, что действительно продали этот товар нашему клиенту и не против, чтобы товар числился в его собственности.

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

Вы же обратили внимание, что в таблице не указан поставщик, у которого мы покупали? Эо как раз из-за того, что его мы можем посмотреть в документе ЗПК20442. А часто это нужно? Если редко, то схема хранения event sourcing себя оправдала. Если часто — уже не факт. Для вашей предметной области вам в первую очередь нужно искать ответ именно на этот вопрос.

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

Остановлюсь подробней на житейских бытовых рабочих буднях.

Использовать datamatrix_event очень удобно при расследовании инцидентов по горячим слезам (слезам!). Бывает, что спросят: «Кто купил этот товар?». По его штрихкоду мы в datamatrix_event узнаём документ продажи. По номеру документа (ЗПР209473, например) мы выясним и покупателя, и цену, и кучу другой полезной информации.

Наоборот, если кто-то в бухгалтерии или отделе продаж будет анализировать документы, то их будут интересовать прибыль, сроки доставки, или же контакты клиента, но никак не номера штрихкодов и их картинки. Поэтому работа этой категории сотрудников не будет вызывать запросов к таблице datamatrix_event.

Таким образом, существуют разные группы сотрудников, у одних из которых работа напрямую связана с datamatrix_event, а у других эта таблица не вызывает никакого интереса. Из-за этого «нагрузка» на разные таблицы таблицы в БД распределяется более равномерно.

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

Подведу итог по первому примеру. Если информация о событиях объекта требует обязательного хранения, но подробности требуются в общем-то редко — это признак посмотреть в сторону Event Sourcing. Если в вашей предметной области есть чёткое разделение зон ответственности сотрудников, где кто-то работает с событиями, а кто-то — только со сводными данными, то это тоже признак для Event Sourcing.

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

Второй пример. Телеметрия сети вендинговых аппаратов

Допустим, имеется абстрактная сеть из 1000 аппаратов по автоматической выпечке вкусных блинчиков. Помимо выпечки, аппараты выполняют функцию розничной продажи этой вкуснятины. Человеку достаточно подойти, внести оплату и подождать блинов.

Немного вникнем в ситуацию. Устройства расставлены в разных районах города, обслуживать и осматривать их только физическим способом — дорого. Желательно отправлять техников на точку только тогда, когда это реально нужно. Все операции, которые можно выполнить удалённо (перезагрузка устройства, обновление ОС, калибровка и всё-всё-всё) лучше выполнять без участия техников.

Ситуация осложняется тем, что устройства выходят на связь через 2G/3G/4G-модем, а значит кто-то из устройств иногда может оставаться без связи. А ещё у аппаратов разная комплектация: кто-то принимает только безналичную оплату, кто-то принимает банкноты, кто-то оснащён монетоприёмником, а кто-то всем этим сразу. Устройства имеют разную начинку из всевозможных датчиков и узлов, набор которых отличается у разных экземпляров этого зоопарка:

• у моделей бывают как аппаратные, так и сенсорные кнопки

• кто-то может измерять вязкость теста, а кто-то нет

• отдельно стоящие устройства оснащены сигнализацией, а стоящие в ТЦ и других защищённых местах — нет

• где-то есть доступ к водопроводу и канализации, а где-то всё автономно.

Теперь вернёмся к базе данных. Конечно, у нас будет таблица с перечнем устройств. Она может выглядеть примерно так:

CREATE TABLE public.device (
	id int4 NOT NULL,
	"name" varchar NOT NULL,
	/* здесь остальные поля, характеризующие наличие или
     * отсутствие какой-то возможности и т. п.
     * Примеры полей: наличие подогрева сгущёнки перед подачей,
     * возможность ручной регулировки количества сахара,
     * номер sim-карты в модеме, тип станции.
     */
	CONSTRAINT device_pk PRIMARY KEY (id)
);

А как мы можем хранить обмен данными с этими устройствами? Можно (но не нужно!) запихнуть всё в одну таблицу с большим количеством столбцов, которые умеют в null. Она может быть, утрированно, такой:

CREATE TABLE public.many_null_column (
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
	dt_creation timestamp NULL DEFAULT now(),
	a int4 NULL,
	b int4 NULL,
	c int4 NULL,
	d int4 NULL,
	e int4 NULL,
	f int4 NULL,
	g int4 NULL,
	h int4 NULL,
	i int4 NULL,
	j int4 NULL,
	k int4 NULL,
	l int4 NULL,
	m int4 NULL,
	n int4 NULL,
	o int4 NULL,
	p int4 NULL,
	r int4 NULL,
	s int4 NULL,
	t int4 NULL,
	u int4 NULL,
	v int4 NULL,
	w int4 NULL,
	x int4 NULL,
	y int4 NULL,
	z int4 NULL,
	device_id int4 NULL,
	CONSTRAINT many_null_column_pk PRIMARY KEY (id)
);

Здесь каждое поле a, b, c, …, z — это значение какого-либо параметра на станции в указанный момент времени. Периодически устройство отправляет свои значения и мы можем наблюдать их динамику.

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

Здесь скучное описание минусов такой таблицы

Если какие-то параметры передаются чаще, чем другие, то многие ячейки будет содержать NULL и анализировать эту кашу будет тяжело. А если в строчке заполнено лишь одно-единственное значение из 20-30?

Также обратите внимание на то, как в БД хранятся значения NULL. В той же PostgreSQL они хранятся весьма экономно — NULL-ячейка занимает лишь один бит (с поправкой на кратность одному байту, конечно). Но это не во всех СУБД! Для примера, сделаем такую же таблицу, но столбцов в ней меньше:

CREATE TABLE public.few_null_column (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
	dt_creation timestamp NULL DEFAULT now(),
	a integer NULL,
	b integer NULL,
	c integer NULL,
	d integer NULL,
	device_id integer NULL,
	CONSTRAINT few_null_column_pk PRIMARY KEY (id),
	CONSTRAINT few_null_column_fk FOREIGN KEY (device_id) REFERENCES public.device(id),
);

Заполним обе таблицы всего лишь одним миллионом идентичных записей:

INSERT into many_null_column (a, e, h) values (1, 2, 3);
INSERT into few_null_column (a, b, c) values (1, 2, 3);

Разница в размере таблиц пока небольшая, но она всё же есть даже на таком количестве записей. В Dbeaver это видно вот так:

Если бы наши столбцы не умели null, а заполнялись значениями по умолчанию (обычными математическими нулями), разница была бы пропорциональна разнице в количестве столбцов у таблиц. Это существенно и на таком небольшом числе строк.

Но я отмечу ещё один недостаток такого подхода, хотя он больше философский. Всё-таки в таблице лучше хранить данные, которые связаны между собой относительно сильно. Держать вместе колонки «Температура смеси», «Количество свободных Мб на SSD», «Версия ПО» и «Сумма продажи» — не всегда хорошая идея. Данные в таблице должны отражать влияние друг на друга, чтобы его можно было анализировать, строить графики. Вы точно будете строить график зависимости содержания сахара в блинах от количества мегабайт на диске или уровня сигнала 4G?

В общем, такой таблицепокалипсис нам скорее во вред.

Я бы предпочёл разбить данные на группы и хранить их в разных таблицах, но в принципе может подойти и модель с одной таблицей. Например, вот такой:

CREATE TABLE public.event_entry (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
	"type" smallint NOT NULL,
	dt_creation timestamp NULL DEFAULT now(),
	value integer NULL,
	device_id integer NOT NULL,
	CONSTRAINT event_entry_pk PRIMARY KEY (id),
	CONSTRAINT event_entry_fk FOREIGN KEY (device_id) REFERENCES public.device(id),
	CONSTRAINT event_entry_fk_1 FOREIGN KEY ("type") REFERENCES public.event_type(id)
);

Вариант для визуалов:

Вы заметили, что и у этой «одной» таблицы есть сразу две небольших таблицы-спутницы: типы измеряемых параметров и список самих устройств. Эти таблицы очень стабильны и меняются не очень часто. Напротив, в event_entry записи вставляются регулярно.

Данные в этой таблице размещены плотно и не возникает null-столбцов. Если есть вероятность появления неопознанных устройств, которые начали отправлять на ваш сервер какие-то данные, то можно убрать внешний ключ на таблицу устройств и всё продолжит работу. Это может произойти если вы открыли филиал вашей сети где-нибудь далеко и местные ребята установили новые аппараты, не сообщив об этом вам.

Допустим, от устройства №245 пришла одна новая порция измеренных значений: температура, остаток муки, текущий баланс. В таблицу будет вставлено три строчки (я опустил столбец ID):

Здесь тип «2» — это код, обозначающий в event_entry тип данных «температура», «8» — это остаток муки, «11» — объём наличности в аппарате.

Как видно, схема в целом работает, хотя сразу бросаются в глаза недостаток: так хранить неудобно, если измеряемые значения имеют разные типы данных с точки зрения БД. Например, даже температура — это не всегда integer, а какие-то параметры вообще бывают текстовыми, значит их не получится хранить в таблице такого рода.

Промежуточные итоги обзора

Основной итог, в общем, всем очевиден с самого начала и даже отражён в названии одной из приведённых схем — event sourcing. Она хорошо зайдёт при хранении событий. Ваша система будет хорошо согласовываться с бизнес-процессами в реальном мире.

Отметим несколько мене очевидных плюшек, которые можно извлечь из данной схемы.

Например, такие таблицы легко компрессировать. Ваши данные с течением времени могут терять ценность. Если удалять жалко, то можно просто сжать, уменьшив детальность. В сухом остатке получим сводные данные по какому-то событию/действию за некий период. Посмотрите на характерный пример истории пополнения баланса сотового телефона:

Как видно, абонент последние три месяца вносил по 1000 рублей, а первая строчка — результат компрессии его платежей за целых 10 лет. За прошедшие годы детальность внесения платежей потеряла свою актуальность и уже никому не интересно: то ли абонент и раньше платил по 1000 в месяц, или два раза в месяц по 500, или оплачивал на год вперёд 12 тысяч. Итог один — все эти деньги он уже потратил. На работе можно увидеть и более жёсткие примеры компрессии.

Ещё одна плюшка: в какой-то мере термин «событие» можно рассматривать как синоним «измерение» или «разница» между старым значением и новым. Соответственно, способ хранения в одной таблице может подойти для хранения информации о меняющихся с течением времени сущностях. Если в вашей предметной области важен только факт изменения, но не имеет значение время этого изменения — это ещё более весомый аргумент в пользу выбора этой схемы. Вы же обратили внимание, что во всех примерах присутствует поле с типом времени? Условный timestamp занимает 4 байта, что на огромном числе строк даёт большой размер.

Самая неочевидная плюшка: отображение таблиц на маленьких экранах. Эта проблема обсуждалась и не раз. Для тех, кто любит использовать что-то типа Bootstrap-овских карточек, это может оказаться хорошей идеей:

Как видно, столбцы id и type из первого примера естественным образом совмещёны, dt_creation бросается в глаза, но сохраняется возможность фильтрации и сортировки.

На самом деле, есть у меня и третий пример

Расскажу ещё один ма-а-а-аленький плюс, который я как-то раз сумел извлечь из описанной схемы. Не факт, что вам это потребуется, но вдруг?

В общем, пришёл к нам как-то стажёр. Мозгами человек совсем неглупый, но опыта в программировании нет, даже поверхностного. Про SQL знал только SELECT * FROM table, а ещё на php показал два простеньких скрипта с простейшим вводом/выводом и одним циклом.

Вот мы ему и дали простую работу: нужно скопировать все данные из таблицы №1 в таблицу №2. Всё.

Обе таблицы содержали историю складских операций (перемещения внутри одного филиала в первой таблице, внутри другого — во второй). Для нас, с учётом чая и перекуса, работы на часок максимум. Стажёр ковырялся с этой работой всю рабочую неделю плюс понедельник следующей. Естественно, серьёзной практикой это нельзя назвать никак, но человек понял, чем занимаются программисты и в нём загорелся огонь. За эти дни человек сам узнал про WHERE в SQL, pg_query() в PHP и выяснил, что свой код, оказывается, нужно тестировать. Да, через некоторое время он стал нашим маленьким джуном.

Ну а какое задание мы ещё могли ему дать?!

Событиировали, событиировали, да невысобытиировали

Не бывает так, чтобы удачная идея не имела недостатков даже там, где ей самое место. У приведённой схемы есть очень важный недостаток — неизбежно большое число строк. Эмпирическое правило гласит, что чем больше строк, тем больше время поиска, пусть даже у вас есть индекс по нужному полю.

Небольшое количество столбцов плотного заполнения не всегда приводит к экономии места в БД, если сравнивать с таблицами, где много столбцов, но которые часто имеют значение NULL. А размер, очевидно, имеет значение. Как уже упоминалось ранее, современные СУБД умеют нехило справляться с этой проблемой, экономно храня NULL. Из-за этого итоговый размер таблицы гораздо сильней зависит от числа строк, чем новичкам может показаться изначально.

Event Sourcing — это в некоторых случаях антипаттерн. На хабре об этом уже была статья. А ещё проскальзывала идея использовать этот подход в качестве альтернативы двойной бухгалтерской записи (или как она там правильно называется?). Предполагается, что модель гораздо удобней и проще при поиске ответа на вопрос: «Откуда пришли деньги и куда они ушли». Но пообщаться на эту тему с бухгалтерами — то ещё приключение.

Финализируем прочитанное

Если ваши бизнес-процессы содержат много нюансов и сложны для формализации, то придётся очень долго придумывать архитектуру вашей БД. Не исключено, что простым схемам в ней вообще не найдётся места. Нет гарантии того, что даже отличное в первое время решение со временем не превратится в обузу. Так или иначе, это не повод изначально забивать на структуру таблиц со словами: «И так сойдёт!».

P. S. Кстати, в конце своей прошлой статьи я так и не разместил рекламу своего телеграм-канала, так как у меня его не было. Собственно, у меня его по-прежнему нет, а значит и в этот раз ссылки на него не будет :-)

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