Проблема с разработкой доски объявлений

Question

[Дмитрий]

Здравствуйте. Поставлена задача разработать сервис для доски объявлений с множеством сортировок.
После вечернего обдумывания структуры БД пришёл в ступор: не могу понять механизм сортировок. Суть проста: есть велосипеды. У велосипедов есть марки, в зависимости от марки велосипед имеет разное количество скоростей. Продумал варианты, в голову пришла только одна мысль - создание таблиц для каждой глобальной категории (Транспорт -> Велосипеды). Есть ли другие оптимальные решения данной задачи? Заранее благодарю.

Comments

[denver]

Что за неизвестная планете проблема механизма сортировок? Какие именно сортировки в результате нужны? А то читается как будто ты велосипеды по скоростям сортировать будешь.

[denver]

Или говоря сортировка ты имеешь в виду хранение? Ну так оптимальный способ хранения зависит от того как надо доставать потом. Как эта информация должна будет выводиться, как сортироваться (order by) и тп.

Answer 1

[Shaks]

Советую глянуть в сторону NoSQL решений, например MongoDB.
А по поводу mysql щас подумаю, чуть позже отпишу.

** подумал..
4 таблицы:
items - товары
categories - список категорий (под-категории не учитываю для простоты изъяснения)
options - характеристики товаров (связь один ко многим)
items_categories - категории товаров (связь один ко многим)

Суть вот в чем, у каждого товара может быть ноль или много характеристик. Характеристики имеют ключ и значение, значение может быть типа integer или varchar. В этом заключается проблема, не знаю как её можно решить изящно. Как вариант делать два поля string_value и int_value с соответствующим типом. А при поиске выбирать поле по типу искомого.

items_categories содержит в себе поля category_id и item_id, таким образом список категорий того или иного товара достигается элементарным запросом с inner join

Пример выборки категорий товара

SELECT `categories`.* FROM `categories` INNER JOIN `items_categories` ON `categories`.`id` = `items_categories`.`category_id` WHERE `items_categories`.`item_id` = 123

Пример выборки товара с его характеристиками

SELECT `items`.* , `options`.* FROM `items`, `options` WHERE `items`.`id` = 123 AND `options`.`item_id` = `items`.`id`

В общем как-то так..

Comments

[Иван]

Можете рассказать человеку, который слышал о nosql только понаслышке, чем в данном случае nosql будет лучше?

[Shaks]

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

[Shaks]

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

MongoDB shell version: 2.4.10
connecting to: test
> use toster
switched to db toster
> item = {name:'велосипед', speed:250, weight:100, color:'red'}
{ "name" : "велосипед", "speed" : 250, "weight" : 100, "color" : "red" }
> db.items.insert(item)
> another_item = {name:'меч', material:'валерийская сталь', weight:3}
{ "name" : "меч", "material" : "валерийская сталь", "weight" : 3 }
> db.items.insert(another_item)
> db.items.find()
{ "_id" : ObjectId("538132bd3a7b3daf65aa8fb1"), "name" : "велосипед", "speed" : 250, "weight" : 100, "color" : "red" }
{ "_id" : ObjectId("538133003a7b3daf65aa8fb2"), "name" : "меч", "material" : "валерийская сталь", "weight" : 3 }
>

[Shaks]

Как видно (выше), в одной коллекции у нас есть велосипед, с атрибутами скорости, цвета и веса, и меч с атрибутом материала и веса.
Очень удобно хранить данные

[Иван]

@shaks а искать?

[Shaks]

@0neS
# добавлю пару ацких великов
> db.items.insert({ "name" : "велосипед", "speed" : 200, "weight" : 90, "color" : "black" })
> db.items.insert({ "name" : "велосипед", "speed" : 150, "weight" : 95, "color" : "black" })

> db.items.find({name:'велосипед'}) # ищу только велосипеды
{ "_id" : ObjectId("538132bd3a7b3daf65aa8fb1"), "name" : "велосипед", "speed" : 250, "weight" : 100, "color" : "red" }
{ "_id" : ObjectId("538136213a7b3daf65aa8fb3"), "name" : "велосипед", "speed" : 200, "weight" : 90, "color" : "black" }
{ "_id" : ObjectId("5381362e3a7b3daf65aa8fb4"), "name" : "велосипед", "speed" : 150, "weight" : 95, "color" : "black" }
> db.items.find({name:'велосипед', speed:{$gt:200}}) # ищу велосипеды со скоростью больше 200
{ "_id" : ObjectId("538132bd3a7b3daf65aa8fb1"), "name" : "велосипед", "speed" : 250, "weight" : 100, "color" : "red" }
>

[Дмитрий]

shaks, спасибо, буду думать в эту сторону. Очень интересное решение вы описали.

[Иван]

@shaks в чем смысл было писать «А по поводу mysql щас подумаю, чуть позже отпишу», а потом редактировать ответ, 1 в 1 повторяя то, что написал я?

[Shaks]

@0neS решения разные, хотя и похожи

[Иван]

@shaks отличаются только названиями таблиц

Answer 2

[Иван]

Я бы сделал вот так:

CREATE TABLE IF NOT EXISTS `category` (
    `id` int unsigned not null AUTO_INCREMENT,
    `name` varchar(256) not null,
    `parent_id` int unsigned,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`, `parent_id`),
    CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO `category` (`id`, `title`, `parent_id`) VALUES
    (1, 'Бытовая техника', null),
    (2, 'Стиральные машины', 1),
    (3, 'Пылесосы', 1),
    (4, 'Холодильники', 1),
    (5, 'Транспорт', null),
    (6, 'Автомобили', 5),
    (7 'Мотоциклы', 5),
    (8,'Катеры', 5),
    (9, 'Велосипеды', 5);

CREATE TABLE IF NOT EXISTS `model` (
    `id` int unsigned not null AUTO_INCREMENT,
    `name` varchar(256) not null,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
) ENGINE=InnoDB;

INSERT INTO `model` (`id`, `name`) VALUES
    (1, 'Аист'),
    (2, 'Орленок'),
    (3, 'Дружок'),
    (4, 'Спутник'),
    (5, 'Десна'),
    (6, 'Салют'),
    (7, 'STELS');

CREATE TABLE IF NOT EXISTS `attribute` (
    `id` int unsigned not null AUTO_INCREMENT,
    `name` varchar(256) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `attribute` (`name`) VALUES
    ('Кол-во скоростей');

CREATE TABLE IF NOT EXISTS `model_attribute` (
    `model_id` int unsigned not null,
    `attribute_id` int unsigned not null,
    `value_int` int,
    `value_var` varchar(4096),
    PRIMARY KEY (`advert_id`, `attribute_id`),
    CONSTRAINT `model_attribute_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `model` (`id`) ON DELETE CASCADE,
    CONSTRAINT `model_attribute_ibfk_2` FOREIGN KEY (`attribute_id`) REFERENCES `attribute` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO `model_attribute` (`model_id`, `attribute_id`, `value_int`, `value_var`) VALUES
    (1, 1, 1, null),
    (2, 1, 1, null),
    (3, 1, 1, null),
    (4, 1, 1, null),
    (5, 1, 1, null),
    (6, 1, 1, null),
    (7, 1, 21, null);

CREATE TABLE IF NOT EXISTS `advert` (
    `id` int unsigned not null AUTO_INCREMENT,
    `title` varchar(256) not null,
    `text` varchar(2048) not null,
    `category_id` int unsigned not null,
    `model_id` int unsigned,
    PRIMARY KEY (`id`),
    CONSTRAINT `advert_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE,
    CONSTRAINT `advert_ibfk_2` FOREIGN KEY (`model_id`) REFERENCES `model` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO `advert` (`id`, `title`, `text`, `category_id`, `model_id`) VALUES
    (1, '...', '...', 9, 7),
    (2, '...', '...', 9, 4);

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

Comments

[Shaks]

Если я правильно понял поставленную задачу, то - "велосипеды" это метафора. Там будут не только велосипеды. а даже розовые кружечки zaxaroid'a с пикабу грубо говоря. Проблема в том, как я понял, что будут товары разных мастей которые имеют разные характеристики на корню. Вот автор и спрашивает как это все правильнее организовать.
Поэтому я посоветовал автору глянуть в сторону NoSql решений.

[Shaks]

Описывать каждую категорию товара в БД это не всегда правильно. Категорий может быть великое множество.

[Иван]

@shaks я предложил автору реализовать эти разные характеристики с помощью отдельных таблиц для каждой нетипичной модели/категории. Если таких отличий слишком много, то можно вынести все в отдельную таблицу для атрибутов с полями имя_атрибута, значение_атрибута, id_модели, id_категории и выводить их в зависимости от модели/категории.

[Shaks]

@0neS Это я понял. Автор как раз и спрашивал, писать подобные костыли или есть более изящное решение?

> Если таких отличий слишком много, то можно вынести все в отдельную таблицу для атрибутов с полями имя_атрибута, значение_атрибута, id_модели, id_категории и выводить их в зависимости от модели/категории.
Да. чтото типа того надо обдумывать.

[Иван]

@shaks то, что предложил автор, проблему далеко не решает. Обновил ответ выше.

[Shaks]

@0neS ну изначально ты описал именно то что автор и предложил, у тебя была таблица отвечающая за скорости байков и тд и тп.

[Иван]

@shaks вы правы, я сначала не правильно понял

[Дмитрий]

0neS, спасибо за ответ, попробую ваш пример.