Является ли хранение разделенного списка в столбце базы данных действительно так плохо?

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

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

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

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

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

  • Невозможно гарантировать, что каждое значение является правильным типом данных: нет возможности предотвратить 1,2,3, банан, 5
  • Нельзя использовать ограничения внешнего ключа для привязки значений к таблице поиска; нет возможности обеспечить ссылочную целостность.
  • Невозможно обеспечить уникальность: нет возможности предотвратить 1,2,3,3,3,5
  • Не удается удалить значение из списка, не выбирая весь список.
  • Не удается сохранить список дольше, чем в столбце строки.
  • Трудно искать все объекты с заданным значением в списке; вы должны использовать неэффективное сканирование таблицы. Возможно, придется прибегать к регулярным выражениям, например в MySQL:
    idlist REGEXP '[[:<:]]2[[:>:]]' *
  • Трудно подсчитать элементы в списке или выполнить другие агрегированные запросы.
  • Трудно присоединить значения к справочной таблице, к которой они ссылаются.
  • Трудно получить список в отсортированном порядке.
  • Сохранение целых чисел в виде строк занимает в два раза больше места, чем сохранение двоичных целых чисел. Не говоря уже о пространстве, занимаемом запятыми.

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

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

Бывают случаи, когда вам нужно использовать денормализацию, но, как упоминает @OMG Ponies , это исключения. Любая нереляционная «оптимизация» выгодна для одного типа запросов за счет других видов использования данных, поэтому убедитесь, что знаете, какие из ваших запросов нужно обрабатывать так специально, что они заслуживают денормализации.


* MySQL 8.0 больше не поддерживает этот синтаксис выражения слова-границы.

Есть много вопросов о том, как SO спрашивает:

  • как получить счет конкретных значений из списка, разделенного запятыми
  • как получить записи, которые имеют только одно и то же значение 2/3 / etc из этого списка, разделенного запятыми

Еще одна проблема с разделенным запятой списком – обеспечить согласованность значений – сохранение текста означает возможность опечаток …

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

«Одной из причин была лень».

Это звонит колокольчикам. Единственная причина, по которой вы должны сделать что-то подобное, – это то, что вы знаете, как это сделать «правильно», но вы пришли к выводу, что есть осязаемая причина не делать этого таким образом.

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

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

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

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

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

Он ломает первую нормальную форму.

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

То, что вы называете лень и отсутствие знаний SQL, – это то, из чего сделаны неофиты. Я бы рекомендовал потратить время, чтобы сделать это правильно, и рассмотреть его как возможность учиться.

Или оставьте это как есть и узнайте болезненный урок атаки SQL-инъекции.

Мне нужен столбец с несколькими значениями, он может быть реализован как поле xml

Он может быть преобразован в запятую, если необходимо

запрос XML-списка на сервере sql с использованием Xquery .

Будучи полем xml, некоторые проблемы могут быть решены.

С CSV: не может гарантировать, что каждое значение является правильным типом данных: нет возможности предотвратить 1,2,3, банан, 5

С XML: значения в теге могут быть принудительно корректными


С CSV: нельзя использовать ограничения внешнего ключа для привязки значений к таблице поиска; нет возможности обеспечить ссылочную целостность.

С XML: все еще проблема


С CSV: не может обеспечить уникальность: нет возможности предотвратить 1,2,3,3,3,5

С XML: все еще проблема


С CSV: невозможно удалить значение из списка без получения всего списка.

С XML: отдельные элементы могут быть удалены


С CSV: трудно найти все объекты с заданным значением в списке; вы должны использовать неэффективное сканирование таблицы.

С XML: поле xml можно индексировать


С CSV: трудно подсчитать элементы в списке или выполнить другие агрегированные запросы. **

С XML: не особо сложно


С CSV: трудно присоединить значения к справочной таблице, на которую они ссылаются. **

С XML: не особо сложно


С CSV: трудно получить список в отсортированном порядке.

С XML: не особо сложно


С CSV: сохранение целых чисел в виде строк занимает в два раза больше места, чем сохранение двоичных целых чисел.

С XML: память еще хуже, чем csv


С CSV: Плюс много запятых.

Вместо XML- тегов вместо запятых


Короче говоря, использование XML обходит некоторые проблемы с ограниченным списком И может быть преобразовано в список с разделителями по мере необходимости

Да, это так плохо. Я считаю, что если вам не нравится использование реляционных баз данных, ищите альтернативу, которая вам подходит, есть много интересных проектов «NOSQL» с некоторыми действительно расширенными функциями.

Ну, я уже более 4 лет использую список разделов вкладки с ключом / стоимостью в столбце NTEXT в SQL Server и работает. Вы теряете гибкость при создании запросов, но, с другой стороны, если у вас есть библиотека, которая сохраняет / держирует пару ключевых значений, то это не такая плохая идея.

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

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