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

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

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

Что нужно иметь в виду:

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

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

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

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

 philipxy11 июл. 2019 г., 00:54
Это не нормализация. (Но есть распространенное заблуждение, что это так.)
 cletus20 дек. 2008 г., 09:32
Я не очень понимаю вопрос. Возможно, вы могли бы перефразировать или уточнить?

Ответы на вопрос(7)

писал:

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

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

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

 philipxy11 июл. 2019 г., 00:53
Замена значений другими значениями, которые являются идентификаторами, не является нормализацией.
 James Skemp08 июл. 2013 г., 21:29
Джо Селко статьи больше нет. Путь машины предполагает, чтоgroups.google.com/d/msg/microsoft.public.sqlserver.programming/... есть текст этого, но не совсем так красиво.

дростью в этом вопросе, главным образом в отношении сложности. Рассмотрим таблицу с шляпами. Вы можете сделать «денормализованный» способ:

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

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

 derobert21 дек. 2008 г., 05:20
хммм, в этом случае вы могли бы иметь hat.color иметь внешний ключ на color.color. Нормализация! = Создание поддельных ключей. Кроме того, вы в основном сказали, что нет необходимости перемещать цвет в свою таблицу, если она не имеет смысла; это просто произвольная строка .... ну да. Это не отношение тогда.
 Ian Varley22 дек. 2008 г., 00:23
@derobert - Согласен, это не отношение, если это просто произвольные строки. Я решил, что это основной вопрос его вопроса: когда вы устанавливаете отношения, а когда нет?

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

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

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

 Mehrdad Afshari20 дек. 2008 г., 16:55
Материализованные представления не являются специфичными для Oracle. SQL Server поддерживает индексированные представления, которые в основном одно и то же, хотя синтаксис другой.

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

Решение Вопроса

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

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

 Bill Karwin08 февр. 2017 г., 22:16
@MacGruber, см. Мое редактирование выше.
 WAQ29 авг. 2018 г., 15:29
@BillKarwin это правда, и это работает. Но большинство БД не позволяют (или не поощряют) изменять значения первичного ключа, скажем, я использую Entity Framework, который не позволит вам изменить первичный ключ таблицы, если я не использую хранимую процедуру и не обновляю ее.
 MobileMon02 окт. 2015 г., 01:08
Если бы не было дополнительной таблицы для цветов, какой уровень нормальной формы она бы сломала? (3-й, 4-й и т. Д.)
 Bill Karwin29 авг. 2018 г., 15:26
@WAQ Чаще встречается ВСТАВЛЕНИЕ нового значения в таблицу поиска. Но если вам нужно ОБНОВИТЬ значение, вы можете использовать каскадное ограничение внешнего ключа для автоматического обновления зависимых строк. Пример, который я привел выше, показывает синтаксисON UPDATE CASCADE, Попробуйте!
 MacGruber08 февр. 2017 г., 21:53
Влияет ли размер столбца поиска на решение об использовании столбца идентификатора или нет? Если у вас есть varchar (64) или varchar (256) против столбца с целочисленным идентификатором, разве это не займет гораздо больше места? Я полагаю, если пространство не является проблемой или разница тривиальна, тогда пропуск столбца идентификаторов не представляет никакой сложности. Просто для записи, мне нравится идея не использовать столбец ID. Я создал таблицу подстановок, и никто не догадывался о том решении, которое привело меня к этой теме. Рад, что моя интуиция подтвердилась!
 Bill Karwin05 окт. 2015 г., 20:05
@MobileMon, для этого потребуется, чтобы таблица ItemsWith Colors выше имела хотя бы одну нетривиальную зависимость соединения. Это будет означать, что помимоcolorесть еще один столбец, который не является частью ключа-кандидата. В этом примере в качестве заполнителя указываются только «... другие столбцы ...», которые могут быть только столбцами ключа-кандидата. Таким образом, недостаточно информации, чтобы судить, находится ли таблица в 6NF.
 Bill Karwin02 окт. 2015 г., 04:16
@MobileMon, вы неправильно поняли цель нормализации. Дело не в том, чтобы делать больше столов. Обычные формы предназначены для представления данных без избыточности. Таблица поиска цветов в моем примере не имеет ничего общего с обычными формами таблицы ItemsWithColors. Речь идет о создании ограничения, чтобы varchar ограничивался конечным списком определенных строк (имен цветов). Это помогает определить доменcolor колонка, но это не очень помогает или препятствует цели устранения избыточности.
 WAQ29 авг. 2018 г., 15:00
@BillKarwin, если вам когда-нибудь придется изменить строку в таблице поиска, как бы вы это сделали? учитывая тот факт, что это будет первичный ключ в таблице.
 Bill Karwin29 авг. 2018 г., 16:54
@WAQ Жаль, потому что именно по этой причине существуют каскадные ограничения внешнего ключа. Это нормальная операция в SQL. Но каскадные обновления должны быть атомарными, поэтому они должны выполняться внутри СУБД, а не быть «смоделированы» фреймворком. Вот почему разработчики фреймворка считают, что это плохая практика. В этом случае вы не можете использовать решение, которое я показываю выше в EF.
 MobileMon05 окт. 2015 г., 18:08
Я считаю, что это нарушает 6-ую нормальную формуen.wikipedia.org/wiki/Sixth_normal_form

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

 philipxy11 июл. 2019 г., 00:54
Замена значений другими значениями, которые являются идентификаторами, не является нормализацией.
 chaos22 дек. 2008 г., 17:38
Я не согласен. ОП задает довольно общий вопрос, поэтому я склонен говорить «нормализовать», пока не увижу, что нормализация - это слишком много для ситуации.
 Lasse Vågsæther Karlsen20 дек. 2008 г., 22:26
Только пока это больше не имеет смысла. Норма также заключается в денормализации при снижении производительности из-за слишком нормализованных данных. Иногда логическая структура слишком медленная.

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

 derobert21 дек. 2008 г., 05:15
Представления довольно дешевы в большинстве баз данных, поскольку они обычно функционируют при переписывании запросов. Конечно, проверьте вывод «объяснения» (или аналогичного) вашей БД.
 Jeremiah Peschka20 дек. 2008 г., 23:44
Я всегда предупреждаю об использовании представлений - они сохраняют читабельность, но могут иметь значительные затраты производительности.

Ваш ответ на вопрос