Как организовать хранение покупок при продаже услуг с разными характеристиками?

Question

[Ипатьев]

Важно: речь о том как хранить покупки, а не товары.

Допустим, мы продаём очень разные по составу услуги. Например

• право поставить определенную символику на определенный сайт (надо хранить имя svg файла, адрес сайта, даты начала и конца действия услуги). Плюс возможность включить автопродление
  
• 10 купонов на посещение определенного ресторана (идентификатор ресторана, процент скидки, количество купонов, даты начала и конца действия услуги)
  
• доступ к определённому файлу на сервере (адрес файла, даты начала и конца действия услуги)
  

Услуги взяты от балды, главное - их полная несовместимость друг с другом.

У меня в голове два варианта - одна широкая таблица со всеми характеристиками всех товаров, или по таблице на услугу.
То есть в любом случае при добавлении услуги придется редактировать схему БД.
Оба варианта мне, понятное дело, не нравятся, но ни выбрать между ними, ни придумать что-то другое я не могу.
JSON не рассматриваю, поскольку нужны джойны с характеристиками товаров (id ресторана например), хотя если будет серьёзная аргументация, то будем брать значение из джейсона.
По этой же причине не рассматриваю недобд типа монги - мне это в существующую базу данных надо встроить - с пользователями, ресторанами, и прочим.

Comments

[Akina]

нужны джойны с характеристиками товаров (id ресторана например)

Оба варианта мне, понятное дело, не нравятся, но ни выбрать между ними, ни придумать что-то другое я не могу.

Вот сразу возникает вопрос - а вот такая выборка, в которой будут использоваться для обработки разреженные атрибуты, она будет включать только одну категорию покупок либо группу совместимых по используемым атрибутам категорий, или может быть ну вообще что угодно (фильтруя купоны по максимальному количеству мест за столиком в ресторане, можем включить в список фильтруемых записей и доступы к файлам)?

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

Как я понимаю, EAV не рассматривается принципиально? У него собственно единственный плюс в том, что туда пролезает ну абсолютно любая чухня. А всё остальное - да, минусы. Включая и производительность.

Что до JSON. Ну формально он индексируется... конечно. Но тут сходу прилетает другая пакость - потребуется индивидуальный индекс практически для каждого типа записи, а MySQL не постгресс, и фильтрованные индексы не умеет. А без индекса - это фуллскан на каждый запрос, и уже на среднем массиве данных всё умрёт, ибо парсинг JSON в MySQL почти внешний. Хотя если есть набор критичных и популярных запросов, можно пойти на частичное переопределение, на вычисляемые виртуальные поля, хранимые в регулярных индексах (которых тоже в штуках получится не по детски), а всё остальное пусть себе тормозит нещадно, переживётся.

Кстати, идея "по таблице на услугу" не так уж и плоха. Вроде бы получается и зоопарк, но собираем всю работу с типом в одну не таблицу, а БД (там ведь не одна таблица на тип получится, будут же ещё всякие словари и справочники, где-то общие, типа адресов, которые ещё можно вынести в служебную БД, а где-то вообще для типа индивидуальные) и один модуль (на клиенте), и уже не так грустно. А если будут требоваться запросы, агрегирующие из нескольких групп, то потенциальную сложность немного закроют стандартизованные представления или процедуры, формирующие сеансовые временные таблицы, а то и дополнительные общие агрегирующие таблицы, обновляемые из триггеров.

[Ипатьев]

Akina, вообще, да, EAV не рассматривал. Но сейчас вот думаю что что-то в этом есть.

Выборки и такие и такие. В основном - либо запрос конкретной услуги (грубо - айди юзера, тип услуги, айди ресторана), либо просто список все купленных услуг, для личного кабинета.

В первом случае кстати доставать айди ресторана через JSON_EXTRACT не проблема - ну прочитаются все покупки этого типа для этого юзера - это будут считанные строки.

[Akina]

Ипатьев, ага, а потом начнётся "выведите мне, сколько я наэкономил за прошлый год". Хотя сколько там будет тех записей на юзера? слёзы, пусть даже за несколько лет...

Опасность есть с обратной стороны - если к этим данным на получение аналитики ещё будут иметь доступ те, кто собственно предоставляет. Вот выборка для них может получиться объёмной.

[Алексей Уколов]

В основном - либо запрос конкретной услуги (грубо - айди юзера, тип услуги, айди ресторана), либо просто список все купленных услуг, для личного кабинета.

Для таких задаче самое адекватное - хранить перечисленные поля в таблице явно, а всё дополнительное уже можно в JSON положить. Если появится ещё какое-то
важное поле, его вполне можно будет "распаковать" из JSON в отдельную колонку. Сценария, когда таких полей будет много (да ещё и так, чтобы у одного типа товара оно было, а у другого нет), я не могу представить.

[Ипатьев]

Akina, тогда если отбросить EAV и "широкую таблицу" (как ненужную, по совету NikFaraday ниже), получается выбор между json и отдельными таблицами.
Первый вариант привлекателен тем, что при добавлении новой услуги мы не меняем структуру БД. Но вообще это лукавство - формально не меняем, а по факту это просто та часть структуры, за которую отвечаем мы сами, как всегда в noSQL. И где-то потом надо всю эту структуру (и её изменения) отдельно хранить.
А во втором у нас внешние ключи, все дела.
Мда, надо уже определяться. Перед тем как задать вопрос, я склонялся к широкой таблице, вчера думал остановиться на джейсоне, а сегодня уже почти уговорил себя на отдельные таблицы :)))

[Ипатьев]

Хотя к таблицам я склоняюсь только потому, что у меня заведомо будет мало услуг, не больше десятка.
А вот если бы было хотя бы в два раза больше, то, пожалуй, останется только json.

[Akina]

Ипатьев,

И где-то потом надо всю эту структуру (и её изменения) отдельно хранить.

Зачем? Ну то есть описание, какой атрибут что означает - да, надо. Но это вопрос, нужный на этапе разработки (и потом поддержки) соответствующего модуля клиентского приложения. И этот модуль уже жёстко привязывается (хардкодом имён скорее всего) к структуре и именам атрибутов JSON для конкретного типа записи. И на основе этой привязки он уже будет выдавать обработанные наборы записей, приведённых к базовой структуре и готовых к использованию модулями отображения и агрегации/анализа.

[Ипатьев]

Akina, ну, у меня привычка хранить эталонный джейсон в БД, а не в коде. Тем более что в таблице с самими услугами как раз самое место.
В общем, пришли обратно к джейсону в итоге, если говорить о масштабируемости...

[Akina]

Ипатьев, я, видимо, не так выразился. На клиенте находится не сам JSON, а код его парсинга на значения отдельных атрибутов - и вот эти имена атрибутов уже там, на клиенте, хардкодом.

Answer 1

[Алексей Уколов]

У меня был проект с такой задачей (и тоже ресторанный). Особенностей у него три:

• "магазины" могут закрываться, а набор товаров полностью меняется несколько раз в год;
  
• заказов относительно немного, 200-400 тысяч в год;
  
• история нужна для личного кабинета и набор полей там довольно ограничен.
  

Исходя из этого, выбрали именно JSON, в который складывались все связанные модели ("магазин", "товар" и т.п.) в сериализованном виде. В таком виде они хранятся год-два на всякий случай, потом старые записи переносятся в архивную таблицу, где только реально используемые на странице истории поля со скалярными типами (путь к картинке, название "магазина" и т.п.).

Но тут надо бы реальную задачу понимать. Может, не надо джойнить, а проще денормализовать данные и вести отдельные таблицы какой-то статистики? Или просто отдельно хранить те поля, по которым предполагается джойнить, а всё остальное пихать в JSON?
В отрыве от конкретных задач вариант широкой "дырявой" таблицы мне кажется самым плохим, вариант с таблицей покупки, к которой полиморфично (properties_type, properties_id) привязаны таблицы со значениями свойств каждого типа сущности выглядит немного лучше.

Comments

[Ипатьев]

Ну вот Akina тоже рекомендует EAV. Спасибо, буду думать в его сторону.

[Akina]

В отрыве от конкретных задач вариант широкой "дырявой" таблицы мне кажется самым плохим

Для MySQL - однозначно да. В других СУБД может быть и иначе - скажем. в SQL Server использование Sparse Column Sets позволит создавать таблицы с ну очень солидным (где-то до 30 тыс.) количеством полей.

Ипатьев,

Akina тоже рекомендует EAV

Упомянул - да. А вот насчёт рекомендовать - не-е-е. На более-менее приличных датасетах EAV быстро превращается в вечную головную боль, причём практически вообще без шансов оптимизировать экстенсивно, а вариант изменения структуры к тому времени уже в принципе не рассматривается как допустимый.

Answer 2

[Nik Faraday]

Самый простой способ просто вынести общие параметры, такие как [конец действия] в отдельную таблицу [услуг]. А далее все таблицы, что нужно соединить с ней в формате one-to-many

customizations
 - id:uuid unique
 - image_name:varchar
 - image_extention:varchar
 - site_url:varchar
 - service_fk:uuid unique

coupons
 - id:uuid unique
 - discount:float
 - ticket_count:integer
 - restaurant_fk
 - service_fk:uuid unique

accesses
 - id:uuid unique
 - file_url:varchar
 - service_fk:uuid unique

services:
 - id:uuid unique
 - date_start:timestamp
 - date_end:timestamp

При выборке делаете просто JOIN и получаете нужные таблицы в связке

Comments

[Ипатьев]

Кстати да, идея всегда джойнить все дополнительные таблицы не приходила мне в голову.
Получается "широкая таблица", но без её недостатков!

[Nik Faraday]

Ипатьев, не то, что бы "широкая таблицы", просто можно делать джоин нужной таблицы и всё. "Широкая таблицы" получится только в некоторых случаях, но в большинстве её не будет

[Nik Faraday]

Ипатьев, если конечно же вопрос идёт об sql, а не о том, как эти данные будут маппиться в объект языка программирования. На уровне sql будет выводиться таблицы с колонками той таблицы, которую вы будете джоинить. Либо процедура с if/else

Answer 3

[hint000]

Старые вопросы на ту же тему:

Как хранить свойства товаров разного рода?

Как в БД делают возможность разделять какой-то продукт по цветам и размерам в наличии?

Какую структуру таблиц выбрать для описания некоторой сущности, у представителей которой часть атрибутов совпадает, а часть — различна?

Можно ли составить mysql запрос с выборкой не определенного множества полей?

Comments

[Ипатьев]

Важно: речь о том как хранить покупки, а не товары.

[Ипатьев]

С другой стороны, если вдуматься, то пожалуй да, ситуация симметричная. Только по другую строну заказа: у товаров все свойства хранятся до покупки, а у услуг - после. Так что пожалуй хранение товаров можно натянуть и на хранение покупок.