Проектирование схемы базы данных для хранения цветов и палитр: практическое руководство

Рабочая схема хранения цветов и палитр состоит из трёх таблиц: colors (справочник цветов), palettes (сами палитры) и palette_colors (связь many-to-many с полем порядка). Цвет записывается в одной канонической модели: три числовых канала RGB плюс альфа-канал в отдельном поле. HEX, HSL и CMYK выводятся конвертацией на лету или кэшируются в генерируемых столбцах.

Три таблицы закрывают почти все практические задачи: поиск цвета по HEX, выборку состава палитры, поиск палитр с конкретным оттенком, контроль дубликатов и историю правок. Упрощение до двух таблиц или одного JSONB-массива оправдано в узком случае: справочник до 1000 цветов, который читают целиком и никогда не фильтруют по отдельным каналам.

Дальше идут семь вопросов, которые нужно закрыть до первого CREATE TABLE, готовая DDL нормализованной схемы, ограничения целостности, версионирование палитр и индексы под типовые запросы. Для каждой рекомендации указано, какую проблему она снимает и чем оборачивается её отсутствие.

Что нужно решить до создания таблиц

Проектирование начинается с семи вопросов. Ответы определяют состав полей, типы, ограничения и индексы. Пропущенный вопрос почти всегда приводит к ALTER TABLE на работающей базе с миллионами строк и простоем на блокировке.

Ключевые вопросы перед проектированием

1. Объём справочника. Тысячи записей и десятки миллионов оттенков требуют разных решений. На больших объёмах индекс по трём каналам занимает больше места, чем сами данные, а поиск похожих оттенков выносится в отдельную структуру.
2. Профиль нагрузки. Каталог, который читают в 100 раз чаще, чем пишут, допускает кэш и материализованные представления. Редактор палитр с ежесекундными правками требует быстрой записи и версионирования каждой итерации.
3. Альфа-канал. Если прозрачность нужна, она хранится отдельным числовым полем, а не внутри восьмизначного HEX. Разбирать строку ради альфы на каждом чтении дороже, чем прочитать один numeric.
4. CMYK. Печатный профиль привязан к конкретному ICC-профилю и типу бумаги. Держать CMYK как каноническую модель в веб-проекте значит получить расхождения при любом пересчёте на стороне клиента.
5. История версий. Нужен ли откат к состоянию палитры на прошлой неделе и аудит «кто поменял оттенок бренда».
6. Поиск похожих оттенков. Запрос «покажи цвета рядом с #3A7BD5» решается вычислением расстояния в Lab или кэшем предрассчитанных соседей, а не обычным B-tree.
7. СУБД и версия. Генерируемые столбцы и частичные индексы есть в PostgreSQL 12 и новее, в MySQL 8.0.13 и новее. JSONB, exclusion-ограничения и GiST-индексы доступны только в PostgreSQL.

Каноническая цветовая модель и производные

В базе хранится одна модель, остальные получаются из неё. Для веб-проектов канонической удобно делать RGB: три канала 0-255 дают однозначное числовое представление, из которого детерминированно считается HEX и с известной погрешностью HSL.

HEX не подходит на роль источника истины. Строки '#ff0000' и '#FF0000' описывают один цвет, но сравниваются как разные значения. Сортировка по строке не даёт ни градации яркости, ни порядка оттенков, а арифметика по каналам требует парсинга каждой записи. HEX удобен как вторичное поле для отображения и точного поиска.

HSL хорошо подходит для генерации: сдвиг hue на 30 градусов даёт предсказуемый переход, изменение lightness управляет затемнением. Обратная конвертация теряет точность. Значение hsl(210, 85%, 47%) после перехода в RGB и обратно вернётся как hsl(210, 84.62%, 46.86%), и сравнение с исходным даст ложное расхождение. Если интерфейсу нужен HSL, храните его как кэш и явно фиксируйте в документации, что источник истины - числовые каналы RGB.

Подробный разбор иерархии токенов, семантических имён и валидации каналов собран в материале о принципах построения системы хранения цветов в IT-инфраструктуре. Там же приведена таблица форматов и матрица выбора подхода под разные типы проектов.

Базовая нормализованная схема: colors, palettes, palette_colors

Минимальная рабочая схема - три таблицы. Связь между цветами и палитрами делается many-to-many, а не массивом внутри palettes. Причина практическая: массив усложняет запрос «в каких палитрах встречается этот цвет» и требует перезаписи всей строки при добавлении одного оттенка. В связующей таблице добавление цвета сводится к INSERT одной строки.

Таблица colors: поля и типы

• id bigserial PRIMARY KEY. В распределённой системе с генерацией идентификаторов на нескольких узлах удобнее uuid, но он занимает 16 байт против 8 и даёт случайный порядок вставки, что бьёт по локальности B-tree.
• r, g, b smallint с CHECK BETWEEN 0 AND 255. Двух байт хватает с запасом, тогда как integer тратит четыре. На 10 млн строк разница только по каналам составит около 60 МБ.
• a numeric(3,2) со значением по умолчанию 1.00 и CHECK от 0 до 1. Вариант smallint 0-255 компактнее, но каждое чтение потребует деления на 255.
• hex char(7) с CHECK на маску ^#[0-9a-f]{6}$. Фиксированная длина индексируется быстрее, чем varchar(9).
• name varchar(80) для человекочитаемого имени вроде «фирменный синий».
• created_at timestamptz. Хранит момент с часовым поясом и не ломается при смене зоны на сервере или в приложении.

CREATE TABLE colors (
  id          bigserial PRIMARY KEY,
  r           smallint NOT NULL CHECK (r BETWEEN 0 AND 255),
  g           smallint NOT NULL CHECK (g BETWEEN 0 AND 255),
  b           smallint NOT NULL CHECK (b BETWEEN 0 AND 255),
  a           numeric(3,2) NOT NULL DEFAULT 1.00 CHECK (a BETWEEN 0 AND 1),
  hex         char(7) NOT NULL,
  name        varchar(80),
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT colors_rgb_a_key UNIQUE (r, g, b, a),
  CONSTRAINT colors_hex_format CHECK (hex ~ '^#[0-9a-f]{6}$')
);

Таблица palettes и связующая palette_colors

Палитра описывается именем, слагом и признаком публикации. Связующая таблица держит порядок цветов в поле position и составной первичный ключ, который сам по себе запрещает добавить один цвет в палитру дважды.

CREATE TABLE palettes (
  id          bigserial PRIMARY KEY,
  slug        varchar(64) NOT NULL UNIQUE,
  name        varchar(120) NOT NULL,
  description text,
  is_public   boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);

CREATE TABLE palette_colors (
  palette_id bigint NOT NULL REFERENCES palettes(id) ON DELETE CASCADE,
  color_id   bigint NOT NULL REFERENCES colors(id) ON DELETE RESTRICT,
  position   smallint NOT NULL CHECK (position >= 0),
  added_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (palette_id, color_id),
  CONSTRAINT palette_colors_position_key
    UNIQUE (palette_id, position) DEFERRABLE INITIALLY DEFERRED
);

Разные политики удаления выбраны осознанно. Удаление палитры удаляет её строки в связующей таблице: они не имеют смысла без родителя. Удаление цвета, который стоит в трёх палитрах, блокируется через RESTRICT. Так справочник не теряет записи, на которые ссылаются другие таблицы, и не появляется «битых» палитр с дырками в составе. Составной уникальный ключ по position объявлен отложенным, чтобы можно было переставить цвета местами в одной транзакции без промежуточного конфликта.

Когда нормализация избыточна

Нормализация окупается, когда есть хотя бы один из трёх запросов: найти все палитры с заданным цветом, обновить один цвет во всех палитрах сразу, обеспечить ссылочную целостность на уровне базы. Если таких задач нет и справочник не выходит за 1000 записей, хватит одной таблицы с JSONB-массивом.

Критерии перехода к простой схеме: данные читаются целиком и отдаются в API как единый документ, поиск по отдельным каналам не нужен, цвета не переиспользуются между палитрами, объём правок низкий. Как только появляется переиспользование, денормализованная схема превращается в источник рассинхрона: один и тот же оттенок правится в десяти местах по-разному.

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

Хранение HEX, RGB, HSL и альфа-канала без дублирования

Главное правило: одна модель записывается, остальные вычисляются. Синхронизация нескольких полей вручную через триггеры или код приложения работает ровно до первого сбоя транзакции, после чего база накапливает противоречивые записи: hex равен #ff0000, а каналы описывают другой цвет.

Типы полей для HEX, RGB и HSL

RGB хранится в трёх smallint с ограничением диапазона. HEX занимает char(7) для шестизначного значения и char(9) для восьмизначного с альфой. HSL требует h как smallint 0-360, а s и l как numeric(5,2) в диапазоне 0-100: целочисленные проценты теряют заметную часть оттенков, например переход 84.62% против 85%.

ALTER TABLE colors
  ADD COLUMN h smallint CHECK (h BETWEEN 0 AND 360),
  ADD COLUMN s numeric(5,2) CHECK (s BETWEEN 0 AND 100),
  ADD COLUMN l numeric(5,2) CHECK (l BETWEEN 0 AND 100),
  ADD CONSTRAINT colors_hsl_consistent
    CHECK ((h IS NULL AND s IS NULL AND l IS NULL)
        OR (h IS NOT NULL AND s IS NOT NULL AND l IS NOT NULL));

Ограничение colors_hsl_consistent запрещает состояние, когда заполнена только часть HSL-полей. Такие полупустые записи ломают конвертеры и дают случайные цвета в интерфейсе.

Конвертация и синхронизация значений

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

ALTER TABLE colors
  ADD COLUMN hex char(7) GENERATED ALWAYS AS (
    '#' || lpad(to_hex(r::int), 2, '0')
        || lpad(to_hex(g::int), 2, '0')
        || lpad(to_hex(b::int), 2, '0')
  ) STORED;

Генерируемый столбец нельзя записать вручную, поэтому рассинхрон между HEX и каналами становится невозможным по построению. Альфа-канал в такой HEX не входит: держать прозрачность в отдельном поле проще, чем разбирать восьмизначную строку при каждом расчёте. Если нужен именно RGBA в текстовом виде, соберите его во view.

Погрешность округления стоит проверять заранее. Для HSL→RGB→HSL расхождение в пределах 0.5% по lightness нормально, а расхождение в 3-4% означает, что где-то применено целочисленное округление. Общая логика сравнения форматов и потерь точности собрана в материале о форматах хранения цветов HEX, RGB, HSL и CMYK.

Целостность данных: уникальность, ограничения и защита от дубликатов

Ограничения на уровне схемы дешевле исправления данных. Запись с каналом 300 или повтор цвета попадает в базу только при полном отсутствии проверок в БД, а такие записи потом приходится искать вручную по всему объёму.

Уникальность цветов и нормализация HEX

Уникальность строится по числовым каналам, а не по строке HEX. Строка допускает варианты '#FF0000', '#ff0000' и '#Ff0000', и UNIQUE по ней пропустит три записи об одном цвете. Ключ по (r, g, b, a) закрывает вопрос полностью.

CREATE UNIQUE INDEX colors_rgb_a_uidx ON colors (r, g, b, a);

ALTER TABLE colors
  ADD CONSTRAINT colors_hex_lower CHECK (hex = lower(hex));

Для генерируемого столбца проверка регистра не нужна: значение собирается из to_hex, который всегда возвращает нижний регистр. Для полей, заполняемых приложением, CHECK на нижний регистр и маску обязателен, иначе в таблице появятся строки вроде 'красный' или '#ff00' и поиск по точному совпадению начнёт врать.

Внешние ключи и каскадные операции

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

Замена физического удаления цвета на soft delete даёт ещё один уровень защиты: запись остаётся доступной для аудита и восстановления палитр, которые на неё ссылались. Ограничение CHECK на диапазоны каналов при этом ловит ошибки приложения на входе, а не через сутки в отчёте.

Версионирование палитр и история изменений

Правки палитр идут волнами: бренд меняет оттенок, маркетинг просит вариант для тёмной темы, дизайнер правит контраст. Без истории откат делается руками по памяти, а вопрос «почему кнопка стала другого цвета» остаётся без ответа.

Снимки версий против temporal-подхода

Снимок хранит полный состав палитры на момент сохранения. Восстановление сводится к чтению одной строки и повторной вставке состава, а сравнение двух версий делается обычным сравнением двух JSONB-документов. Место расходуется щедро: палитра из 20 цветов в 300 версиях даст около 6000 записей в снапшоте.

CREATE TABLE palette_versions (
  id         bigserial PRIMARY KEY,
  palette_id bigint NOT NULL REFERENCES palettes(id) ON DELETE CASCADE,
  version    integer NOT NULL,
  snapshot   jsonb NOT NULL,
  checksum   char(64) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by bigint,
  UNIQUE (palette_id, version)
);

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

CREATE TABLE palette_colors_temporal (
  palette_id bigint NOT NULL,
  color_id   bigint NOT NULL,
  position   smallint NOT NULL,
  valid      tstzrange NOT NULL,
  PRIMARY KEY (palette_id, color_id, valid),
  EXCLUDE USING gist (palette_id WITH =, color_id WITH =, valid WITH &&)
);

Ограничение EXCLUDE не даст завести два пересекающихся интервала для одной пары палитра-цвет. Это ровно та ошибка, которая в обычной таблице с valid_from и valid_to остаётся незамеченной и портит историю.

Практическое правило выбора: снимки для палитр до 100 цветов и до нескольких десятков правок в месяц, temporal для больших каталогов с ежедневными изменениями. Механику снимков, дельта-хранения и семантического версионирования токенов подробно разбирает руководство по версионированию цветов и палитр.

Аудит изменений и soft delete

Поля created_by, updated_by и deleted_at закрывают вопросы авторства и восстановления. Физическое удаление палитры рвёт ссылки из релизов, документации и макетов, поэтому запись помечается временем в deleted_at, а выборки фильтруют её условием deleted_at IS NULL.

SELECT id, name, updated_at
FROM palettes
WHERE deleted_at IS NULL
ORDER BY updated_at DESC;

INSERT INTO palette_colors (palette_id, color_id, position)
SELECT pv.palette_id,
       (item->>'color_id')::bigint,
       (item->>'position')::smallint
FROM palette_versions pv,
     jsonb_array_elements(pv.snapshot->'colors') AS item
WHERE pv.palette_id = 42 AND pv.version = 7
ON CONFLICT (palette_id, color_id)
DO UPDATE SET position = EXCLUDED.position;

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

Индексы и типы полей для быстрых выборок

Индекс создаётся под конкретный запрос. Универсального набора нет: индекс, ускоряющий выборку состава палитры, бесполезен для поиска цвета по HEX, но при этом замедляет каждую вставку.

Индексы под типовые запросы

CREATE INDEX colors_hex_idx ON colors (hex);
CREATE INDEX colors_name_lower_idx ON colors (lower(name));
CREATE INDEX palette_colors_palette_pos_idx ON palette_colors (palette_id, position);
CREATE INDEX palette_colors_color_idx ON palette_colors (color_id);
CREATE INDEX palettes_updated_idx ON palettes (updated_at DESC) WHERE deleted_at IS NULL;

• colors_hex_idx ускоряет поиск точного цвета и проверку существования перед вставкой.
• colors_name_lower_idx нужен для поиска по имени без учёта регистра. Функциональный индекс по lower(name) работает, а обычный B-tree по name такой запрос не использует.
• palette_colors_palette_pos_idx составной: он закрывает выборку состава палитры и отдаёт строки уже отсортированными по position, избавляя от сортировки в плане.
• palette_colors_color_idx отвечает за обратный запрос «в каких палитрах есть этот цвет». Без него такой поиск идёт полным сканированием связующей таблицы.
• palettes_updated_idx частичный: в индекс попадают только неудалённые палитры, поэтому он меньше и быстрее обновляется.

Проверка плана занимает одну команду. Сравните вывод до и после создания индекса, обращая внимание на строку Index Scan против Seq Scan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.hex, pc.position
FROM palette_colors pc
JOIN colors c ON c.id = pc.color_id
WHERE pc.palette_id = 42
ORDER BY pc.position;

Индекс не используется в трёх типичных случаях: выборка возвращает больше 5-10% таблицы, условие обёрнуто функцией без функционального индекса, тип аргумента не совпадает с типом столбца при неявном приведении. Крупный справочник цветов с миллионами строк стоит партиционировать по диапазону каналов только при реальной необходимости, потому что партиционирование усложняет уникальные индексы.

Выбор типов полей и его влияние на производительность

smallint занимает 2 байта и полностью покрывает диапазон 0-255, тогда как integer тратит вдвое больше. На справочнике в 10 млн цветов разница по трём каналам составляет около 60 МБ только данных, плюс пропорционально растут индексы и объём чтения при сканировании.

Отдельная тема - поиск похожих оттенков. B-tree по каналам здесь не помогает: близкие цвета лежат в разных частях индекса. Рабочие варианты: считать расстояние в Lab в приложении по небольшой выборке-кандидату, держать таблицу предрассчитанных соседей или использовать индекс по векторам, если СУБД его поддерживает.

Практические примеры схем и миграций

Ниже полный DDL нормализованной схемы, денормализованный вариант с JSONB и порядок миграции между ними. Синтаксис приведён для PostgreSQL, различия с MySQL указаны отдельно.

DDL нормализованной схемы

CREATE TABLE colors (
  id         bigserial PRIMARY KEY,
  r          smallint NOT NULL CHECK (r BETWEEN 0 AND 255),
  g          smallint NOT NULL CHECK (g BETWEEN 0 AND 255),
  b          smallint NOT NULL CHECK (b BETWEEN 0 AND 255),
  a          numeric(3,2) NOT NULL DEFAULT 1.00 CHECK (a BETWEEN 0 AND 1),
  hex        char(7) GENERATED ALWAYS AS (
    '#' || lpad(to_hex(r::int), 2, '0')
        || lpad(to_hex(g::int), 2, '0')
        || lpad(to_hex(b::int), 2, '0')
  ) STORED,
  name       varchar(80),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX colors_rgb_a_uidx ON colors (r, g, b, a);
CREATE INDEX colors_hex_idx ON colors (hex);

CREATE TABLE palettes (
  id         bigserial PRIMARY KEY,
  slug       varchar(64) NOT NULL UNIQUE,
  name       varchar(120) NOT NULL,
  description text,
  is_public  boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE TABLE palette_colors (
  palette_id bigint NOT NULL REFERENCES palettes(id) ON DELETE CASCADE,
  color_id   bigint NOT NULL REFERENCES colors(id) ON DELETE RESTRICT,
  position   smallint NOT NULL CHECK (position >= 0),
  added_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (palette_id, color_id),
  CONSTRAINT palette_colors_position_key
    UNIQUE (palette_id, position) DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX palette_colors_color_idx ON palette_colors (color_id);
CREATE INDEX palettes_updated_idx ON palettes (updated_at DESC) WHERE deleted_at IS NULL;

Роли ограничений: CHECK на каналы отсекает выход за диапазон, UNIQUE по четырём каналам убирает дубликаты, генерируемый hex исключает рассинхрон, RESTRICT на цвет защищает справочник, отложенный уникальный ключ по position позволяет переставлять цвета в одной транзакции.

Денормализованный вариант с JSONB

CREATE TABLE palettes_denorm (
  id         bigserial PRIMARY KEY,
  name       varchar(120) NOT NULL,
  colors     jsonb NOT NULL DEFAULT '[]'::jsonb,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX palettes_denorm_colors_idx
  ON palettes_denorm USING gin (colors jsonb_path_ops);

Чтение палитры в такой схеме идёт одним запросом без JOIN, что заметно на read-heavy каталогах. Целостность цветов при этом контролирует приложение: база не знает, что оттенок уже существует в другой палитре. Поиск по составу возможен только через оператор вхождения.

SELECT id, name
FROM palettes_denorm
WHERE colors @> '[{"hex": "#3a7bd5"}]'::jsonb;

Оператор вхождения работает с GIN-индексом jsonb_path_ops. Альтернатива через jsonb_array_elements требует разворачивания массива и индекс не использует, поэтому на больших объёмах она превращается в полное сканирование.

Миграция между схемами

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

ALTER TABLE colors
  ADD COLUMN a numeric(3,2) NOT NULL DEFAULT 1.00;

INSERT INTO palette_colors (palette_id, color_id, position)
SELECT pcc.palette_id,
       pcc.color_id,
       row_number() OVER (
         PARTITION BY pcc.palette_id ORDER BY pcc.sort_order
       ) - 1
FROM legacy_palette_colors pcc
ON CONFLICT (palette_id, color_id) DO NOTHING;

Функция row_number пересчитывает позиции с нуля, поэтому в новой таблице не остаётся дыр после удалённых связей. Для переноса цветов из JSONB-массива используйте INSERT ... SELECT DISTINCT с преобразованием строки HEX в каналы и ON CONFLICT DO NOTHING по ключу (r, g, b, a): так дубликаты оттенков из разных палитр схлопнутся в одну запись справочника.

Порядок работ для снижения риска: сначала наполнение, потом сверка количества строк и контрольных сумм, затем переключение чтения, и только после периода наблюдения удаление старых таблиц. Для MySQL меняются типы: tinyint unsigned вместо smallint, json вместо jsonb, а синтаксис исключения пересечений интервалов недоступен, его придётся проверять триггером. Развернуть тестовый контур с managed PostgreSQL и прогнать миграцию на копии данных удобно через облачную инфраструктуру, например Timeweb Cloud: снимок базы создаётся перед экспериментом, а ресурсы масштабируются под объём справочника.

Чек-лист внедрения и типичные ошибки

Собранные вместе решения, которые стоит принять до запуска: одна каноническая модель (каналы RGB плюс alpha), HEX как производное поле, UNIQUE по (r, g, b, a), CHECK на диапазоны каналов и формат HEX, внешний ключ на цвет с ON DELETE RESTRICT, индекс по color_id в связующей таблице, версионирование через таблицу снимков, soft delete вместо физического удаления, timestamptz для всех временных полей.

Начните с DDL из раздела с примерами: три таблицы, ключ по каналам, ограничения на диапазоны и пять индексов. Прогоните EXPLAIN для своих реальных запросов, сверьте план с ожидаемым Index Scan и только после этого добавляйте версионирование и кэш производных представлений. Такой порядок даёт рабочую схему за один проход и не требует переписывать структуру, когда в справочнике появятся первые сто тысяч цветов.