Одна фиксированная таблица с несколькими столбцами против гибких абстрактных таблиц

Мне было интересно, если у вас есть веб-сайт с дюжиной различных типов списков (магазины, рестораны, клубы, отели, события), которые требуют различных полей, есть ли преимущество в создании таблицы с столбцами, определенными так
Пример магазина:

shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website | opening_hours

Или более абстрактный подход, похожий на этот:

object_id | name        
---------------
1         | Messy Joe's  
2         | Bate's Motel 

type_id | name
---------------
1       | hotel
2       | restaurant


object_id | type_id
---------------
1         | 2
2         | 1

field_id | name           | field_type
---------------
1        | address        | text
2        | opening_hours  | date 
3        | speciality     | text

type_id | field_id
---------------
1       | 1
1       | 2
2       | 1
2       | 3

object_id | field_id | value
1         | 1        | 1st street....
1         | 3        | English Cuisine

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

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

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

Источник

5 ответов

Решение

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

Предварительное разрешение

  1. Этикетки
    В профессии, которая требует точности, важно, чтобы мы использовали точные метки, чтобы избежать путаницы, и чтобы мы могли общаться без использования длинных описаний и классификаторов.
    ,
    То, что вы опубликовали как FixedTables, является ненормализованным. Справедливо, это может быть попытка создания формы в третьем нормальном состоянии, но на самом деле это плоский файл, ненормализованный (не "денормализованный"). То, что вы разместили как AbstractTables, это, если быть точным, Entity-Attribute-Value, который почти, но не совсем, Шестая Нормальная форма, и, следовательно, более Нормализована, чем 3NF. Если конечно это сделано правильно.

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

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

    • Нельзя сказать, что он денормализован "для производительности", потому что, будучи лидером по производительности, он является полной противоположностью производительности. Ну, они нуждаются в обосновании отсутствия формализованного дизайна], и это "для производительности". Даже самое маленькое формальное исследование выявило искажение (но очень немногие могут предоставить, так что оно остается скрытым до тех пор, пока они не получат стороннего решения, как вы уже догадались, огромную проблему производительности).

    • Нормализованные структуры работают намного лучше, чем ненормализованные структуры. Более нормализованные структуры (EAV/6NF) работают лучше, чем менее нормализованные структуры (3NF/5NF).

    • Я согласен с тягой OMG Ponies, но не с их ярлыками и определениями

    • Вместо того, чтобы говорить " не" денормализовать ", если вам не нужно", я говорю: "Нормализовать точно, точка" и "если есть проблемы с производительностью, вы не нормализовались правильно".
      ,

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

    • Определение 3NF является стабильным и не изменилось.
    • Существует много путаницы между NF и 3NF и 5NF. Правда в том, что это область, которая прогрессировала за последние 15 лет; и многие организации, академики, а также поставщики со своими продуктами с ограничениями, прыгнули, чтобы создать новую "Нормальную форму" для проверки своих предложений. Все служат коммерческим интересам и академически несостоятельны. 3NF в своем первоначальном нетронутом состоянии предполагал и гарантировал определенные атрибуты.
    • Итого, 5NF сегодня, это то, что 3NF было задумано 15 лет назад, и вы можете пропустить коммерческий подшучивание и двенадцать или около того "специальных" (коммерческих и псевдоакадемических) национальных федераций, некоторые из которых являются определены в вики, и даже это в запутанных терминах.
      ,

  3. Поскольку вы смогли понять и внедрить EAV в своем посте, у вас не возникнет проблем с пониманием следующего. Конечно, истинная реляционная модель обязательна, сильные клавиши и т. Д. Пятая нормальная форма - это, поскольку мы пропускаем четвертую:

    • Третья нормальная форма
      • что в простых однозначных терминах означает, что каждый неключевой столбец в каждой таблице имеет отношение 1::1 к первичному ключу таблицы,
      • и нет других неключевых столбцов
    • Нулевое дублирование данных (результат, если нормализация продвигается усердно; не достигается с помощью интеллекта или опыта, или работая для достижения цели без формального процесса)
    • нет аномалий обновления (когда вы обновляете столбец где-то, вам не нужно обновлять тот же столбец, расположенный где-то еще; столбец существует в одном и только одном месте).
      ,

  4. Шестая нормальная форма - это, конечно, пятая нормальная форма, плюс:

    • Устранение недостающих данных (столбцы). Это единственное верное решение проблемы с нулем (также называемое обработкой пропущенных значений), и в результате получается база данных без нулей. (Это может быть сделано в 5NF со стандартами и нулевыми заменителями, но это не оптимально.) Как вы интерпретируете и отображаете пропущенные значения, это другая история.
      ,
  5. EAV против шестой нормальной формы
    Все базы данных, которые я написал, кроме одной, являются чистыми 5NF. Я работал с (администрировал, исправлял, улучшал) парой баз данных EAV, и я реализовал одну настоящую базу данных 6NF. EAV - это свободная реализация 6NF, часто выполняемая людьми, которые плохо разбираются в нормализации и NF, но которые могут видеть ценность и нуждаются в гибкости EAV. Вы прекрасный пример. Разница заключается в следующем: поскольку он неэффективен и поскольку у разработчиков нет ссылки (6NF), которой он должен быть верен, они реализуют только то, что им нужно, и пишут все это в коде; в конечном итоге это противоречивая модель.
    ,
    Принимая во внимание, что чистая реализация 6NF имеет чисто академический ориентир, и поэтому она обычно более жесткая и последовательная. Обычно это проявляется в двух видимых элементах:
    • 6NF имеет каталог, содержащий метаданные, и все определяется метаданными, а не кодом. У EAV его нет, все в коде (разработчики отслеживают объекты и атрибуты). Очевидно, что каталог облегчает добавление столбцов, навигацию и позволяет формировать утилиты.
    • 6NF, если его понять, обеспечивает истинное решение нулевой проблемы. Реализаторы EAV, поскольку в них отсутствует контекст 6NF, обрабатывают недостающие данные в коде, непоследовательно или хуже, разрешают пустые значения в базе данных. Реализаторы 6NF запрещают Null и обрабатывают недостающие данные последовательно и элегантно, не требуя конструкций кода (для обработки Null; вам, конечно, все равно придется кодировать отсутствующие данные).
      ,
      Например. Для баз данных 6NF с каталогом у меня есть набор процедур, которые будут [повторно] генерировать SQL, необходимый для выполнения всех SELECT, и я предоставляю представления в 5NF для всех пользователей, поэтому им не нужно знать или понимать базовую структуру 6NF., Они изгнаны из каталога. Таким образом, изменения просты и автоматизированы. Типы EAV делают это вручную из-за отсутствия каталога.

Теперь мы можем начать

обсуждение

"Конечно, это может быть более абстрактно, если значения предопределены (пример: специальности могут иметь свой собственный список)"

Конечно. Но не становитесь слишком "абстрактными". Поддерживайте согласованность и реализуйте такие списки таким же образом EAV (или 6NF), как и другие списки.

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

  1. Объединения являются пешеходными в реляционных базах данных. Проблема не в базе данных, а в том, что SQL трудоемок при обработке соединений, особенно составных ключей.
  2. Базы данных EAV и 6NF имеют больше соединений, которые, как пешеходы, не больше, не меньше. Если вам приходится кодировать каждый SELECT вручную, конечно, громоздкий становится действительно громоздким.
  3. Всю проблему можно устранить, если (а) перейти с 6NF на EAV и (б) внедрить каталог, из которого вы можете (с) сгенерировать все основные SQL. Устраняет также целый класс ошибок.
  4. Это распространенный миф, что соединения как-то имеют цену. Совершенно неверно. Объединение реализуется во время компиляции, нет ничего существенного в том, чтобы "стоить" циклы ЦП. Проблема заключается в размере объединяемых таблиц, а не в стоимости объединения этих таблиц. Соединение двух таблиц с миллионами строк в каждой по правильному отношению PK⇢FK, каждая из которых имеет соответствующие индексы (уникальный со стороны родителя [FK]; уникальный со стороны ребенка), происходит мгновенно;; где дочерний индекс не уникален, но, по крайней мере, допустим ведущий столбец, он медленнее; где нет полезного индекса, конечно, это очень медленно. Ничто из этого не имеет отношения к стоимости присоединения. Если возвращено много строк, узким местом будет сеть и структура диска; не обработка соединения.
  5. Таким образом, вы можете делать все так, как вам угодно, без затрат, SQL может справиться с этим.

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

  1. 5NF (или 3NF для тех, кто не продвинулся в прогрессе) - это самое простое и лучшее с точки зрения внедрения, простоты использования (как разработчикам, так и пользователям) обслуживание. Недостатком является то, что каждый раз, когда вы добавляете столбец, вы должны изменить структуру базы данных (таблица DDL). Это хорошо в некоторых случаях, но не в большинстве случаев, из-за контроля над изменениями на месте, довольно обременительно. Во-вторых, вы должны изменить существующий код (код, обрабатывающий новый столбец, не учитывается, потому что это является обязательным): там, где применяются хорошие стандарты, это сводится к минимуму; там, где их нет, сфера непредсказуема.

  2. EAV (то, что вы опубликовали), позволяет добавлять столбцы без изменений DDL. Это единственная причина, по которой люди выбирают это. (код, обрабатывающий новый столбец, не считается, потому что это обязательно). Если все реализовано правильно, это не повлияет на существующий код; если нет, то будет. Но вам нужны разработчики с поддержкой EAV. Когда EAV реализован плохо, он отвратителен, хуже, чем 5NF, плохо, но не хуже, чем Unnormalised, что и есть в большинстве баз данных (искажается как "Денормализовано для производительности"). конечно, даже более важно (чем в 5NF/3NF) поддерживать сильный контекст транзакции, потому что столбцы гораздо более распределены. Точно так же важно сохранить Декларативную ссылочную целостность: я видел беспорядки, которые во многом были связаны с удалением DRI разработчиками, потому что его стало "слишком сложно поддерживать", в результате, как вы можете себе представить, была одна мать данных куча с дубликатами 3NF / 5NF строк и столбцов повсюду. И непоследовательная обработка Null.

  3. Нет никакой разницы в производительности, если предположить, что сервер был разумно настроен для использования по назначению. (Хорошо, есть определенные оптимизации, которые возможны только в 6NF, которые невозможны в других NF, но я думаю, что это выходит за рамки этого потока.) И снова, EAV, выполненный плохо, может вызвать ненужные узкие места, не более, чем Unnormalised.

  4. Конечно, если вы используете EAV, я рекомендую больше формальностей; купить полную цену; перейти с 6NF; внедрить каталог; утилиты для производства SQL; Просмотры; последовательно обрабатывать недостающие данные; полностью исключить Null. Это снижает вашу уязвимость к качеству ваших разработчиков; они могут забыть об эзотерических выпусках EAV / 6NF, использовать представления и сосредоточиться на логике приложения.

Простите за длинный пост.

Источник

В своем вопросе вы представили как минимум две основные проблемы одновременно. Эти две проблемы - EAV и gen-spec.

Сначала поговорим о EAV. Ваша последняя таблица (object_id, field_id, value) по сути является EAV. У EAV есть свои плюсы и минусы. Положительным моментом является то, что структура является настолько общей, что она может вместить практически любой массив данных, описывающих практически любую тему. Это означает, что вы можете приступить к разработке и внедрению без анализа данных и понимания предмета, а также не беспокоиться о неправильных предположениях. Недостатком является то, что во время поиска вы должны выполнить анализ данных, который вы пропустили, прежде чем создавать базу данных, чтобы получить запросы, которые что-нибудь значат. Это гораздо серьезнее, чем просто эффективность поиска. Но у вас также будут ужасные проблемы с эффективностью поиска. Есть только два способа узнать об этой ловушке: пережить или прочитать об этом от тех, кто имеет. Я рекомендую к прочтению.

Во-вторых, у вас есть случай с ген-спецификацией. Ваша таблица (object_id, type_id) содержит шаблон gen-spec (generalization-specialization) вместе со связанными таблицами. Если бы мне пришлось обобщать отели и рестораны, я мог бы назвать это чем-то вроде "общественных помещений" или "мест встречи". Но я не уверен, что понимаю ваш случай, и вы, возможно, стремитесь к чему-то более общему, чем те, что предполагают эти два имени. В конце концов, вы включили "события" в свой список, и событие, на мой взгляд, не является местом встречи.

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

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

Источник

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

{
   "id": 1,
   "type":"Restaurant",
   "name":"Messy Joe",
   "address":"1 Main St.",
   "tags":["asian","fusion","casual"]
}
Источник

"Абстрактный" подход более известен как "нормализация", выглядит как 3-я нормальная форма (3NF).

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

Источник

Как у вас есть списки, представленные в коде? Я бы догадался Listing как супертип, с Shop, Restuarantи т.д. как подтипы?

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

  • Вариант 1: одна таблица для каждого подтипа, с общими атрибутами, повторяемыми в каждой таблице (имя, идентификатор и т. Д.).
  • Вариант 2: одна таблица для всех объектов (ваш подход к одной таблице)
  • Вариант 3: таблица для супертипа и одна для каждого подтипа

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

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

Вариант 1 на первый взгляд кажется наиболее эффективным, хотя есть два предостережения: (1) Изменение не является устойчивым. Если вы добавите новый подтип (и другие атрибуты), вам нужно изменить структуру таблицы и перенести ее. (2) Это может быть менее эффективно, чем кажется. Поскольку таблица заполнена редко, некоторые БД не хранят ее особенно эффективно. Как следствие, он может быть менее эффективным, чем вариант 1 - поскольку механизм запросов может выполнять объединения быстрее, чем он может искать раздутые разреженные табличные пространства.

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

НТН

Источник
Другие вопросы по тегам