Например есть разные типы сущностей, у них разные дополнительные поля (к примеру витрина в магазине, типов товаров может быть очень много вплоть до разделения "процессор", "оперативная память" и тд) и у каждого типа могут быть свои характеристики, по которым потенциально может быть необходимо сортировать, фильтровать и тд. Как для такой задачи лучше организовать схему БД?

1. Для каждой сущности держать отдельную таблицу (ну или одну общую и к ней джойнится куча отдельных). Соответственно при редактировании списка столбцов базе данных высылаются команды вроде alter table. Плюс — БД хорошо делает то, для чего она вроде как предназначена. Минус — замороченность софта а также отдельный запрос на каждую сущность, если грузятся сущности разных типов (ну или миллион left join-ов).

2. Данные хранятся в одной таблице вида (row_id, attr_id, value), по сути таблица разворачивается по столбцам. Плюс — схема БД не меняется, вроде как работать с таким попроще. Минус — наверняка будут запросы, которые тяжело будет оптимизировать на стороне БД.

3. Все динамические столбцы упаковываются в JSON и хранятся в одном поле. Подразумевает современную БД, которая умеет смотреть внутрь JSON-а, например PostgreSQL. Вроде на первый взгляд сплошные плюсы, тут и сортировку можно делать и фильтрацию и индекс создать. Но я с таким никогда не работал и наверняка есть минусы.

Здравствуйте, vsb, Вы писали:

vsb>3. Все динамические столбцы упаковываются в JSON и хранятся в одном поле. Подразумевает современную БД, которая умеет смотреть внутрь JSON-а, например PostgreSQL. Вроде на первый взгляд сплошные плюсы, тут и сортировку можно делать и фильтрацию и индекс создать. Но я с таким никогда не работал и наверняка есть минусы.

На этот сценарий не плохо ложится использование MongoDb.


Для реляционок же к таблице с товарами делаете таблицу с атрибутами. Связь 1:N. Если атрибуты вложенные, то можно сделать древовидную структуру с таблицей вида:
ID, ParentID, Value. Где ParentID — ссылается на Value.

Здравствуйте, vsb, Вы писали:


vsb>3. Все динамические столбцы упаковываются в JSON и хранятся в одном поле. Подразумевает современную БД, которая умеет смотреть внутрь JSON-а, например PostgreSQL. Вроде на первый взгляд сплошные плюсы, тут и сортировку можно делать и фильтрацию и индекс создать. Но я с таким никогда не работал и наверняка есть минусы.

Я предпочитаю такой вариант. Как раз вчера рассказывал про него на митапе в Nexign, описывал разные подводные камни и методы их обхода, советую послушать.
Вроде бы запись должны выложить на YouTube, где-то рядом с https://www.youtube.com/watch?v=7VpymPpcZUg

Если потом будут вопросы — обращайся.

Здравствуйте, vsb, Вы писали:

vsb>Например есть разные типы сущностей, у них разные дополнительные поля (к примеру витрина в магазине, типов товаров может быть очень много вплоть до разделения "процессор", "оперативная память" и тд) и у каждого типа могут быть свои характеристики, по которым потенциально может быть необходимо сортировать, фильтровать и тд. Как для такой задачи лучше организовать схему БД?

vsb>1. Для каждой сущности держать отдельную таблицу (ну или одну общую и к ней джойнится куча отдельных). Соответственно при редактировании списка столбцов базе данных высылаются команды вроде alter table. Плюс — БД хорошо делает то, для чего она вроде как предназначена. Минус — замороченность софта а также отдельный запрос на каждую сущность, если грузятся сущности разных типов (ну или миллион left join-ов).

vsb>2. Данные хранятся в одной таблице вида (row_id, attr_id, value), по сути таблица разворачивается по столбцам. Плюс — схема БД не меняется, вроде как работать с таким попроще. Минус — наверняка будут запросы, которые тяжело будет оптимизировать на стороне БД.

vsb>3. Все динамические столбцы упаковываются в JSON и хранятся в одном поле. Подразумевает современную БД, которая умеет смотреть внутрь JSON-а, например PostgreSQL. Вроде на первый взгляд сплошные плюсы, тут и сортировку можно делать и фильтрацию и индекс создать. Но я с таким никогда не работал и наверняка есть минусы.

Тут в принципе два решения: sql и не sql. Для sql стандартно поле — это сущность и именем, типом и значением. Не sql по разному можно сделать.

P.S. Это уже сто раз тут спрашивали.

Здравствуйте, vsb, Вы писали:

vsb>Например есть разные типы сущностей, у них разные дополнительные поля (к примеру витрина в магазине, типов товаров может быть очень много вплоть до разделения "процессор", "оперативная память" и тд) и у каждого типа могут быть свои характеристики, по которым потенциально может быть необходимо сортировать, фильтровать и тд. Как для такой задачи лучше организовать схему БД?

Я для решения подобной задачи делал следующую схему:
Таблицы:
объекты — основные неизменяемые данные (название, тип, тег)
атрибуты объекта — то, что может меняться (ссылка на объект, тип атрибута, подтип атрибута, тип данных (простые типы, документ, BLOB), опциональная ссылка на другой объект, если атрибут это связь, типа вложенного объекта).
простые данные — хранит данные атрибута, если они простые (double, int, string и ссылку на атрибут)
сырые данные — хранит документы, картинки и прочие файлы, содержит ссылку на объект, название файла, описание файла, тип файла, данные файла и ссылку на атрибут)

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

Здравствуйте, vsb, Вы писали:

vsb>Например есть разные типы сущностей, у них разные дополнительные поля (к примеру витрина в магазине, типов товаров может быть очень много вплоть до разделения "процессор", "оперативная память" и тд) и у каждого типа могут быть свои характеристики, по которым потенциально может быть необходимо сортировать, фильтровать и тд. Как для такой задачи лучше организовать схему БД?
Ключевое — выделено. Наличие либо отсутствие этого пункта радикально влияет на применимость решений.
Без фильтрации можно смотреть в сторону неструктурированных хранилищ. Холиварить не хочу, поэтому тут не очень важно — монго это будет или JSON в nvarchar(max).

Если нужна фильтрация — то забудьте про NoSQL, всех советчиков посылайте лесом: у вас каждая операция будет упираться в full scan. Он будет прекрасно работать на тестовых объёмах, а в продакшне ляжет, а вам будут рассказывать о том, как зато легко это можно победить, подняв 1024 реплики базы в клауде. Тут поможет только нормальное структурированное хранилище.

Филигранить структуру таблиц я бы не советовал — во-первых, быстро упрётесь в ограничения СУБД (они только кажутся большими), во-вторых, зафиксировать "тип товара" удаётся довольно-таки редко. Как правило, реальный товар — это вялый конгломерат каких-то атрибутов, и даже внутри одного типа у вас будет множество атрибутов со значением "не задано". Иногда — потому, что оператор поленился прочитать внимательно сайт производителя, иногда — потому что для данного подвида товара конкретно этот атрибут нерелевантен.

Поэтому выносить в "настоящие" колонки можно только самые-самые популярные атрибуты, ну, и те, которые обязательные (вроде названия и артикула).
Всю остальную кунсткамеру выносите в таблицу атрибутов.
Да, серверу делать по ней многоатрибутную фильтрацию — чуть сложнее, чем для фильтрации по настоящим колонкам.
Хуже работает предсказание селективности, приводя, возможно, к неоптимальному порядку выполнения джойнов. Один хрен, это будет быстрее, чем full scan.

Вообще, по идее, эту задачу не решал только ленивый — наверняка, если порыть интернет внимательно, где-то есть открытый код, который решает типовые задачи для "каталога товаров на EAV":
— собрать entity по её ID
— сравнить N entities, возвращая только различающиеся атрибуты
— найти все entity по заданным значениям (или предикатам) для заданных атрибутов
— найти все значения для заданного атрибута, по убыванию "встречаемости"
— получить список атрибутов для данного типа сущности, отсортированный по убыванию популярности
— итд

Здравствуйте, Sinclair, Вы писали:

S>Если нужна фильтрация — то забудьте про NoSQL, всех советчиков посылайте лесом: у вас каждая операция будет упираться в full scan. Он будет прекрасно работать на тестовых объёмах, а в продакшне ляжет, а вам будут рассказывать о том, как зато легко это можно победить, подняв 1024 реплики базы в клауде. Тут поможет только нормальное структурированное хранилище.

Почему? Postgres умеет строить индекс по конкретному выражению внутри JSON и применять его для фильтрации (возможно и для сортировки, не проверял). Другие коммерческие базы наверняка не хуже. Не просто так же ввели отдельный тип для JSON, XML, весь смысл в том, чтобы база умела смотреть внутрь. Я думал в этом направлении, то бишь изначально предполагаем, что первичного фильтра нам хватает (например по типу товара), а для дальнейшей фильтрации база может и просканировать все отобранные записи. Если это начинает вызывать проблемы (у нас тысячи моделей мониторов и пользователи часто ищут мониторы с конкретными диагоналями), добавляем индекс по нужному подвыражению.

Здравствуйте, vsb, Вы писали:
vsb>Почему? Postgres умеет строить индекс по конкретному выражению внутри JSON и применять его для фильтрации (возможно и для сортировки, не проверял). Другие коммерческие базы наверняка не хуже. Не просто так же ввели отдельный тип для JSON, XML, весь смысл в том, чтобы база умела смотреть внутрь. Я думал в этом направлении, то бишь изначально предполагаем, что первичного фильтра нам хватает (например по типу товара), а для дальнейшей фильтрации база может и просканировать все отобранные записи. Если это начинает вызывать проблемы (у нас тысячи моделей мониторов и пользователи часто ищут мониторы с конкретными диагоналями), добавляем индекс по нужному подвыражению.
В том-то и дело, что "по нужному подвыражению". В таком виде можно прикрутить "искабельность" даже к древним базам — главное, чтобы было понятие "вычисляемая колонка" и индексы по ним.
Это требует постоянного мониторинга статистики эксплуатации, и регулярного тюнинга индексов. Не то, чтобы это было прямо плохо, просто не всегда возможно.
А банальный EAV обойдётся парой индексов отсюда и навсегда.

Здравствуйте, BlackEric, Вы писали:

vsb>>3. Все динамические столбцы упаковываются в JSON и хранятся в одном поле. Подразумевает современную БД, которая умеет смотреть внутрь JSON-а, например PostgreSQL. Вроде на первый взгляд сплошные плюсы, тут и сортировку можно делать и фильтрацию и индекс создать. Но я с таким никогда не работал и наверняка есть минусы.

BE>На этот сценарий не плохо ложится использование MongoDb.

Третий сценарий поддерживается в SQL 2016 и старше — можно завести computed column ссылющуюся на json в другой blob-колонке. Но это если сильно надо.

BE>Для реляционок же к таблице с товарами делаете таблицу с атрибутами. Связь 1:N. Если атрибуты вложенные, то можно сделать древовидную структуру с таблицей вида:
BE>ID, ParentID, Value. Где ParentID — ссылается на Value.

Это второй сценарий. Тормозной.

Здравствуйте, VladCore, Вы писали:

VC>Это второй сценарий. Тормозной.
Есть сравнения производительности в таких сценариях? Ну, просто мне пока непонятно, как обыграть "второй сценари" в более-менее произвольном случае.

Здравствуйте, VladCore, Вы писали:

BE>>Для реляционок же к таблице с товарами делаете таблицу с атрибутами. Связь 1:N. Если атрибуты вложенные, то можно сделать древовидную структуру с таблицей вида:
BE>>ID, ParentID, Value. Где ParentID — ссылается на Value.

VC>Это второй сценарий. Тормозной.

С чего он тормозной? Это весьма распространенное решение.

Здравствуйте, Sinclair, Вы писали:


VC>>Это второй сценарий. Тормозной.
S>Есть сравнения производительности в таких сценариях? Ну, просто мне пока непонятно, как обыграть "второй сценари" в более-менее произвольном случае.

Я имел ввиду в IoT это тормозной сценарий — там с тысяч девайсов переодически и часто приходят данные 10-500 датчикам/параметрам, они разом обрабатываются один/несколько раз а отдельные параметры нужны почти никогда.

Здравствуйте, VladCore, Вы писали:
VC>Я имел ввиду в IoT это тормозной сценарий — там с тысяч девайсов переодически и часто приходят данные 10-500 датчикам/параметрам, они разом обрабатываются один/несколько раз а отдельные параметры нужны почти никогда.
Вроде бы IoT в топике нигде не фигурировал. А в задаче ТС "витрина магазина" основная нагрузка как раз ложится на сценарий "дай мне список продуктов вот с такими параметрами".

Я бы сделал гибрид
— таблица одна и она колумнстор
— все атрибуты по любому запихиваются в json (будет использоваться как аналог select *)
— часть (или все, если база твоя позволит) раскидываются в колонки (да, синхронизацией при апдейтах придется озаботиться)
— nonclustered indexes вешаем на каждую с условием not null (опять же, если их не слишком много)

Здравствуйте, Sinclair, Вы писали:

S>Здравствуйте, vsb, Вы писали:

vsb>>Например есть разные типы сущностей, у них разные дополнительные поля (к примеру витрина в магазине, типов товаров может быть очень много вплоть до разделения "процессор", "оперативная память" и тд) и у каждого типа могут быть свои характеристики, по которым потенциально может быть необходимо сортировать, фильтровать и тд. Как для такой задачи лучше организовать схему БД?
S>Ключевое — выделено. Наличие либо отсутствие этого пункта радикально влияет на применимость решений.
S>Без фильтрации можно смотреть в сторону неструктурированных хранилищ. Холиварить не хочу, поэтому тут не очень важно — монго это будет или JSON в nvarchar(max).

Антон, кажется в этом мире что-то меняется. Есть вот платное облачное решение от мелкософта CosmosDb, которое как-то все это индексирует. Также есть извращенцы, которые строят базу на Elastic Search Cluster.

Здравствуйте, Danchik, Вы писали:

D>Антон, кажется в этом мире что-то меняется. Есть вот платное облачное решение от мелкософта CosmosDb, которое как-то все это индексирует. Также есть извращенцы, которые строят базу на Elastic Search Cluster.
На Cosmos ещё не смотрел.
Вообще, анонс у них впечатляющий — у них есть и финансирование, и команда, и доступ к офигенному объёму статистики по реальному использованию.
С таким стартом можно реально крутую штуку забабахать, а не студенческую курсовую "как я хранил данные, не зная про ACID".