Как лучше задать структуру для данных разных типов?

Question

[Тимофей]

Так вот, у меня есть модель User со всеми присущими devise'овскими полями.
Каждая запись должна так же обладать набором неких дополнительных полей, набор которых строго определен, но при этом по воле администратора сайта этот набор может быть изменен. Скажем, этот набор хранится в таблице с именем profile_fields.
Самым очевидным было бы добавить дополнительную табличку типа profile_field_values, и все бы хорошо, но у этой таблички будет фиксированный тип данных для колонки со значением, а мне необходимо делать выборку по разным типам. Например, у нас есть пользователь vasya, у которого есть следующие доп. поля:

• имя: Вася
  
• дата рождения: 7.7.1997
  
• размер ноги: 43
  

И, скажем, мне нужно получить выборку пользователей, у которых дата рождения более 1.1.1995, размер ноги меньше 45, имя начинается на букву "В". То есть при выборке базе нужно знать, что дата рождения - это именно дата, размер ноги - число, а имя - строка. Следовательно, вариант с одной таблицей для пар ключ-значение по каждому полю не подходит, так как у нас есть только строковое значения, а каждый раз кастовать его при выборке в нужное, думаю, как-то не очень.

Но пока я думал, что тут можно сделать, я вспомнил о такой замечательной вещи в Postgresql, как json. То есть по сути мы можем хранить json объект со всеми необходимыми нам значениями (с определенными ограничениями, но с ними можно справиться) в одном поле и работать с ним так, как если бы у нас внутри стоял MongoDB. Это пока что наиболее предпочтительный для меня вариант, но возникают большие вопросы по поводу производительности и удобства работы с такой структурой.

Третьим вариантом я думал использовать вторую БД Mongo для хранения пользовательских полей, но это уже мне совсем не нравится...

Итак, вопрос: как вы считаете, какой из вариантов наиболее подходящий и почему? Или все эти варианты для лохов и есть куда более грамотное решение?

Answer 1

[vsuhachev]

Наиболее подходящий вариант нельзя посоветовать не зная ваших объемов данных и того как вы планируете это использовать.

Если время поиска не критично, типов данных мало, нужно простое и переносимое решение, то я бы посоветовал вариант 1 с сериализацией различных типов в строку.

Вариант с постгревыми json/jsonb добавляет гибкости/производительности, но в json простые типы это число, строка и булев. Даты и прочих типов там нету. Для jsonb поддерживаются индексы. Это решение означает жесткую привязку к PG.

Есть еще вариант похожий на 1, но с заданием разнотипных колонок и хранением в каждом свойстве его типа. Запросы будут выглядет как-то так

SELECT * FROM profile_field_values
WHERE 
  (property_field="дата рождения"
  and value_type="date" and value_date > ?)
and
  (property_field="размер ноги"
  and value_type="integer" and value_integer < ?)

По скорости вы выиграете, но получите сложности с реализацией и интеграцией всего этого в рельсы.

Еще есть вариация на тему решения N3 - внешний сервис для поиска: Sphinx, Solr, etc...

Comments

[Тимофей]

Ну в плане типов данных, хранимых в json, я все понимаю, но в принципе ту же дату можно хранить как timestamp, в остальном же проблем с типами никаких, думаю, не возникнет. Ну и да, это понятно, что такая реализация привяжет меня к pg, но не думаю, что это проблема. Время поиска скорее всего будет критично, но до этого момента проекту еще дожить надо... Твой вариант я тоже продумывал, тут можно еще на каждый тип данных просто отдельную табличку создавать, но как-то это не по-джедайски)

[Тимофей]

А сервисы типа Sphinx разве не только полнотекстовый поиск реализуют?

[vsuhachev]

Тимофей: конкретно по Сфинксу не подскажу. Солр может искать по любому типу. Если нужно сначала простое решение, а потом на вырост возможность ускорится: вариант 1 + потом внешний поиск.

Answer 2

[Владислав]

Очевидно, что самое разумное решение в данной постановке вопроса - json поле. имхо
В будущем, если некоторое поле будет часто встречаться, его можно будет добавить к модели User, чтобы поиск по этом полю происходил быстрее.

Comments

[Тимофей]

А как дела у postgresql с поиском по значениям внутри json поля?