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

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

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

Точки, о которых следует помнить:

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

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

Ответ 1

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

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

Ответ 2

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

Если вам пришлось денормализовать, всегда сохраняйте целостность данных с помощью ограничений и триггеров.

Ответ 3

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

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" */
)

Или вы можете нормализовать его, создав таблицу "color":

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 сигналом, что объект должен быть нормализован.
Будет ли часто меняться набор возможных значений? Использование нормализованной таблицы поиска облегчит будущие изменения элементов набора, поскольку вы просто обновляете одну строку. Однако, если это редко, не делайте ошибок в операторах, которые вместо этого должны обновлять множество строк в основной таблице; базы данных довольно хороши в этом. Выполняйте некоторые тесты скорости, если вы не уверены.
Будет ли набор возможных значений непосредственно администрироваться пользователями? I.e. есть ли экран, где они могут добавлять/удалять/изменять порядок элементов в списке? Если это так, отдельная таблица является обязательной, очевидно.
Будет ли список различных значений влиять на некоторый элемент пользовательского интерфейса?. является "цветным" дротиком в пользовательском интерфейсе? Тогда вам будет лучше иметь его в своей таблице, вместо того, чтобы делать SELECT DISTINCT в таблице каждый раз, когда вам нужно показать droplist.

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

Ответ 4

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

Ответ 5

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

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

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

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

Ответ 6

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

Ответ 7

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

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

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

Ответ 8

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