Настраиваемые поля для таблицы клиентов в CRM. Генерация таблиц?

Question

[Avreliya777]

Попросили в crm на PHP дописать для другого офиса настраиваемые поля для базы клиентов.
Как это обычно реализовывается?
Генерацией таблицы в mysql под нужные поля из PHP?
В одной ячейке поля хранить можно конечно в виде json, но тогда сортировки по ним не будет.
Нагуглила что в других црм тоже некоторых есть такое, и как они под каждого клиента подстраиваются? Если под каждого клиента таблицу создавать, таблиц ведь совсем тогда много будет. Это нормально для БД?

Comments

[Avreliya777]

Dr. Bacon, перепроверила, всё ещё нельзя.
Если в одной ячейке хранится информация по 10-ти полям в виде json-строки, то как средствами mysql сделать select с сортировкой по 7-му полю?

[Алексей Горбунов]

Avreliya777,

EntityID, ID, Name, Value

Где
EntityID = Клиент
ID = ID в таблице клиент
Name = название поля
Value = значение поля

LEFT JOIN в руки и SELECTы строчить

можно еще поле Type добавить, где будет тип поля для всяких приведений, но лучше Тип в отдельные настройки.

[Akina]

но тогда сортировки по ним не будет

Сортировка-то по-любому будет - если можно извлечь отдельное значение, то можно по извлечённому и сортировать.
Другой вопрос - можно ли это делать эффективно. А вот тут всё зависит от точного вида данных и версии MySQL.

Как это обычно реализовывается?

Самый обычный подход - это всё же EAV. Сериализация (включая JSON) - всё же на втором месте.

Если под каждого клиента таблицу создавать, таблиц ведь совсем тогда много будет. Это нормально для БД?

А вот это как раз совершенно ненормально.
Вот если "клиент" - это отдельный MySQL-аккаунт, тогда нормально. Но не таблица, а БД. И то больше по причине безопасности и разделения доступа.

[Avreliya777]

Dr. Bacon, блин, вот я отстала от жизни, даже не знала о JSON_EXTRACT, я так поняла вы это имели в виду. Блин, но разве это эффективно? Тут за производительность бьёшься созданием ключей на нужные поля, оптимизируя сами запросы и прочее прочее... а тут мало того что извлечь нужно так ещё и отсортировать. Кажется совсем мало эффективно будет, сделаю вот так, а потом будут люди по полчаса ждать ответы на запросы в поиске. Алексей Горбунов вот видимо тоже это имел в виду.

[Avreliya777]

Алексей Горбунов, хмм, получается как бы не база клиентов, а база полей со значениями для всех клиентов, верно? Не приходило мне что-то такое в голову, выглядит заманчиво и эффективно вроде. А это не то ли, что называется EAV, про которое Akina вот чуть ниже написала (про eav просто не слышала даже, предстоит погуглить).

[Avreliya777]

Akina, так, генерация таблиц - плохая идея. Это уже хорошо, что хоть что-то можно сразу отмести)
А можете при EAV пример малюсенький применительно к моей ситуации привести? Вот там выше Алексей Горбунов пример привёл хороший вроде, это оно?
А вот про сортировки по json, тут да, меня вот тоже сомнения берут по производительности. Там ведь ключей даже никаких нет.

[Akina]

Avreliya777,

А можете при EAV пример малюсенький применительно к моей ситуации привести?

Дык Алексей уже всё привёл, там и добавить-то особо нечего. Ну кроме правильной расшифровки аббревиатуры: EAV == Entity-Attribute-Value. Для поиска. Ну и обычно ID референсной записи в структуре ставят в начало - но это, само собой, бантики.

Да, предупреждаю сразу - паттерн в общем проблемный. И запросы непростые. да ещё сплошь внешние, и постоянно требуют группировки, посему с производительностью так себе, особенно при ограниченной оперативке. Но зато прекрасно формализуется. и несложно строить динамические запросы.

А вот про сортировки по json, тут да, меня вот тоже сомнения берут по производительности.

Ну я уже сказал - зависит от версии сервера и данных. Свежие версии MySQL поддерживают multivalued indices, хоть и с кучей ограничений (и баги имеются - пользовать осторожно). Кроме того, никто не отменял виртуальных индексированных вычисляемых полей - при фиксированном имени JSON-компонента для сортировки подходят идеально. Правда, при сортировке по зависимой таблице использование сервером индекса из неё при сортировке - штука достаточно редкая, filesort оказывается эффективнее, особенно при высокой селективности запроса.

[Avreliya777]

Akina, так, всё поняла. Спасибо большое за разъяснение! Выбор есть, что-нибудь из этого выберу!

[Дмитрий]

Прям совсем много данных? Просто думал, что можно переложить на пыху

Answer 1

[kritic]

Надо понимать, что именно хотите добавить.
Из описания видится три варианта:
1) Хотите добавить новое конкретное поле к данным клиента.
Тут всё просто, в текущей таблице с клиентом добавляете новое поле в базе, а со стороны crm настраиваете его редактирование и отображение.
2) Хотите добавить n- новых конкретных полей к данным клиента. Для этого делаете новую таблицу со справочником новых полей, где описываете название, тип, значение и ещё одну таблицу, в которой потребуется хранить связь доп. поля и клиента. Например, id клиента, id поля из справочника и значение. Таким образом можно будет проще управлять справочником доп полей и основная таблица с клиентами не превратится в горизонтальную.
3) Хотите добавить n- полей, не знаете каких - это будут добавлять пользователи. Для этого берём п2 и дополнительно реализовываем добавление кастомных доп полей со стороны crm., чтобы пользователи могли редактировать справочник доп полей.