Перечисляемый тип в базе данных

Виноградов С. А.
17.05.2001 г.

Введение

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

Рассмотрим, например, объект класса работники (Employees), у которого есть свойство вид оплаты труда (SalaryType). Видов оплаты (SalaryTypes) может быть несколько: сдельная (Piece), почасовая (Tariff), оклад (Salary) и т.д. В зависимости от этого, работнику рассчитывают зарплату различными методами. В языке программирования с поддержкой перечисляемого типа данных, класс таких объектов будет выглядеть следующим образом (синтаксис C++):

enum SalaryTypes { Piece, Tariff, Salary }; // Различные виды оплаты труда

class Employees
{
    ...
    SalaryTypes SalaryType; // Вид оплаты труда
};

Требования к перечисляемому типу

При попытке создания подобного объекта в базе данных, нам придется столкнуться с некоторыми дополнительными требованиями.

Во-первых, поле для хранения данного свойства в таблице объекта должно быть целочисленного типа — из соображений эффективности и однообразности.

Во-вторых, где-то в базе обязательно должно хранится название на нормальном человеческом языке каждого из возможных значений данного свойства. Это необходимо для получения клиентом готового результата запроса, который не нужно дополнительно обрабатывать, перед тем, как показать пользователю. Также название нужно показывать при изменении пользователем свойства объекта. А лучше хранить названия в одном месте в базе, чем в разных местах в программе — проще менять и добавлять новые.

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

Отдельная таблица для каждого типа

Под эти требования вполне подходит обычная нормализованная схема из двух связанных таблиц: Employees и SalaryTypes (здесь и далее — синтаксис MS SQL):

create table [SalaryTypes]
(
    [Id] int not null identity primary key,
    [Code] varchar(120) not null unique,
    [Name] varchar(120) not null
)

create table [Employees]
(
    ...
    [SalaryType] int not null references [SalaryTypes]([Id])
)

где поле Employees.SalaryType ссылается на поле SalaryTypes.Id.

Одна таблица для всех типов

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

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

create table [Enumerations]
(
    [Id] int not null identity primary key,
    [Type] varchar(120) not null,
    [Code] varchar(120) not null unique,
    [Name] varchar(120) not null
)

Иерархический справочник перечислений

Можно бы так и оставить, но возникает желание также иметь описание типа перечисления, а для этого таблицу необходимо слегка переделать. Поле типа (Type) сделаем целочисленной ссылкой на таблицу, содержащую все типы перечислений. В качестве этой недостающей таблицы можно использовать саму таблицу перечислений (Enumerations) и получить в ней ссылку на саму себя. Для увеличения наглядности и удобства работы с полученной таблицей, можно еще сделать ее иерархической, заменив поле типа перечислений (Type) на поле родителя (Parent) каждой записи.

Таким образом, получаем окончательный вариант:

create table [Enumerations]
(
    [Id] int not null identity primary key,
    [Parent] int references [Enumerations]([Id]),
    [Code] varchar(120) not null unique,
    [Name] varchar(120) not null
)

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