Подходы для расширяемых пользователем сущностей с реляционной базой данных (mySQL)
У нас есть система php / mysql с 5 основными объектами. Теперь нам нужно добавить возможность для клиентов создавать настраиваемые поля для некоторых из этих объектов для каждого проекта.
Они будут содержать метку, ключ, тип, значение по умолчанию и возможные допустимые значения.
Это делается для того, чтобы они могли добавить пользовательское поле даты или пользовательский выпадающий список в пользовательский интерфейс и сохранить это значение для конкретной сущности.
Каков наилучший подход для хранения данных такого рода в базе данных mySQL? Мне нужно сохранить как конфиг для поля, а затем текущее значение для конкретной сущности.
Я посмотрел на различные варианты здесь .. https://ayende.com/blog/3498/multi-tenancy-extensible-data-model
Но это на самом деле не на уровне аренды, а скорее на уровне проекта.
Я думал…
- Таблица CustomFields для хранения конфигурации поля с типом сущности и идентификатором проекта.
- Таблица CustomFieldValues для хранения значения, сохраненного для поля — по строке на поле (entity_id | field_id | field_value)
Затем мы создаем отношения между сущностями и этими пользовательскими значениями при извлечении сущностей.
Проблема заключается в том, что в таблице «Значения» будет столько строк, сколько есть настраиваемых полей, поэтому сохранение сущности приведет к X дополнительным строкам. Кроме того, они являются версионными, поэтому после создания новой версии для этой новой версии будут созданы еще X строк.
Кроме того, вы не можете индексировать поля по имени, объединения станут довольно сложными, я думаю, что вам нужно присоединиться к конфигурации и значениям для построения пары ключ-значение для возврата к сущности, и как вы будете выбирать на основе имя настраиваемого поля, когда имя поля было на самом деле значение?
Я не хочу добавлять динамические столбцы в таблицу, так как это повлияет на ВСЕ объекты во всей системе, а не только на текущий клиент / проект.
Другой вариант — сохранить значения в столбце JSON.
Это может быть в самой строке сущности customFields или похожие. Это предотвратит дополнительные строки для каждого поля, но также приведет к проблемам с отсутствием индексации и т. Д., И все же потребуется присоединиться к таблице конфигурации. Однако вы можете выполнять запросы по имени свойства, если key=value был сохранен в JSON … WHERE entity.customFields->"$.myCustomFieldName" > 1,
Сохранение сохраненного имени в json означает, что вы не сможете изменить его после создания без особой боли.
Если у кого-нибудь есть какие-либо советы относительно подходов к этому или статьи, на которые я бы указывал, это будет высоко цениться — я уверен, что это было решено много раз раньше ….
Решение
JSON records: нет! Тысячу раз нет! Если вы сделаете это, просто подождите, пока кто-нибудь на самом деле использует вашу систему для нескольких десятков миллионов записей, а затем попросит вас выполнить поиск по одному из ваших дополнительных полей. Ваши помощники проклянут ваше имя.
Хранилище ключей-значений. Вероятно, да. Существует очень широко распространенное доказательство существования этого проекта: WordPress. У него есть таблица wp_postmeta, содержащая поля метаданных, применяемые к wp_posts (страницы блога и сообщения). Это оказалось успешным.
Вам нужно будет сделать несколько соединений, чтобы использовать этот материал. Например, для поиска по высоте и цвету глаз вам понадобится
SELECT p.person_id, p.first, p.last, h.value height, e.value eye_color
FROM person p
LEFT JOIN attrib h ON p.person_id = h.person_id AND h.key='eye_color'
LEFT JOIN attrib e ON p.person_id = e.person_id AND e.key='height'
WHERE e.value='green' and CAST(h.value AS INT) < 160
Как АКТЕР в этом WHERE В этом разделе вы также столкнетесь с типом данных.
Вам понадобятся операции LEFT JOIN для поиска атрибутов такого типа; обычные внутренние операции JOIN будут подавлять строки с отсутствующими атрибутами, и это может не сработать.
Но если вы хорошо поработаете с индексами, вы сможете получить приличную производительность при таком подходе.
Структура таблицы, представленная в моем примере, не содержит таблицы, описывающей каждое дополнительное поле, но вы знаете, как ее добавить. Он также не имеет явной поддержки разделения данных между проектами и несколькими арендаторами. Но вы также можете добавить это.
Другие решения
Других решений пока нет …