Действительно ли плохо хранить список с разделителями в столбце базы данных?
Представьте себе веб-форму с набором флажков (можно выбрать любой или все из них). Я решил сохранить их в списке значений, разделенных запятыми, которые хранятся в одном столбце таблицы базы данных.
Теперь я знаю, что правильным решением было бы создать вторую таблицу и правильно нормализовать базу данных. Простое решение было реализовано быстрее, и я хотел быстро проверить концепцию этого приложения, не тратя на него слишком много времени.
Я думал, что сэкономленное время и более простой код того стоили в моей ситуации, это оправданный выбор дизайна или я должен был его нормализовать с самого начала?
Еще немного контекста, это небольшое внутреннее приложение, которое по сути заменяет файл Excel, который хранился в общей папке. Я также спрашиваю, потому что я думаю о том, чтобы очистить программу и сделать ее более понятной. Там есть некоторые вещи, которыми я не совсем доволен, одна из них - тема этого вопроса.
10 ответов
В дополнение к нарушению формы First Normal из-за повторяющейся группы значений, хранящихся в одном столбце, у разделенных запятыми списков есть много других более практических проблем:
- Не могу гарантировать, что каждое значение является правильным типом данных: нет способа предотвратить 1,2,3, банан,5
- Невозможно использовать ограничения внешнего ключа, чтобы связать значения с таблицей поиска; нет способа обеспечить ссылочную целостность.
- Не может обеспечить уникальность: нет способа предотвратить 1,2,3,3,3,5
- Невозможно удалить значение из списка без извлечения всего списка.
- Невозможно сохранить список дольше, чем умещается в столбце строки.
- Трудно найти все объекты с данным значением в списке; Вы должны использовать неэффективное сканирование таблицы. Возможно, придется прибегнуть к регулярным выражениям, например, в MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'* - Трудно сосчитать элементы в списке или выполнить другие агрегированные запросы.
- Трудно соединить значения с таблицей поиска, на которую они ссылаются.
- Трудно получить список в отсортированном порядке.
- Хранение целых чисел в виде строк занимает примерно вдвое больше места, чем хранение двоичных чисел. Не говоря уже о месте, занимаемом запятыми.
Чтобы решить эти проблемы, вы должны написать тонны кода приложения, заново изобретая функциональность, которую СУБД уже обеспечивает гораздо более эффективно.
Разделенные запятыми списки настолько ошибочны, что я сделал это первой главой своей книги: " Антипаттерны SQL: предотвращение ловушек программирования баз данных".
Есть моменты, когда вам нужно использовать денормализацию, но, как упоминает @OMG Ponies, это исключительные случаи. Любая нереляционная "оптимизация" приносит пользу одному типу запроса за счет других видов использования данных, поэтому убедитесь, что вы знаете, какие из ваших запросов необходимо обрабатывать настолько специально, чтобы они заслуживали денормализации.
* MySQL 8.0 больше не поддерживает этот синтаксис выражения границы слова.
"Одной из причин была лень".
Это звонит в тревогу. Единственная причина, по которой вы должны делать что-то подобное, состоит в том, что вы знаете, как сделать это "правильным образом", но вы пришли к выводу, что есть реальная причина не делать это таким образом.
Сказав это: если данные, которые вы выбираете для хранения таким способом, являются данными, к которым вам никогда не понадобится запрашивать, то может быть случай для сохранения их так, как вы выбрали.
(Некоторые пользователи оспаривают утверждение в моем предыдущем абзаце, говоря, что "вы никогда не знаете, какие требования будут добавлены в будущем". Эти пользователи либо вводят в заблуждение, либо высказывают религиозные убеждения. Иногда выгодно работать с требованиями, которые вы иметь перед вами.)
Есть много вопросов о том, как задать вопрос:
- как получить количество определенных значений из списка через запятую
- как получить записи, которые имеют только то же самое значение 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 позволяет обойти некоторые проблемы со списком с разделителями и при необходимости может быть преобразовано в список с разделителями.
Ну, я использую разделенный табуляцией список ключ / значение в столбце NTEXT в SQL Server уже более 4 лет, и он работает. Вы теряете гибкость при создании запросов, но, с другой стороны, если у вас есть библиотека, которая сохраняет / удерживает пару ключей / значений, тогда это не такая уж плохая идея.
Да, это так плохо. Я считаю, что если вам не нравится использовать реляционные базы данных, тогда ищите альтернативу, которая подходит вам лучше, есть множество интересных проектов "NOSQL" с некоторыми действительно продвинутыми функциями.
Я бы, вероятно, занял среднюю позицию: превратить каждое поле в CSV в отдельный столбец в базе данных, но не сильно беспокоиться о нормализации (по крайней мере, пока). В какой-то момент нормализация может стать интересной, но, собрав все данные в один столбец, вы практически не получаете никакой выгоды от использования базы данных. Вам нужно разделить данные на логические поля / столбцы / как угодно, чтобы вызывать их, прежде чем вы сможете манипулировать ими вообще.
Если у вас есть фиксированное число логических полей, вы можете использовать INT(1) NOT NULL (или же BIT NOT NULL если он существует) или CHAR (0) (обнуляемый) для каждого. Вы также можете использовать SET (Я забыл точный синтаксис).