Хранение списка с разделителями в столбце базы данных действительно так плохо?
представьте себе веб-форму с набором флажков (любой или все из них могут быть выбраны). Я решил сохранить их в разделенный запятыми список значений, хранящихся в столбце таблицы базы данных.
теперь я знаю, что правильным решением было бы создать вторую таблицу и правильно нормализовать базу данных. Это было быстрее реализовать простое решение, и я хотел иметь доказательство концепции этого приложения быстро и без необходимости тратить слишком много времени на оно.
Я думал, что сэкономленное время и более простой код стоили того в моей ситуации, это оправданный выбор дизайна, или я должен был нормализовать его с самого начала?
еще один контекст, это небольшое внутреннее приложение, которое по существу заменяет файл Excel, который был сохранен в общей папке. Я также спрашиваю, потому что я думаю об очистке программы и сделать ее более ремонтопригодной. Есть некоторые вещи, которыми я не совсем доволен, один из них тема этого вопроса.
9 ответов:
в дополнение к нарушению Первая Нормальная Форма из-за повторяющейся группы значений, хранящихся в одном столбце, разделенные запятыми списки имеют много других более практических проблем:
- не может гарантировать, что каждое значение является правильным типом данных: нет способа предотвратить 1,2,3,банан,5
- невозможно использовать ограничения внешнего ключа для связи значений с таблицей подстановки; нет способа обеспечить ссылочную целостность.
- не может быть применена УНИКАЛЬНОСТЬ: Нет способа предотвратить 1,2,3,3,3,5
- невозможно удалить значение из списка без извлечения всего списка.
- не может хранить список дольше, чем то, что вписывается в строку столбца.
- трудно найти все объекты с заданным значением в списке; вы должны использовать неэффективное сканирование таблицы. Возможно, придется прибегнуть к регулярным выражениям, например в MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*- трудно подсчитать элементы в списке, или сделать другие агрегированные запросы.
- трудно присоединить значения к таблице поиска, на которую они ссылаются.
- трудно получить список в отсортированном порядке.
- хранение целых чисел в виде строк занимает примерно в два раза больше места, чем хранение двоичных чисел. Не говоря уже о пробелах, занимаемых символами запятой.
чтобы решить эти проблемы, вы должны написать тонны кода приложения, изобретая функциональность, что СУБД уже предоставляет гораздо больше эффективно.
разделенные запятыми списки достаточно неправильны, чтобы я сделал это первой главой в своей книге:антипаттерны SQL: избегая ловушек программирования баз данных.
бывают случаи, когда вам нужно использовать денормализацию, но как @OMG пони упоминает, это исключительные случаи. Любая нереляционная "оптимизация" приносит пользу одному типу запроса за счет другого использования данных, поэтому убедитесь, что вы знаете, какой из ваших запросов к ним нужно относиться так специально, чтобы они заслуживали денормализации.
* MySQL 8.0 больше не поддерживает этот синтаксис выражения границы слова.
есть много вопросов на так спрашивая:
- как получить количество конкретных значений из списка через запятую
- как получить записи, которые имеют только то же самое 2/3 / etc конкретное значение из этого списка через запятую
еще одна проблема с разделенным запятыми списком заключается в обеспечении согласованности значений - сохранение текста означает возможность опечаток...
Это все симптомы денормализованных данных, и выделите почему вы всегда должны моделировать нормализованные данные. Денормализация можете оптимизация запросов,для применения, когда необходимость действительно представляет себя.
"одной из причин была лень".
Это вызывает тревогу. Единственная причина, по которой вы должны делать что-то подобное, заключается в том, что вы знаете, как это сделать "правильно", но вы пришли к выводу, что есть ощутимая причина не делать этого таким образом.
сказав это: если данные, которые вы выбираете для хранения таким образом, это данные, которые вам никогда не нужно будет запрашивать, то может быть случай для хранения его так, как вы выбрали.
(некоторые пользователи оспорите утверждение в моем предыдущем абзаце, сказав, что"вы никогда не можете знать, какие требования будут добавлены в будущем". Эти пользователи либо заблуждаются, либо заявляют о религиозных убеждениях. Иногда бывает выгодно работать с теми требованиями, которые у вас есть перед вами.)
В общем, все может быть оправдано, если оно соответствует требованиям вашего проекта. Это не означает, что люди согласятся или захотят защищать ваше решение...
Как правило, хранение данных таким образом является неоптимальным (например, сложнее выполнять эффективные запросы) и может вызвать проблемы с обслуживанием, если вы измените элементы в своей форме. Возможно, вы могли бы найти золотую середину и использовать целое число, а не набор битовых флагов?
Да, я бы сказал, что это действительно плохо. Это оправданный выбор, но это не делает его правильным или хорошим.
он ломает первую нормальную форму.
вторая критика заключается в том, что ввод необработанных входных результатов непосредственно в базу данных, без какой-либо проверки или привязки вообще, оставляет вас открытыми для атак SQL-инъекций.
то, что вы называете ленью и отсутствием знаний SQL, - это то, из чего сделаны неофиты. Я бы рекомендовал взять время, чтобы делайте это правильно и рассматривайте это как возможность учиться.
или оставить все как есть и выучить болезненный урок атаки SQL-инъекции.
Мне нужен столбец с несколькими значениями, он может быть реализован как поле xml
Он может быть преобразован в запятую по мере необходимости
запрос XML-списка в sql server с помощью 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: хранение целых чисел as strings занимает примерно в два раза больше места, чем хранение двоичных целых чисел.
С XML: хранение еще хуже, чем csv
С CSV: плюс много запятых.
С XML: теги используются вместо запятых
короче говоря, использование XML позволяет обойти некоторые проблемы с разделенным списком и может быть преобразовано в разделенный список по мере необходимости
да и что плохо. Я считаю, что если вам не нравится использовать реляционные базы данных, то ищите альтернативу, которая подходит вам лучше, есть много интересных проектов "NOSQL" с некоторыми действительно расширенными функциями.
Ну, я использую разделенный список пар ключ/значение в столбце NTEXT в SQL Server уже более 4 лет, и он работает. Вы теряете гибкость в создании запросов, но с другой стороны, если у вас есть библиотека, которая сохраняет/derpersists пару ключевых значений, то это не такая уж плохая идея.
Я бы, вероятно, взял золотую середину: сделать каждое поле в CSV в отдельный столбец в базе данных, но не беспокоиться о нормализации (по крайней мере, на данный момент). В какой-то момент нормализация может стать интересным, но со всеми данными, запихнутыми в один столбец, вы практически не получаете никакой выгоды от использования базы данных вообще. Вам нужно разделить данные на логические поля/столбцы/все, что вы хотите назвать их, прежде чем вы сможете манипулировать ими осмысленно все.