Структура базы данных магазина/каталога для SQL. Как лучше хранить атрибуты и их значения в БД?

Question

[Vasyl Fomin]

Планируется разработка "стандартного" интернет-магазина (реализация будет на Laravel).
Должно быть:

• категории товаров (categories: электроника, огород, книги,...)
  
• товары (products), при этом товары разных категорий имеют разные атрибуты
  
• атрибуты (attributes: цвет, масса, размер)
  
• значения атрибутов (values: зеленый, красный, 1, 2, 5, m, s, xl, xxl)
  

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

Нарисовал следующую структуру БД:


Пока что, теоретически, как бы все должно работать. Но хотелось ли увидеть комментарии/советы, у кого был опыт подобной работы. Возможна ли такая реализация на практике и не столкнусь ли я, с пока что не известными мне, проблемами и ограничениями?

Answer 1

[stratosmi]

Неверно в корне.
Вы нарисовали по сути EAV (есть такая готовая схема данных под эту задачу - погуглите, примеров много).

Плюсы:
Удобно ложиться в реляционную модель, удобно программируется (если вам удобно работать с реляционной моделью), на первый взгляд хорошо подходит под любую реляционную СУБД (MySQL, PostgreSQL, Oracle).

Минусы:
Неоправдано малоэффективно по производительности.
Есть решения на порядок более быстрые.

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

По мне так EAV удобен как средство редактирования (ну и хранения первичной информации).

А вот собственно сам поиск гораздо эффективнее осуществляется на full-text search engines, например:
SphinxSearch (или его клон Мантикора), ElasticSearch и т.п.

И чем по большему количеству фильтров идет поиск - тем больше этот разрыв (хотя он и по одному фильтру уже весьма заметен).

В наиболее развитых full-text search engine поиск по свойствам является "дешевой" операцией, совершенно совместимой с основным полнотекстовый поиском.

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

Бонусом практически "бесплатно" получаете т.н. "фасеточные итоги", отвечающие на вопрос "сколько у нас товаров с таким то свойством" (это часто используется в отображении фильтров на сайтах).

====================

Вывод:

Если вам принципиально на MySQL - просто см. описание схемы данных EAV. Она как раз именно что для этого.
Если вам нужно чтобы все летало - см. SphinxSearch (он крайне быстр и нетребователен к ресурсам).

P.S.:
SphinxSearch
Умеет выдирать данные из MySQL. На их основании строит уже свой собственный узкоспециализированный но очень быстрый поиск для выборок, в вашем случае, по названию и по свойствам товаров.

Comments

[Vasyl Fomin]

А почему же "Неверно в корне" если "Вы нарисовали по сути EAV"?
Неверно нарисованный пример EAV?

Значения атрибутов у меня создаются наперед и просто указываются (выбираются например в multyselect) при создании товара

[stratosmi]

Vasyl Fomin,
я имею ввиду - не верен подход под эту задачу использовать реляционные СУБД, к которым относится и MySQL.
в корне - потому что под задачу поиска по свойствам гораздо лучше подходит совершенно другая СУБД.

для внутреннего учета магазина, где не важна скорость (ну там 2-3 менеджера сидят, редко ищут товар по свойствам, никуда не уйдут, если будет слишком долго думать) - использовать реляционную СУБД это целесообразно.

И даже удобно.
Удобно для хранения/редактирования этих свойство. Но только для менеджеров. Только для редактирования.

Но, скорее всего, в магазине уже есть внутренний учет, например, используется 1С или т.п. Вот там первично фильтры и хранить.

А на сайте - EAV не нужен, если вы не хотите, чтобы у вас все тормозило.

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

После редактирования - все равно все нужно сливать в full-text search engine (например, тот же SphinxSearch).

И искать - только из FTS (full-text search)

[stratosmi]

Vasyl Fomin,

Значения атрибутов у меня создаются наперед и просто указываются (выбираются например в multyselect) при создании товара

Вы говорите про создание/редактирование товара.
Тут да - ваш метод, схема EAV, и реляционные СУБД, к которым относится MySQL - вполне к месту и удобно.

Я же говорю про поиск.
Поиск из MySQL будет довольно медленным в такой схеме.

Поиск на FTS будет радикально быстр.

Поэтому, если создания редактирование товара непосредственно на сайте (а не из другой системы автоматически загружается), я бы сделал так:

Редактирование, создание в MySQL по схеме как у вас или лучше по полному EAV.
После редактирования - запускаем переиндексацию FTS.
Для поиска - обращаемся только к FTS.

P.S.:
FTS не любит частичное обновление индекса, при индексации индекс удаляется и создается заново, поэтому на какое то время поиск будет недоступен вообще.

Поэтому лучше реализовывать переиндексацию FTS как отдельную операцию после окончания редактирования всех товаров. А не после каждого товара.

Имеет смысл сначала создавать новый индекс, для поиска во время индексации FTS использовать старый. Потом переключаться на новый.

Впрочем, в современной версии SphinxSearch есть технология индексации на лету. Это называется RT-индексы. Такие индексы позволяют переиндексировать без временной потери работоспособности поиска.
https://ruhighload.com/real+time+%d0%b8%d0%bd%d0%b...

[vitaly_74]

stratosmi, спаасибо зп ответ

Answer 2

[Дмитрий]

1) Не очень понятно назначение таблицы product_value, допустим у вас есть товар Куртка зимняя, у него есть два варианта (цвет: черный, размер: 42), (цвет: красный, размер: 43), и что будет в таблице?
Четыре записи:

Куртка зимняя	Цвет черный
Куртка зимняя	Цвет красный
Куртка зимняя	размер 42
Куртка зимняя	размер 43

И как вы определите, как эти свойства соотносятся и главное как вы это будете использовать?
У вас же будут вноситься данные по продажам?

Назначение этой таблицы в таком виде непонятно, я бы предложил сделать таблицы

attributes_sets (модификации товара)

• id
  
• product_id
  
• title
  

и

attributes_sets_values (значения свойств данной модификации)

• attributes_set_id
  
• value_id
  

Тогда получится таблица вида

Куртка: вариант 1	Цвет черный
Куртка: вариант 1	размер 42
Куртка: вариант 2	Цвет красный
Куртка: вариант 2	размер 43

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

Также можно писать в поле title таблицы attributes_sets значения модификации через запятую "черный, 42", это упростит вывод информации о модификации в отчетах.

2) В product_value(или attributes_sets_values, если примените пункт 1) можно добавить поля attribute_id, value_int, это позволит для числовых атрибутов не задавать заранее список значений в values, а сразу указывать числовое значение

3) Цены лучше не хранить в товаре, а сделать отдельные таблицы

price_types: id, name
prices: price_type_id, product_id, set_date, price

Это позволит назначать товару разные цены в зависимости от всякого рода условий, можно также добавить в prices поле user_id, чтобы не возникал вопрос кто поменял цену

Comments

[Vasyl Fomin]

"два варианта (цвет: черный, размер: 42), (цвет: красный, размер: 43)" - да, это уже проблема, получается, если не изменять структуру на вашу, то нужно варианты товара хранить как два отдельных товара..(

Answer 3

[Станислав Шендаков]

Я в подобной ситуации, использовал поле с типом JSON. Т.е. содержимое такого поля у одного товара может быть таким: [ "color" : black, "size" : 42, "gender" : "male", "season" : "winter"], а у другого следующим : [ "color" : black, "size" : 42, "kind" : "mountain", "sole" : "vibram"]. Нынешний MySQL позволяет по таким полям осуществлять поиск, а в ЯП имеются функции по раскодировке JSON в обычный массив.

Comments

[Vasyl Fomin]

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

Answer 4

[alex-1917]

Вот тебе подсказка (см. скрин), правда, убраны цвета, с ними на порядок больше возни и в БД и при поиске.
Т.е. разный цвет - это разный товар.

spoiler

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

spoiler

Comments

[Pimur]

Что за админка?