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

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

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

Точки для учета:

  • Со вторым методом необходимо было бы сделать массовые обновления всех записей, ссылающихся на данные, если это изменяется в справочной таблице.

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

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

Существует ли лучшая практика здесь или какие-либо ключевые пункты для рассмотрения?

14
задан philipxy 10 July 2019 в 22:51
поделиться

7 ответов

Можно использовать справочную таблицу с первичным ключом 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 строками не имеет значения. Но на ту справочную таблицу могли бы сослаться миллионы или миллиарды из строк в других таблицах!

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

28
ответ дан 1 December 2019 в 06:54
поделиться

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

3
ответ дан 1 December 2019 в 06:54
поделиться

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

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, что объект должен быть нормализован.
  • Будет набор возможных значений часто изменяться? Используя нормализованную справочную таблицу будет делать будущие изменения в элементы набора легче, потому что Вы просто обновляете одну строку. Если это является нечастым, тем не менее, не передумали относительно операторов, которые должны обновить много строк в основной таблице вместо этого; базы данных довольно хороши в этом. Сделайте некоторые тесты скорости, если Вы не уверены.
  • Будет множество возможных значений быть непосредственно администрируемым пользователями? Т.е. там экран, где они могут добавить / удаляют / переупорядочивают элементы в списке? Если так, отдельная таблица - необходимость, очевидно.
  • Будет список отличного питания значений некоторый элемент UI? , Например, "действительно ли цвет" droplist в UI? Затем Вы будете более обеспеченным наличием его в его собственной таблице, вместо того, чтобы делать ВЫБОР, ОТЛИЧНЫЙ на таблице каждый раз, когда необходимо показать droplist.

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

5
ответ дан 1 December 2019 в 06:54
поделиться

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

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

, Который не является 4-й нормальной формой. Это - частая ошибка под названием Один Истинный Поиск: http://www.dbazine.com/ofinterest/oi-articles/celko22

4-я нормальная форма: http://en.wikipedia.org/wiki/Fourth_normal_form

2
ответ дан 1 December 2019 в 06:54
поделиться

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

1
ответ дан 1 December 2019 в 06:54
поделиться

Можно даже сделать это, правило к всегда программе против представлений, имея представление получает поиски.

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

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

1
ответ дан 1 December 2019 в 06:54
поделиться

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

1
ответ дан 1 December 2019 в 06:54
поделиться
Другие вопросы по тегам:

Похожие вопросы: