Как работать с множеством прайс-листов в БД MySQL?

Question

[Chvalov]

Как правильно организовать работу с прайс-листами для интернет магазина ?
Есть 4-6 поставщиков, некоторые товары повторяются, разнятся лишь цены и места складов

Набросал маленькую схему:
Производители:

CREATE TABLE product__brands (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  brand VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Товары:

CREATE TABLE product (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  image VARCHAR(128) NULL DEFAULT NULL,
  price DECIMAL(13,6) NOT NULL,
  brand INT(10) UNSIGNED NOT NULL,
  mpn_number VARCHAR(64) NOT NULL COMMENT 'Артикул товара',
  PRIMARY KEY (`id`),
  INDEX product_brand_idx (mpn_number,brand),
  CONSTRAINT FK_brand_id FOREIGN KEY (`brand`) REFERENCES product__brands(`id`)
) ENGINE=InnoDB;

Также есть таблица product_details в которой характеристики/атрибуты и т.п.

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

Есть ли CMS в которой реализован нужный для меня функционал, чтобы посмотреть на структуру БД ?

Comments

[sim3x]

Сохранять историю прайсов от поставщиков нужно?
Поставщики имеют одно и то же название для каждой позиции?
Для выдачи пользователю требуется отдавать товар с одной ценой?

[Chvalov]

sim3x,

1. Сохранять не обязательно, но было бы плюсом
   
2. Поставщики имеют одинаковый артикул товара, в некоторых правда без лишних символов, но это буду делать с помощью trim()
   
3. Пользователю будет показываться либо средняя цена + наценка либо минимальная с прайса + наценка
   

Правда с наценкой так-же нужно будет продумать, так как на разные категории товаров разная наценка.

В БД товары с артикулами уже привязаны к категориям

-- Связь (Товар<->Категория)
CREATE TABLE category__product (
  product_id INT(10) UNSIGNED NOT NULL,
  category_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  CONSTRAINT FK_category_produc_pid FOREIGN KEY (`product_id`) REFERENCES product(`id`),
  CONSTRAINT FK_category_produc_cid FOREIGN KEY (`category_id`) REFERENCES category(`id`)
) ENGINE=InnoDB;

[Chvalov]

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

Answer 1

[sim3x]

производитель товаров или владелец торговой марки

Brand: 
  name
...

Каталог, как тег товара
То что помечено знаками вопроса странно называется или не имеет смысла
Если каталог = теги, то они не должны иметь вложенности
Теги плоская структура
Иерархия сама по себе геморная вещь
Реализация иерархии своими силами - требует неплохих навыков

Catalog: 
  name
  name_lang2
  description
  description_lang2
  meta_description
  meta_description_lang2
  meta_keywords
  meta_keywords_lang2

  parent -- ???
  status --???
  top --???
  left_key 
  right_key

Товар

Product: 
  mpn  -- Manufacturer part number

  mpn_fix  -- ???

  catalog = ManyToMany(Catalog)
  brand = ForeignKey(Brand)
  
  image
  -- для ускорения, после импорта прайса от поставщика,
  -- можно перекалькулировать поля
  price
  price_median
....

Поставщик
Если поставщик имеет несколько складов, то данная таблица будет складом,
а таблица поставщиков отдельно
от таблицы Склад будет идти связь по FK к поставщику

Supplier:
  name
  name_lang2
  address
  phone
  website
...

Product_price:
  supplier = ForeignKey(Supplier)
  product = ForeignKey(Product)

  price
  -- вариант с сохранением истории цен
  date_time

Comments

[Chvalov]

sim3x Если я правильно понял схема будет примерно такой:
Поставщик:

CREATE TABLE supplier (
  id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  address VARCHAR(255) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  website VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Теперь товары поставщиков:

CREATE TABLE product_supplier (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  supplier_id TINYINT(3) UNSIGNED NOT NULL,
  brand INT(10) UNSIGNED NOT NULL,
  mpn_number VARCHAR(64) NOT NULL COMMENT 'Артикул товара',
  warehouse VARCHAR(255) NOT NULL COMMENT 'Склад (Адрес)',
  PRIMARY KEY (`id`),
  CONSTRAINT FK_supplier_id FOREIGN KEY (`supplier_id`) REFERENCES supplier(`id`),
  CONSTRAINT FK_brand_id FOREIGN KEY (`brand`) REFERENCES product__brands(`id`)
) ENGINE=InnoDB;

И последнее история цен

CREATE TABLE product_price (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  price DECIMAL(13,6) NOT NULL,
  supplier_id TINYINT(3) UNSIGNED NOT NULL,
  product_id INT(10) UNSIGNED NOT NULL,
  date DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  CONSTRAINT FK_supplier_id FOREIGN KEY (`supplier_id`) REFERENCES supplier(`id`),
  CONSTRAINT FK_product_id FOREIGN KEY (`product_id`) REFERENCES product(`id`)
) ENGINE=InnoDB;

Но я что-то запутался, логика этой схемы такая ?:

• При загрузке прайс листа все данные пишем в product_supplier, без цен
  
• Цены пишем в product_price
  
• После чего делаем перекалькуляцию цен в таблице product используя последние данные с product_price
  

[sim3x]

Chvalov,
Артикул товара == SKU

Что за связи

CONSTRAINT FK_brand_id FOREIGN KEY (`brand`) REFERENCES product__brands(`id`)

Бренд (производитель, владелец торговой марки) относится к продукту, а не к поставщику

Сменил в ответе product_supplier на supplier

[Chvalov]

sim3x Для всех следующих прайсов (Импорт):
INSERT INTO ... ON DUPLICATE KEY UPDATE ?

Второй шаг: для записи цены и связи товара + поставщика, если товара нету в базе добавлять товар в общую категорию, после добавлять данные в product_price ?

[sim3x]

Chvalov,
Если вариант с date_time, то дупликатов не будет
Без - да

2. Ну да

Советую прямо сейчас подумать о бекенд фреймворке, чтоб не писать на скуле вот ето все

[Chvalov]

sim3x, По поводу связи
Некоторые поставщики в прайсе пишут по разному:

• MPN: AB600632, BRAND: AB
  
• MPN: 600632, BRAND: AB
  

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

[sim3x]

Chvalov, перед вставкой все такие вещи нужно обработать и привести к одному варианту

[Chvalov]

sim3x, Для этого планирую создать под каждого поставщика конфиг, в котором все будет сводится к одному варианту.

Схема хороша тем что в дополнение можно рисовать график роста/падения цены, а также можно к цене и времени писать и наличие и также в графике рисовать

Минус в том что нужно продумать как её оптимизировать, так как на втором шаге при записи в product_price слишком много выборок будет (Прайсы больше 100-150к товаров)

[sim3x]

Chvalov,

Минус в том что нужно продумать как её оптимизировать, так как на втором шаге при записи в product_price слишком много выборок будет (Прайсы больше 100-150к товаров)

зачем при записи делать выборку?

150к не много

[Chvalov]

sim3x, точнее на третем шаге в таблице product_price
Вставка новой цены и даты
А с выборки товар и поставщик

price DECIMAL(13,6) NOT NULL,
supplier_id TINYINT(3) UNSIGNED NOT NULL,
product_id INT(10) UNSIGNED NOT NULL,

[sim3x]

Chvalov, INSERT INTO ... ON DUPLICATE KEY UPDATE же

[Chvalov]

sim3x, Хм, думал это применять на таблице product_supplier
А в product_price всегда дописывать данные, чтобы график нарисовать + в админке увидеть какой товар несколько дней не обновлялся

|   price  |   supplier_id    |   product_id   |   datetime   |
|   162    |   1              | 1              |   26.01.18   |
|   158    |   2              | 1              |   26.01.18   |
|   160    |   1              | 1              |   27.01.18   |
|   202    |   1              | 1              |   28.01.18   |
|   193    |   2              | 1              |   28.01.18   |

[sim3x]

Chvalov,
Запишите мысль полностью
Я вас не понимаю начиная с Как работать с множеством прайс-листов в БД MySQL?

[Chvalov]

sim3x, смотрите: Импортирую прайс, в product_supplier пишу SKU (mpn_number), склад и кол-во на складе использую INSERT INTO ... ON DUPLICATE KEY UPDATE
Актуальные цены пишу в product_price
price - Актуальная цена с прайс листа
supplier_id - id поставшика в таблице supplier
datetime - время импорта DEFAULT NOW();

А вот с product_id мне нужно перед вставкой выбрать id в таблице product (Таблица в самом вопросе)

*************************
Или если Я правильно понял, у вас product_id = артикул товара
И тогда при перекалькуляции цен мы просто выбираем свежие данные с таблицы product_price по артикру и обновляем цену в таблице price ?

ERD

блин, уже сам запутался в своих мыслях, походу пора спать ((

[sim3x]

Chvalov,

Все что касается продукта в разрезе поставщика
- цена
- его количество у поставщика
заносится в таблицу Product_price (и ее следует переименовать, раз ТЗ у нас расширяется на ходу)

product_id = артикул товара

нет
Каждая таблица имеет свое поле id - PK
И к нему привязываем все FK

Я все таблицы мыслю так, как бы я писал код моделей на джанго

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

Уточним сначала ето, а потмо я обновлю ответ

[Chvalov]

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

[sim3x]

Chvalov, какая многоязычность?
Несколько языков без добавления десятка по ходу писания проекта?
Как будет происходить перевод для товаров, брендов и компаний?

[Chvalov]

sim3x, Да, будет 2 языка, в процессе работы не планируется расширять языковый диапазон

Под перевод попадают: Категории товаров, Блог (Новости/Акции),
Из перевода товаров, планируется лишь характеристики и описание
Для этого будет создана еще одна таблица product_details и связь товар и язык

Названия не планируются к переводу

[sim3x]

Chvalov, обновил

Нужно решать зачем каталогам иерархия

Как будут использоваться Характеристики товара

[Chvalov]

sim3x, Категории - Nested sets

status -- можно переназвать в active (Если нужно будет скрыть категорию)
top -- для отображения категории в блоке "Популярные категории"

По поводу характеристик, реализация будет на подобии OpenCart (ERD)
выборка будет уже при открытии самого товара

Для работы с прайсами характеристики и атрибуты товара не нужны

[sim3x]

Chvalov,
Я вижу что категории сделаны с намеком на MPTT - я спрашиваю зачем?

На опенкарт не стоит смотреть
Магента - еще куда ни шло

Но вопрос не в том - производительность жсона в постгресе слегка быстрее чем EAV и hstore
Стоит иметь в виду и такую реализацию характеристик

is_active
in_top_list, top_list_position
звучат более информативно

[Chvalov]

sim3x, MPTT это и есть Nested Sets, для удобства выборки, построения дерева, хлебные крошки, намного лучше чем в том-же opencart

А как с производительностью Json в MySQL 5.7 ?
С подобной

структурой

[
        {"name": "Parent 1",
            "child": [
                {"name": "Child 1-1"},
                {"name": "Child 1-2"},
                {"name": "Child 1-3"}
            ]},
        {"name": "Parent 2",
            "child": [
                {"name": "Child 2-1"},
                {"name": "Child 2-2"},
                {"name": "Child 2-3"
                    ,"child": [
                    {"name": "Child 2-3-1"},
                    {"name": "Child 2-3-2"}                 
                    ]
                }
             ]
        }       
    ];

В будущем возможно перейду на PostgreSQL, пока в планах MySQL + memcached

[sim3x]

Chvalov, про мускул не скажу - тем более что там непонятно на каком движке жсон работает

Тройная вложенность может стать головной

МРТТ для тегов не нужен