SQL: Преимущества ENUM против отношения "один ко многим"?

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

CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);

Но то же самое можно также показать с помощью пользовательского типа /ENUM:

CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);

(Пример показан с использованием PostgreSQL, но другие РСУБД имеют аналогичный синтаксис)

Самый большой недостаток, который я вижу в использовании ENUM, заключается в том, что его сложнее обновлять из приложения. И это может также запутать неопытного разработчика, который использовал SQL DB просто как бит-ведро.

Предполагая, что информация в основном статична (названия дней недели, имена месяцев, ряды армии США и т.д.), есть ли какое-либо преимущество в использовании ENUM?

Ответ 1

Недостатком использования чего-то вроде ENUM является то, что вы не можете получить список всех доступных значений, если они не существуют в вашей таблице данных, если вы не скопируете список доступных значений где-нибудь. Например, если в вашей таблице OFFICERS у вас нет MG на почте, нет способа узнать, что ранг существует. Таким образом, когда BG Blowhard освобождается от MG Marjorie-Banks, у вас не будет возможности войти в новый офицерский ранг, что является позором, поскольку он является самой моделью современного генерал-майора.:-) А что происходит, когда появляется генерал армии (пятизвездочный генерал)?

Для простых типов, которые не будут меняться, я успешно использовал домены. Например, в одной из моих баз данных у меня есть yes_no_domain, определяемый следующим образом:

CREATE DOMAIN yes_no_dom
  AS character(1)
  DEFAULT 'N'::bpchar
  NOT NULL
   CONSTRAINT yes_no_dom_check
     CHECK ((VALUE = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])));

Поделитесь и наслаждайтесь.

Ответ 2

Пример, показанный с использованием PostgreSQL, но другие РСУБД имеют аналогичный синтаксис

Это неверно. Это не требование стандарта ISO/IEC/ANSI SQL, поэтому коммерческие базы данных не предоставляют его (вы должны предоставить таблицы Lookup). Небольшой конец города реализует различные "дополнительные услуги", но не реализует более строгие требования или ворчание большого города.

У нас нет ENUM в составе DataType, что абсурдно.

Первый недостаток ENUM заключается в том, что он нестандартен и поэтому не переносится.

Второй большой недостаток ENUM заключается в том, что база данных закрыта. Сотни инструментов отчетов, которые можно использовать в базе данных (независимо от приложения), не могут найти их и поэтому не могут проецировать имена/значения. Если у вас нормальная таблица стандартного SQL Lookup, эта проблема устранена.

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

Наконец, вы не можете легко получить список содержимого ENUM; вы можете с помощью таблицы поиска. Более того, у вас есть вектор для выполнения любых запросов Dimension-Fact, устраняя необходимость выбора из большой таблицы фактов и GROUP BY.

Ответ 3

Я не вижу никакого преимущества в использовании ENUMS.

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

Ответ 4

Небольшое преимущество может заключаться в том, что у вас есть своего рода UDT при создании ENUM. Пользовательский тип может быть повторно использован во многих других объектах базы данных, например. в представлениях, других таблицах, других типах, хранимых процедурах (в других СУБД) и т.д.

Другим преимуществом является документирование допустимых значений поля. Примеры:

Поле yes/no
Поле мужчины/женщины
Поле mr/mrs/ms/dr

Наверное, дело вкуса. Я предпочитаю ENUM для этих типов полей, а не внешних ключей для поиска таблиц для таких простых понятий.

Еще одно преимущество может заключаться в том, что при использовании генерации кода или ORM, таких как jOOQ в Java, вы может использовать этот ENUM для создания из него класса enum Java, вместо того, чтобы присоединяться к таблице поиска или работать с литеральным идентификатором ENUM

Однако факт, что только несколько СУБД поддерживают формальный тип ENUM. Я знаю только о Postgres и MySQL. Oracle или DB2 не имеют этого.

Ответ 5

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

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

Ответ 6

Преимущества:

Тип безопасности для хранимых процедур: повысит ошибку типа, если аргумент не может быть принудительно введен в тип. Например: select court_martial('3LT') автоматически вызывает ошибку типа.
Пользовательский коалиционный заказ: в вашем примере офицеры могут быть отсортированы без идентификатора рейтинга.

Ответ 7

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

В базе данных такие перечисления обычно представляют собой текстовые или целочисленные поля без ограничений. Перечисления базы данных не будут транслироваться в Java/С#/и т.д. enums, поэтому разработчики не видят в этом никакого выигрыша.

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