Структура таблиц БД: хранение списков значений наряду с обычными значениями
БД: MySQL. Задача: хранить словаревидные данные в виде id:int->value:string. Проблема: оказалось, что иногда нужно, чтобы одному id соответствовал список значений. При этом, если даже список состоит из одного элемента, все равно нужно отличать его от обычного значения.
Я вижу несколько вариантов решения, но ни один мне не нравится.
1) Хранить данные не в виде строки, а в каком-то формате: XML, JSON, etc. Тогда в одно строковое поле можно будет сохранить целый объект.
Вариант не нравится тем, что в итоге получаем денормализацию данных и проблемы, с ней связанные, например, невозможность оперировать значениями списка по отдельности стандартными средствами SQL. Чтение и изменение отдельных элементов прийдется реализовывать средствами приложения.
1.а) Хранить данные в одной строке с разделителем. Это частный случай варианта 1, и минусы те же самые.
2) Создать отдельную таблицу для значений списков.
Вариант не нравится тем, что прийдется делать запросы уже к двум таблицам как при чтении, так и при записи.
3) Хранить все данные в одной таблице, просто не делать id строки словаря уникальным ключом, тогда можно будет добавлять несколько записей для одного id.
Не нравится тем, что тогда сложно определить, является ли элемент обычным элементом, или же частью списка. Добавление специального поля-флага а-ля is_list_element — костыль.
Ну как по мне то вариант2 (Создать отдельную таблицу для значений списков.) является оптимальным и стандартным. Обычно от него отходят только в нестандартных ситуациях. Не рекомендую выдумывать велосипед.
Как показывает опыт, это оптимальный вариант. В таком случае как минимум удобно:
1. Переносить элемент списка от одного раздела в другой
2. Строить Вьюшки и соответственно их выводить
Описанное вами — это развитие реляционной модели в сетевую… в mysql как я знаю средств для этого нет, в postgresql есть поддержка массивов, только производительность не ля всех случаев оптимальна.
Ваша задача лучше всего решается все таки сериализацией. Проблема обновления данных при расширении функционала не на столько критична чтобы только на основании этого отказываться от сериализации.
Так же не стоит закрывать глаза штатную реализацию списков второй таблицей M-1.
И конечно же никто не мешает совместить оба подхода (хранить данные в 2 таблицах и кешировать дополнительными полями в главной, например информация о количестве элементов в списке, значение первого элемента,..)
Наверное, вы имели в виду иерархическую модель данных. Впрочем, она — всего лишь частный случай сетевой модели.
А почему вы считаете, что задача лучше всего решается сериализацией?
Проблема обновления данных встаёт не только и не столько при расширении функционала, но и вообще при любых обновлениях данных.
Вернее, самая большая проблема такого подхода — перенос действий, которые должны производиться базой данных (выборочное обновление, выборочное чтение, поиск), в код приложения. А зачем это делать, если можно структурировать данные правильно и оперировать ими при помощи средств БД?
Собстенно говоря, я уже склонился к использованию варианта с двумя таблицами. Он является самым правильным с точки зрения нормализированной реляционной структуры данных. Да и самым удобным, судя по всему, тоже.
где ID — уникальный номер записи в таблице (primary key), ListID — какойто ваш внутренний идентификатор, Value сами понимаете, что. Тогда вы сможете извлеч как одну строку, написав SELECT * From TableName Where ListID = 1 или несколько значений, которые уже и составят для вас список. Узнать, сколько у ваc значений с одним ListID так: SELECT Count( * ) From TableName Where ListID = 1; и не нужно дополнителных полей для отметки список это или нет.
Обратите внимание, что (по вашим вариантам) разделители или мультизначения тоже потом надо будет както обработать, потратив время, и (вероятно) еще раз сделать SELECT, затратив и на него дополнительное время и ресурсы. С моим вариантов у вас будет просто join если потрубется.
Не в коем случае не теряйте уникальный ключ в вашей таблиц, как вы хотите в 3!
В третьем варианте необязательно терять первичный ключ. Я только сказал, что в этой таблице можно сделать id записи в словаре неуникальной. Тогда этот id будет соответствовать вашему ListID. И получается точно тот же вариант.
Собственно, проблема с этим вариантом состоит в том, что без дополнительных костылей нельзя отличить обычную запись от записи-списка. При этом также присутствует некоторая денормализация, как мне кажется.
Primary Key уникален по определению и настоятельно рекомендуется к использованию. Если вы его делаете неуникальным от от него остается одно название и стройная колонка цифр…
Нет ничего страшного в добавлении еще одной колонки, осоценно цифр — вы не потеряете в скорости не на йоту. А вот если будите хранить json/xml то потеряете возможность масшабирования базы и придется в коде сайта разбирать этот формат, что потребует времени.
А как отличить — сделайте запрос SQL, вернулась одна строка — не список, больше одной — список ;) кажется в php это очень просто выяснить и не надо в базу ничего писать лишнего.
Я нигде не писал, что в этом ваиранте нет Primary Key. Я писал, что идентификатор записи словаря (в которой может содержаться несколько элементов) будет неуникальным в такой таблице. В вашем примере это соответствует ListID. Естественно, будет добавлен искусственный первичный ключ отдельной колонкой.
А вот отличить список с одним элементом от элемента, не являющегося списком, в жанном случае невозможно без добавления уже 4-й колонки а-ля is_list_element. В условии задачи я написал, что это — необходимое условие.
В этом варианте струтуры да, это единственный способ достичь желаемого результата. Но выглядит он как костыль :) В частности, поэтому я уже решил использовать вариант №2. Он является самым правильным с точки зрения нормализированной реляционной структуры данных. Да и самым удобным, судя по всему, тоже.
Разрешите поинтересоваться, а как соотношение обычных элементов к списковым на что-то влияет?
Меня вот беспокоит другой вопрос. Я сейчас создал две таблицы по варианту №2, и у этих таблиц получилась идентичная структура, за исключением уникального индекса в первой таблице.
Т.е. имеем три поля: id, control_id, value.
В таблице с простыми элементами control_id нужно, чтобы поставить в соответствие запись в этой таблице и элемент страницы, в который данные будут попадать (textbox). Во второй таблице control_id выполняет ту же роль для dropdown-листов + по этому полю нужно будет группировать записи.
Все хорошо, но практически идентичная структура таблиц наводит на подозрения :)
Разрешите поинтересоваться, а как соотношение обычных элементов к списковым на что-то влияет?
Если у Вас всего 1% записей вида «список», и всего 1-2 дубля на каждую, то 99 раз из 100 Вы будете тратить 2 запроса вместо одного, и только 1 раз сэкономите на этом что-то. Стоит ли это того?
Это нечто сродни кэшированию. Если кэш долго строится… и у Вас 99% попаданий в кэш это хорошо, а если 1% попадание в кэш, то смысла в кэше в общем-то не много. С кэшем это как-то более очевидно:)
Все хорошо, но практически идентичная структура таблиц наводит на подозрения
Именно. Вы тут абсолютно правы.
Но окончательное решение зависит от Ваших реальных данных. Нормализация должна делаться во благо, не в последнюю очередь для уменьшения объема данных. А в случае почти полного дублирования…
То есть если у Вас по 10 в среднем значений на каждый ключ, и при этом 80% ключей имеют значения типа «список», то выбор 2-ого варианта однозначен. А если списки редки и невелики, то не однозначен как минимум.
Средний
Простой
Средний
