Решение между хранением идентификаторов таблиц поиска или чистыми данными

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

CREATE TABLE ColorLookup (
  color VARCHAR(20) PRIMARY KEY
);

CREATE TABLE ItemsWithColors (
  ...other columns...,
  color VARCHAR(20),
  FOREIGN KEY (color) REFERENCES ColorLookup(color)
    ON UPDATE CASCADE ON DELETE SET NULL
);

Это решение имеет следующие преимущества:

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

Меня удивляет, что так много других людей в этой ветке, кажется, ошибочно представляют, что такое «нормализация». Использование суррогатных ключей (вездесущий id) не имеет ничего общего с нормализацией!

Ответ от @MacGruber:

Да, размер имеет значение. В InnoDB, например, каждый вторичный индекс хранит значение первичного ключа строки (строк), где встречается данное значение индекса. Таким образом, чем больше у вас вторичных индексов, тем больше накладных расходов на использование «объемного» типа данных для первичного ключа.

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

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

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

CREATE TABLE Hat (
  hat_id INT NOT NULL PRIMARY KEY,
  brand VARCHAR(255) NOT NULL,
  size INT NOT NULL,
  color VARCHAR(30) NOT NULL /* color is a string, like "Red", "Blue" */
)

Или можно еще нормализовать, составив "цветную" таблицу:

CREATE TABLE Color (
  color_id INT NOT NULL PRIMARY KEY,
  color_name VARCHAR(30) NOT NULL
)

CREATE TABLE Hat (
  hat_id INT NOT NULL PRIMARY KEY,
  brand VARCHAR(255) NOT NULL,
  size INT NOT NULL,
  color_id INT NOT NULL REFERENCES Color(color_id)
)

Конечным результатом последнего является то, что вы добавили некоторую сложность - вместо:

SELECT * FROM Hat

Теперь вы должны сказать:

SELECT * FROM Hat H INNER JOIN Color C ON H.color_id = C.color_id

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

• Есть ли другие атрибуты, которые "свисают" с этого атрибута? Вы фиксируете, скажем, и "название цвета", и "шестнадцатеричное значение", так что шестнадцатеричное значение всегда зависит от названия цвета? Если да, то вам определенно нужна отдельная таблица цветов, чтобы избежать ситуаций, когда в одной строке есть («Красный», «# FF0000»), а в другой («Красный», «# FF3333»). Множественные коррелированные атрибуты - это сигнал №1 о том, что объект должен быть нормализован.
• Будет ли набор возможных значений часто меняться? Использование нормализованной таблицы поиска упростит будущие изменения элементов набора, поскольку вы просто обновляете одну строку. Однако, если это происходит нечасто, не упирайтесь в утверждения, которые вместо этого должны обновлять множество строк в основной таблице; базы данных неплохо справляются с этим. Если вы не уверены, проведите тесты скорости.
• Будет ли набор возможных значений напрямую управляться пользователями? Т.е. есть ли экран, на котором они могут добавлять / удалять / изменять порядок элементов в списке? В таком случае, очевидно, необходим отдельный стол.
• Будет ли список различных значений влиять на какой-либо элемент пользовательского интерфейса? Например, такое "цветное" дроплист в пользовательском интерфейсе? Тогда вам будет лучше иметь его в отдельной таблице, а не выполнять SELECT DISTINCT в таблице каждый раз, когда вам нужно отобразить дроплист.

Если ни один из них не подходит, мне будет трудно найти другую (хорошую) причину для нормализации. Если вы просто хотите убедиться, что значение является одним из определенного (небольшого) набора допустимых значений, вам лучше использовать ОГРАНИЧЕНИЕ, в котором говорится, что значение должно быть в определенном списке; делает вещи простыми, и вы всегда можете "перейти" на отдельную таблицу позже, если возникнет необходимость.

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

rauhr.myopenid.com написал:

Мы решили решить эту проблему с помощью 4-й нормальной формы. ...

Это не 4-я нормальная форма. Это распространенная ошибка под названием One True Lookup: http://www.dbazine.com/ofinterest/oi-articles/celko22

Четвертая нормальная форма: http://en.wikipedia.org/wiki/Fourth_normal_form

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

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

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

Это позволяет оптимизировать представление и сделать ваш код устойчивым к изменениям в таблицах.

В oracle вы даже можете преобразовать представление в материализованное представление, если вам когда-нибудь понадобится.