Меня зовут Екатерина Петрова, я автор медиа «вАЙТИ» и аналитик. Перечитывая свой любимый роман И. Ильфа и Е. Петрова «Двенадцать стульев», а именно сцену с архивариусом Коробейниковым, я вдруг поняла: его бумажный архив ордеров на имущество бывших дворян не что иное, как идеальный пример реляционной базы данных. Алфавитные указатели — это индексы, книги учета — таблицы с первичными ключами, ордера — настоящие транзакции.
Мне сразу захотелось пофантазировать, как бы выглядела СУБД Варфоломея Коробейникова спустя сто лет. Так и возникла идея этой статьи о структуре реляционных баз данных и базовых SQL-запросах. Начинающим знакомиться с базами данных будет полезно и интересно рассмотреть тему под таким углом.
Стулья умершей тещи Кисы Воробьянинова будем искать через SELECT. Начнем!
Бумажный прототип реляционной СУБД конфискованных стульев
В 1927 году архивариус Коробейников из романа «Двенадцать стульев» создал систему учета данных об имуществе, конфискованном у бывших дворян после революции. «Дивная канцеля��ия! Полная механизация — архив на дому!» — сказал бы Остап, увидев PostgreSQL. Давайте спроектируем эту систему в SQL и напишем ключевые DDL.
Архивариус раскрыл толстые книги учета и распределения.
— Все здесь, — сказал он, — весь Старгород! Вся мебель! У кого когда взято, кому когда выдано. А вот это — алфавитная книга, зеркало жизни! Вам про чью мебель? Купца первой гильдии Ангелова? Пожа-алуйста. Смотрите на букву А. Буква А, Ак, Ам, Ан, Ангелов… Номер? Вот 82 742. Теперь книгу учета сюда. Страница 142. Где Ангелов? Вот Ангелов. Взято у Ангелова 18 декабря 1918 года: рояль «Беккер» №97 012, табурет к нему мягкий, бюро две штуки, гардеробов четыре (два красного дерева), шифоньер один и так далее… А кому дано?.. Смотрим книгу распределения. Тот же номер 82 742… Дано. Шифоньер — в горвоенком, гардеробов три штуки — в детский интернат «Жаворонок»… И еще один гардероб — в личное распоряжение секретаря Старпродкомгуба. А рояль куды пошел? Пошел рояль в собес, во 2-й дом. И посейчас там рояль есть…
Из отрывка видно, что у архивариуса есть:
Алфавитный указатель (по фамилиям бывших владельцев мебели).
Книга учета (что именно было изъято у каждого владельца).
Книга распределения (кому и куда переданы вещи).
Ордера (документы с подписями получателей, подтверждающие передачу).
Это напоминает реляционную базу данных с несколькими связанными таблицами.
Таблица 1 — Бывшие владельцы (owners)
Коробейников сортирует их по алфавиту — это первичный ключ + индекс:
```sql CREATE TABLE owners ( owner_id INT PRIMARY KEY, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255), patronymic VARCHAR (255), social_status VARCHAR(100) ); -- Индекс для быстрого поиска по фамилиям: CREATE INDEX idx_owners_last_name ON owners(last_name); ```
По правилам нормализации БД, а именно третьей нормальной формы, в записи не должно быть столбцов с неключевыми значениями, которые зависят от других неключевых значений. Поле social_status нарушает 3НФ. То есть по-хорошему данные о социальном статусе надо бы вынести в отдельную таблицу estates.
Таблица 2 — Изъятые предметы (confiscated_items)
Пример записи:
«Рояль „Беккер“ №97 012, табурет к нему мягкий, бюро две штуки…»
```sql CREATE TABLE confiscated_items ( item_id INT PRIMARY KEY, owner_id INT REFERENCES owners(owner_id), item_name VARCHAR(255) NOT NULL, quantity INT, description TEXT, taken_at DATE, ); ```
Таблица 3 — Распределение (кому досталась мебель) (distribution)
«Шифоньер — в горвоенком, гардероб — в интернат „Жаворонок“…»
```sql CREATE TABLE distribution ( distribution_id INT PRIMARY KEY, item_id INT REFERENCES confiscated_items(item_id), recipient VARCHAR(255) NOT NULL, distribution_date DATE, warrant_number INT ); ```
Для оптимизации можно добавить индекс для поис��а по получателям:
```sql CREATE INDEX idx_distribution_recipient ON distribution(recipient); ```
Таблица 4 — Ордера на конфискованную мебель (warrants)
«На корешках все адреса прописаны и собственноручная подпись получателя»
```sql CREATE TABLE warrants ( warrant_id INT PRIMARY KEY, item_id INT REFERENCES confiscated_items(item_id), recipient_signature TEXT, issued_at DATE ) ```
ERD для базы данных
Между таблицами работает связь «один ко многим», так как у одного владельца может храниться множество изъятых предметов. Например, у Воробьянинова было изъято 13 предметов.
OWNERS → CONFISCATED_ITEMS (1:N)
Один изъятый предмет может быть распределен в несколько мест. Например, тот самый гостиный гарнитур со стульями распределен частями. Десять стульев Гамбса → музей мебельного мастерства, 1 стул → Грицацуеву.
CONFISCATED_ITEMS → DISTRIBUTION (1:N)
На каждый предмет может быть несколько ордеров (например, на разные части гарнитура).
CONFISCATED_ITEMS → WARRANTS (1:N)
SQL-запросы, которые cэкономили бы Остапу Бендеру время на поиск стульев
Чтобы найти, «куда девали все сокровища убиенной тещи» бывшего предводителя дворянства Ипполита Матвеевича Воробьянинова, в БД можно сделать такой запрос:
SELECT o.last_name, o.first_name, ci.item_name, ci.description, d.recipient, d.distribution_date FROM owners o JOIN confiscated_items ci ON o.owner_id = ci.owner_id LEFT JOIN distribution d ON ci.item_id = d.item_id WHERE o.last_name = 'Воробь��нинов'
Чтобы найти те самые стулья, в которых запрятаны сокровища, сделаем такой запрос:
SELECT ci.item_name AS "Название предмета", ci.description AS "Описание", d.recipient AS "Текущее местонахождение", d.distribution_date AS "Дата распределения" FROM confiscated_items ci LEFT JOIN distribution d ON ci.item_id = d.item_id WHERE ci.owner_id = 48238 AND ( ci.item_name LIKE '%стул%' OR ci.item_name LIKE '%Стул%' OR ci.description LIKE '%стул%' OR ci.description LIKE '%Стул%' )
Чтобы проверить ордера на гостиный гарнитур тещи Воробьянинова, сделаем запрос:
SELECT w.warrant_id, ci.item_name, w.recipient_signature, w.address FROM warrants w JOIN confiscated_items ci ON w.item_id = ci.item_id WHERE ci.owner_id = 48238 AND ci.item_name LIKE '%гарнитур%'
Вместо заключения: найдите стулья тещи Кисы Воробьянинова быстрее Остапа Бендера
Система архивариуса Варфоломея Коробейникова — это идеальный пример реляционной модели на бумаге, но с некоторыми ошибками с точки зрения DBA:
Нарушение 1НФ. В поле description хранятся списки конфискованного имущества («2 бюро, 4 гардероба»).
Отсутствие транзакционности. Нет механизма отката, если ордер потерян (аналог ROLLBACK).
Ручной поиск. Алфавитный указатель работает медленнее, чем B-tree-индекс.
Нет бэкапов. Пожар в Старкомхозе равен полной потере данных.
Эту базу вполне можно пересобрать на PostgreSQL с соблюдением всех нормальных форм. Что я сделала: создала интерактивную демоверсию БД конфискованной мебели, добавила туда фамилии раскулаченных, упомянутые авторами в книге, а также придумала еще фамилии бывших дворян, чтобы список стал больше.
Вы можете сделать запросы в базу данных Коробейникова самостоятельно, вот что для этого нужно:
Скачайте демоархив Коробейникова (сохранится файл БД Korobeynikov.db, 40 KB).
Зайдите в SQL-песочницу https://sqliteonline.com/ (или в DBeaver).
Нажмите» «+» в левом верхнем углу → нажмите Open SQLite DB → выберите и откройте из загрузок скачанный файл Korobeynikov.db.
Слева должна открыться демоверсия СУБД с названием Korobeynikov, а также четыре таблицы с фамилиями и мебелью.
Попробуйте написать SQL-запрос, который найдет те самые стулья с сокровищами, и нажмите на треугольник (RUN), чтобы получить результат.
Экспериментируйте и пишите в комментариях, что вам удалось найти 🙂
вАЙТИ — DIY-медиа для ИТ-специалистов. Делитесь личными историями про решение самых разных ИТ-задач и получайте вознаграждение.
Другие статьи по теме #Хранение данных
Greenplum vs ClickHouse: битва СУБД-титанов
Сравниваю два популярных инструмента для работы с данными, чтобы вы могли выбрать наиболее подходящий
Как добыть ценные инсайты в большом объеме
Наводим порядок в хаосе: выбираем хранилище, обрабатываем, выводим принципы
Data driven на практике: с чего начать, как избежать ошибок и эффективно применять
На примере двух кейсов: цифрового и производственного
Как перенести XML в базу данных
Рассказываю, как решил необычную задачу рефакторинга и перенес больше 40 файлов с сохранением связей
