Дизайн БД: 1-я нормальная форма и повторяющиеся группы

Дизайн 2 и Дизайн 4 — лучшие способы, если результаты не всегда будут присутствовать (также известные как NULL в Дизайне 1). Если они всегда берутся, то первый дизайн в порядке.

Я считаю, что повторяющиеся группы в SQL на самом деле будут, если у вас есть столбец, заполненный дополнительными значениями, например. Phone_Number содержит «123-444-4444,123-333-3334» и т. д.

В любом случае, более поздние проекты неоптимальны - вы продолжаете доводить это до финального уровня и имеете «Единую таблицу истинного поиска» http://www.dbazine.com/ofinterest/oi-articles/celko22 или значение атрибута объекта http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

В любом случае, это почти всегда плохо. Хотя они могут иметь общий тип данных/домен, значение различается, поэтому они должны оставаться отдельными атрибутами (maxtemp, mintemp и т. д.).

Я думаю (и меня учили) 1NF как «все строки должны быть одинаковой длины», а не как «без повторяющихся групп». С таким представлением вам будет немного легче принять решение из следующего:

В дизайне 1 ВСЕГДА присутствуют оба теста? Если это так, то на самом деле это не повторяющаяся группа. Все ли средние значения всегда присутствуют в схеме 2? Может ли быть больше (или меньше) в данной строке?

В схеме 4 всегда ли присутствуют оба эти значения? Если да, то все в порядке. Если нет, то следует использовать схему 5.

Вот правило повторяющихся групп — что функционально зависимо?

Если значение статистики функционально зависит от SN, теста и имени статистики, то у вас есть три ключевых элемента и один элемент значения. ( SN, Test, Statistic -> Value )

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

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

Для ваших примеров следуйте дизайну хранилища данных, чтобы найти более практичный тест:

Вы бы стали использовать другой ключ?

Думайте о своем статистическом факте как о точке, окруженной тремя измерениями: (SN, Test, Statistic). Это действительно? (Со сводными данными часто бывает неясно.)

Вместо этого давайте посмотрим на подробные данные, которые мы должны были сохранить: SN, Test, Score. Очевидно, что на пересечении этих двух измерений есть два измерения (SN, тест) и одна мера (оценка). Мы можем получить любое количество статистических данных из этих подробных данных, используя любое измерение (SN или Test).

В примере с батареей вы, вероятно, делаете создание базы данных EAV, а не более типичной реляционной базы данных. Ваши измерения (AvergaeCurrent и BatteryCapacity) дают вам веские основания для использования Entity-Attribute-Value дизайн базы данных.

Обратите внимание, что ВСЕ реляционные конструкции представляют собой противоречие между более длинными отношениями и тройками EAV. Вы всегда должны балансировать между «это ключ» и «это столбец», потому что вы всегда можете пометить все как ключ атрибута и использовать дизайн EAV.

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

Дизайн 1

SN     Test1_Max   Test1_Min    Test1_Mean  Test2_Max   Test2_Min    Test2_Mean
2093      23          2            15         54          -24           45  

Это лучшее с точки зрения производительности. Он не требует JOIN. Если количество полей является детерминированным, а не произвольным (например, у каждого человека не более двух тестовых баллов), то это лучше, хотя и более жестко, если вы решите связать с человеком более двух тестовых баллов. Поскольку SN равен unique для каждой строки, механизм базы данных может вернуться, как только найдет совпадение, что является еще одной причиной повышения производительности.

Дизайн 2

SN     Test      Max    Min    Mean     
2093    1        23     2      15       
2093    2        54     -24     45      

Это полезно, если SN 2093 может иметь N тестов в своем профиле. Точно так же, если количество тестов, скажем, 10 м, то этот дизайн также лучше, чем иметь 30 столбцов. Каждый запрос и сравнение будет довольно тяжелым. Это также полезно, если вашему приложению требуются запросы для получения наилучшего теста для учащегося 2093 или если вы хотите провести некоторую аналитику и составить отчет о результатах тестов. Это более гибкий, хотя и немного медленнее, чем предыдущий. Я предпочитаю это, потому что у меня есть подозрение, что вас, вероятно, заинтересует статистика тестов, а учащиеся могут сдавать более двух тестов каждый.

Дизайн 3

SN     Test    Statistic    Value
2093    1        Max          23
2093    1        Min          2
2093    1        Mean         15       
2093    2        Max          54
2093    2        Min         -24
2093    2        Mean         45  

Это полезно, если ваши запросы больше всего интересуются значениями. Например, если вас интересует, сколько значений больше 80, это будет быстро. В вашем сценарии это не имеет смысла. В конечном итоге вы сделаете слишком много самостоятельных JOIN. Чтение будет медленным! Однако запись, вероятно, будет быстрее, потому что вы можете быстро ОБНОВИТЬ максимальную оценку для SN 2093 и Test 2 (при условии, что столбец статистики является перечислением, а не строкой, поскольку сравнение строк может быть дорогостоящим).

Дизайн 4

SN      AverageCurrent (mA)    BatteryCapacity (mA)  
2093          200                    540  

Дизайн 5

SN      mA_Measuremnt       Value
2093    AverageCurrent      200 
2093    BatteryCapacity     540 

Применяются те же аргументы. Это действительно зависит от того, собираетесь ли вы оптимизировать для чтения или записи? Например, для веб-приложений, если вам это сойдет с рук, я предпочитаю Дизайн 1. Например, я обычно знаю, что у пользователя будет не более 3 телефонных номеров, поэтому я сделаю их каждым полем в столбце пользователя и избегайте СОЕДИНЕНИЙ. Чтение выполняется быстро, хотя для записи потребуется установить для некоторых полей значение null.

Когда вы уверены, что «тест» будет (когда-либо) иметь только Макс, Мин и Среднее -> используйте схему 2. Однако, если возможно, что в будущем появится новая «статистика», лучше использовать дизайн 3.

Ответ на:

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

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

Я предлагаю перемещать повторяющиеся группы только в отдельные таблицы, если они имеют переменную длину. Если у вас когда-нибудь будут только Телефон1, Телефон2 и Телефон3, нет необходимости их разделять. В другом случае, если количество повторов варьируется, лучшим вариантом будет отдельная таблица.

И ваша концепция точно такой же области и значения не очень интуитивна, потому что зависит от уровня абстракции. Phone1 не совсем то же самое, что Phone2, но оба они являются телефонными номерами. Вы также можете создать таблицу AddressDetails и переместить туда телефонные номера. А также имя, улица и город — все это адресные детали. Вы должны найти способ между общими парами ключ-значение и только выделенными столбцами.

Дизайн 1 фактически находится в 1NF, если у вас есть PK на CustID. Это может быть в 3NF, если никакие данные не зависят ни от чего, кроме ПК, например. Phone1 не повторяется для других CustID.

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

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

Первая нормальная форма — это другой вопрос: речь идет о том, как преобразовать модели данных из нереляционных форм баз данных (то, что сегодня мы могли бы назвать базами данных NoSQL) в реляционную форму.

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

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

Таким образом, дизайн с тремя телефонными номерами в виде трех отдельных столбцов не является нарушением 1NF. Конечно, немного странно ожидать, что у каждого клиента будет ровно три телефонных номера, но это всего лишь вопрос бизнес-правил, а не нормализации. Если у всех клиентов действительно есть ровно три телефонных номера, дизайн с тремя столбцами безопаснее и проще в управлении, чем дизайн с отдельной таблицей. Например, сложнее обеспечить ограничение точного числа деревьев для каждого клиента, если числа находятся в отдельной таблице.

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

Если количество телефонных номеров не ограничено, то, очевидно, необходимо было бы выделить его в отдельную таблицу. У вас не может быть неограниченного количества столбцов для телефонных номеров.

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

Во втором примере Дизайн 1 не нарушает 1NF, но дизайн 2 все же кажется намного более удобным в управлении. Тем более, что у вас (я предполагаю) будет более 2 тестовых прогонов в будущем.

Однако вариант 3 более проблематичен. Это пример антипаттерна EVA (Entity-Attribute-Value). В реляционной таблице тип и семантика поля должны быть заданы его столбцом. Но в этом дизайне тип и семантика значения задаются другим полем, статистикой. Различные статистические данные не будут иметь одинаковый тип значения (например, «количество» будет целым числом, «среднее» будет значением с плавающей запятой). Кроме того, представьте, что вам нужно написать запрос, который возвращает разницу между минимумом и максимумом для всех SN. Это было бы тривиально, если бы статистика была столбцом, но довольно сложно с таким дизайном.

Дизайн 5 имеет ту же проблему, что и дизайн 4. Значение имени столбца всегда является красным флажком, поскольку оно указывает, что столбец не имеет единственного значения.

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