Вредные привычки в SQL: не создавайте ID колонку в каждой таблице

Перевод Bad Habits to Kick : Putting an IDENTITY column on every table.

Существует некая тенденция класть в каждую таблицу колонку идентификатора (суррогатного auto increment первичного ключа). Обычно это происходит потому что это самый простой способ определить каждую отдельную строку таблицы. Вот простой пример с таблицой связи многие-ко-многим, соединяющей продукты и теги на них:

CREATE TABLE dbo.Products
(
  ProductID int IDENTITY(1,1) PRIMARY KEY,
  ...
);

CREATE TABLE dbo.Tags
(
  TagID int IDENTITY(1,1) PRIMARY KEY,
  ...
);

CREATE TABLE dbo.ProductTags
(
  ProductTagID  int IDENTITY(1,1),
  ProductID     int NOT NULL FOREIGN KEY REFERENCES dbo.Products(ProductID),
  TagID         int NOT NULL FOREIGN KEY REFERENCES dbo.Tags(TagID),
  ...
);

А теперь скажите мне, в чём польза для бизнеса от колонки ProductTagID – того самого искусственного идентификатора? Когда вам нужно будет обратиться к записи по ProductTagID, при этом не имея ProductID и/или TagID? И почему именно эта колонка должна уникально идентифицировать строку в таблице? Если у вас есть эта колонка, значит вы хотите разрешить наличие многих строк с одними и теми же парами продукт-тег (что вряд ли).

Что произойдёт, если эта колонка исчезнет? Польза от этой колонки в лучшем случае сомнительна. По сути же, на каждую запись вы потратите дополнительно 8 байт. Плюс сам индекс требует места, ещё и нагрузка на пропускную способность между базой данных и приложением, да и перебалансировка индексного дерева не бесплатна.

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

CREATE TABLE dbo.ActivityLog
(
  LogID     int      IDENTITY(1,1) PRIMARY KEY,
  EventDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ...
);

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

Суть подобных таблиц в том, чтобы хранить историю по некоторым событиям. Не так часто нужно обращаться к какой-то конкретной записи. И даже если вы ищете какую-то конкретную запись, вы будете использовать не идентификатор, а время добавления, часть сообщения и т.д.

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

• определить самый большой идентификатор, принадлежащий записям старше 30-ти дней,
• присвоить его переменной,
• удалить из таблицы записи, где идентификатор меньше того, что в переменной.

Если кластерный индекс будет ориентироваться на поле datetime (время события), а поле идентификатора мы выбросим, база данных выполнит удаление проще и быстрее (не нужен будет предварительный поиск по ID).

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

CREATE TABLE dbo.ActivityLog
(
  LogID     int IDENTITY(1,1) UNIQUE NONCLUSTERED,
  EventType tinyint, -- возможно, какой-то внешний ключ на таблицу с типами событий
  StartDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  EndDate   datetime NULL,
  ...
);

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

DECLARE @LogID int;

INSERT dbo.ActivityLog(EventType) SELECT 1;

SELECT @LogID = SCOPE_IDENTITY();

-- Делаем работу

UPDATE dbo.ActivityLog
    SET EndDate = CURRENT_TIMESTAMP
    WHERE LogID = @LogID;

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

Если же вернуться к теме. Стоит отказаться от колонок идентификаторов? Естественно нет! Просто не нужно бездумно добавлять в каждую таблицу ещё одно поле просто так.