Можно использовать справочную таблицу с первичным ключом 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
);
Это решение имеет следующие преимущества:
удивительно мне, что у такого количества других людей на этом потоке, кажется, есть ошибочные идеи того, какова "нормализация". Используя суррогатные ключи (повсеместный "идентификатор") не имеет никакого отношения к нормализации!
<час>комментарий Ре от @MacGruber:
Да, размер является фактором. В InnoDB, например, каждый вторичный индекс хранит значение первичного ключа строки (строк), где данное индексное значение происходит. Так, чем более вторичные индексы Вы имеете, тем больше издержки для использования "большого" типа данных для первичного ключа.
Также это влияет на внешние ключи; столбец внешнего ключа должен быть совпадающим типом данных как первичным ключом, на который он ссылается. У Вас могла бы быть маленькая справочная таблица, таким образом, Вы думаете, что размер первичного ключа в таблице с 50 строками не имеет значения. Но на ту справочную таблицу могли бы сослаться миллионы или миллиарды из строк в других таблицах!
нет никакого правильного ответа для всех случаев. Любой ответ может быть корректным для различных случаев. Вы просто узнаете о компромиссах и пытаетесь сделать обоснованное решение на индивидуальной основе.
Одна вещь, которую никто не рассмотрел, состоит в том, что Вы не соединили бы со справочной таблицей, если данные в ней могут изменяться со временем, и записи, к которым присоединяются к, являются хронологическими. Примером является таблица деталей и таблица порядка. Поставщики могут отбросить части или числа сменной детали, но таблица заказов должна alawys иметь точно, что было заказано в то время, когда это было заказано. Поэтому это должно поиск данные, чтобы сделать рекордную вставку, но никогда не должно соединять со справочной таблицей для получения информации о существующем порядке. Вместо этого номер детали и описание и цена, и т.д. должен быть сохранен в таблице заказов. Это espceially очень важно так, чтобы изменения цен не распространяли через исторические данные и делали Вашу финансовую документацию неточной. В этом случае Вы также хотели бы избегать использования любого вида каскадного обновления также.
В случаях простых атомарных значений я склонен не соглашаться с общими знаниями на этом, главным образом на передней стороне сложности. Рассмотрите таблицу, содержащую шляпы. Можно сделать "денормализованный" путь:
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
то, что дополнительное соединение огромное соглашение? Нет - на самом деле, это - основа реляционной модели дизайна - нормализация позволяет Вам предотвращать возможные несоответствия в данных. Но каждая такая ситуация добавляет немного из сложности, и если нет серьезное основание, стоит спросить, почему Вы делаете его. Я рассматриваю возможные "серьезные основания" включать:
, Если бы ни один из тех не применяется, мне было бы трудно находить другую (хорошую) причину нормализовать. Если Вы просто хотите удостовериться, что значение является одним из определенного (маленького) множества легальных значений, Вы - более обеспеченное использование ОГРАНИЧЕНИЯ, которое говорит, что значение должно быть в определенном списке; сохраняет вещи простыми, и можно всегда "обновить" до отдельной таблицы позже, если потребность возникает.
rauhr.myopenid.com записал :
способ, которым мы решили решить эту проблему, с 4-й нормальной формой....
, Который не является 4-й нормальной формой. Это - частая ошибка под названием Один Истинный Поиск: http://www.dbazine.com/ofinterest/oi-articles/celko22
4-я нормальная форма: http://en.wikipedia.org/wiki/Fourth_normal_form
Нормализация довольно универсально рассматривается как часть лучших практик в базах данных, и нормализация говорит да, Вы выставляете данные и обращаетесь к ним ключом.
Можно даже сделать это, правило к всегда программе против представлений, имея представление получает поиски.
Это позволяет оптимизировать представление и сделать Ваш код стойким к изменениям в таблицах.
В оракуле, Вы могли даже преобразовать представление в осуществленное представление, если Вы когда-нибудь должны.
Так как никто больше не обратился к Вашей второй точке: Когда запросы станут долгими и трудными читать и записать из-за всех тех соединений, представление будет обычно разрешать это.