Форум MySQL

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

Итак, каталог товаров. Каталог древовидный. Соображал я соображал и осенило впихнуть все дерево в 1 таблицу примерно так:

Поля: 1 - id 2 - id родительского уровня 3 - 4 - 5 - понятно 6 - количество в наличии +----+-----------+---------------+-------+---------+-----------+ | id | parent_id | name          | price | discont | existence | +----+-----------+---------------+-------+---------+-----------+ | 1  | null      | цветы         | null  | null    | null      | | 2  | null      | букеты        | null  | null    | null      | | 3  | null      | открытки      | null  | null    | null      | | 4  | null      | игрушки       | null  | null    | null      | | 5  | 1         | живые         | null  | null    | null      | | 6  | 1         | искусственные | null  | null    | null      | | 7  | 5         | комнатные     | null  | null    | null      | | 8  | 5         | срезка        | null  | null    | null      | | 9  | 8         | роза          | null  | null    | null      | | 10 | 8         | хризантема    | null  | null    | null      | | 11 | 8         | гербера       | null  | null    | null      | | 12 | 3         | свадьба       | null  | null    | null      | | 13 | 3         | день рождения | null  | null    | null      | | 14 | 12        | №1            | 25    | 0       | 5         | | 15 | 2         | "Нежность"    | 1200  | 10      | 1         | +----+-----------+---------------+-------+---------+-----------+

т.е. понятно, что зная только id товара (например 14), можно узнать не только его цену (25) и название (№1), но и иерархию каталога: Открытки -> Свадьба -> №1.
И понятно, что удобно в одной таблице иметь неограниченную вложенность.

И все бы было красиво и замечательно, пока ветви этого дерева не начинают пересекаться.
Ну например "id 9"/"роза". Ясно что это не макушка дерева, но продолжать не стал потому что

роза может быть: Эквадорская, Российская длинная, короткая красная, белая в любом сочетании

На самом деле вариантов намного больше, но для примера и по 2 штуки хватит.
Возникает вопрос! Как их хранить? В той же самой таблице перебирать все возможные варианты: Эквадор-длинная-красная, Эквадор-длинная-белая и т.д.? В этом случае пугает геометрическая прогрессия.
Либо для всех подобных случаев создавать новую таблицу, типа:

страна - enum(Россия, Эквадор, Колумбия, Кения) Рост - enum(40-60см, 70см, >=80см) Цвет - enum(красные, белые, желтые, розовые, другие) +цена, скидка, наличие

В этом случае пугает возможное количество таблиц. Особенно если делать по такой таблице в конце каждой ветки. А оно скорее всего так и будет. Но из первой таблицы тогда можно будет вообще убрать цену, скидку и наличие.

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

Но работать, мне кажется, удобнее будет с первым вариантом.

Кто что может посоветовать?

1. Пусть имеются в отдельной таблице свойства, "Место происхождения", со значениями "Эквадор", "Россия" и т.п. "Цвет" со значениями "красный", "белый", "желтый", "золотистый", "красно-коричневый", "Размер" со значениями "длинный", "короткий", "сферический в вакууме". Причем важно организовать эти свойства таким образом, чтобы они хранились в отдельных таблицах. Т.е. в таблице Имена храним название свойства и его первичный ключ, в таблице Значения храним значения свойства, указывая в специальном поле, первичный ключ из таблицы Имена.

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

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

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

не все понятно....
вот то что понял:

prop_names               prop_values +----+--------------     +----+--------------+--------- | id | name              | id | id_prop_name | value +----+--------------     +----+--------------+--------- | 1  | Производитель     | 1  | 1            | Россия | 2  | Цвет              | 2  | 1            | Эквадор | 3  | Рост              | 3  | 2            | Белый                          | 4  | 2            | черный                          | 5  | 3            | длинный                          | 6  | 3            | короткий has_prop +----+--------+------------- | id | id_tov | id_prop_name        +----+--------+------------- | 1  | 9      | 1 | 2  | 9      | 2   | 3  | 9      | 3

так?
И что с этим можно сделать? Я как-то уже на 3-х свойствах запутался. Конечно когда разберешься, наверно уж без разницы 3 их или 333...... Ну разбираемся дальше.... ))))

> В еще одной таблице будет сохраняться значения этих свойств.
Вот это вот самое непонятное! Как из этого теперь можно получить таблицу с 8-ю (все возможные варианты) сопоставлениями?

has_prop может и нужная таблица, но не обязательная, у вас должна быть какая-то такая таблица

tov_value --российская длинная белая роза +----+--------+----------+--------+ | id | id_tov | id_prop  | id_val | +----+--------+----------+--------+ | 1  | 9      | 1        | 1      | | 2  | 9      | 2        | 3      | | 3  | 9      | 3        | 5      |

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

Для данного примера прайс будет такой:
Эквадор, Длинные, Красные - 120р
Эквадор, Длинные, Белые - 110р
Эквадор, Короткие, Красные - 100р
Эквадор, Короткие, Белые - 90р
Россия, Длинные, Красные - 110р
Россия, Длинные, Белые - 100р
Россия, Короткие, Красные - 90р
Россия, Короткие, Белые - 80р

Если вы правильно меня поняли, а я не догоняю, то переведите этот прайс, плиз, в новую табличку? Я с конкретным примером быстрей разберусь.

Собственно, я привел для одного значения. Возможно не понятно, вот для всех значений

prop_names               prop_values  +----+--------------     +----+--------------+---------  | id | name              | id | id_prop_name | value  +----+--------------     +----+--------------+---------  | 1  | Производитель     | 1  | 1            | Россия  | 2  | Цвет              | 2  | 1            | Эквадор  | 3  | Рост              | 3  | 2            | Белый                           | 4  | 2            | Красный                          | 5  | 3            | длинный                           | 6  | 3            | короткий tov (товары) +----+--------+ | id | price  | +----+--------+ |  1 |   120р | -- Эквадор, Длинные, Красные - 120р |  2 |   110р | -- Эквадор, Длинные, Белые - 110р |  3 |   100р | -- Эквадор, Короткие, Красные - 100р |  4 |    90р | -- Эквадор, Короткие, Белые - 90р |  5 |   110р | -- Россия, Длинные, Красные - 110р |  6 |   100р | -- Россия, Длинные, Белые - 100р |  7 |    90р | -- Россия, Короткие, Красные - 90р |  8 |    80р | -- Россия, Короткие, Белые - 80р value (свойства) +----+--------+----------+--------+  | id | id_tov | id_prop  | id_val |  +----+--------+----------+--------+  | 1  | 1      | 1        | 2      |  | 2  | 1      | 2        | 4      |  | 3  | 1      | 3        | 5      | | 4  | 2      | 1        | 2      |  | 5  | 2      | 2        | 3      |  | 6  | 2      | 3        | 5      | | 7  | 3      | 1        | 2      |  | 8  | 3      | 2        | 4      |  | 9  | 3      | 3        | 6      | | 10 | 4      | 1        | 2      |  | 11 | 4      | 2        | 3      |  | 12 | 4      | 3        | 6      | | 13 | 5      | 1        | 1      |  | 14 | 5      | 2        | 4      |  | 15 | 5      | 3        | 5      | | 16 | 6      | 1        | 1      |  | 17 | 6      | 2        | 3      |  | 18 | 6      | 3        | 5      | | 19 | 7      | 1        | 1      |  | 20 | 7      | 2        | 4      |  | 21 | 7      | 3        | 6      | | 22 | 8      | 1        | 1      |  | 23 | 8      | 2        | 3      |  | 24 | 8      | 3        | 6      |

> tov_value --российская длинная белая роза  > +----+--------+----------+--------+  > | id | id_tov | id_prop  | id_val |  > +----+--------+----------+--------+  > | 1  | 9      | 1        | 1      |  > | 2  | 9      | 2        | 3      |  > | 3  | 9      | 3        | 5      |

Блин. Я так и подумал, что это описание одной строки.... Но не поверил сам себе! Этож ппц!!! Не, вы знаете, универсальность оно может и хорошо, но не такими же жертвами! Я свихнусь на таком каталоге =) Я ж не озон пишу =)

А кроме универсальности у такой модели есть плюсы? Может в скорости или в объеме данных? Универсальность весчь абстрактная =)))

>А кроме универсальности у такой модели есть плюсы? Может в скорости или в объеме данных?
Есть парочка достоинств. Перво наперво, скорее всего вам второй раз каталог больше никогда делать не придется, ну если вы его когда-нибудь доведете до ума и останетесь им довольны :))) Второе, если вам захочется иметь поиск как на яндекс-маркете, ну когда много-много разных параметров и ядра у процессора, и энергопотребление, и сокет, и производитель... то лучше все-таки рассмотреть этот вариант, особенно, если ввод новых товаров/параметров будет вашей зоной ответственности.

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

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

Но!

Я тут покурил, проветрился, кажется что-то начал понимать.....
Поправьте меня, если что-то не так:

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

Все верно, надеюсь???

Да, все верно, это только сначала дико кажется, на самом деле у вас добавляется 2, ну 3, ну максимум 5 таблиц... а ведь альтернатива по одной таблице на каждый вид товара, машина, роза, сотовый телефон потребуют свой собственной таблицы и своего собственного обработчика, будет ли проще? В начале наверняка, а потом позавидуете пяти пусть и хитросвязанным таблицам, но единому обработчику, который не требует повторного кодирования. Предполагается, что для этого каталога заполнение будет вестись через Web-интерфейс, а база данных будет строиться автоматически путем добавления новых свойств из системы администрирования. Да, придется поработать, но один раз в жизни :))), потому что такой каталог позволяет строить каталог даже не программисту. Знай себе свойства добавляй, да заполняй их потом. Мы нечто подобное хотели еще первой "PHP. Практике создания Web-сайтов описать", по разным причинам отказывались, то архитектура неудачная была на момент написания, то внутристудийное лобби побеждает: "а не жирно ли за бесплатно раздавать такие приложения" :))). Да и сложноватенько получается, и так ругают, что слишком сложные книги стали.

Самое интересное, я сейчас вспомнил, что самый первый сайтег, по скачке мелодий, у меня так и был устроен! Ну не совсем так, в смысле универсальности... Было грубо говоря 3 таблицы: буквы, авторы и песни и в каждой был только его личный id. А стыковались они все в 4ой таблице где так же были одни цифры! И как-то не пугало. Потом похоже насмотрелся, нахватался, расслабился )))

> Да, с наглядностью беда ваще.
В целом, когда понимаешь структуру, не такая уж и беда.... Несколько JOIN'ов и можно все слить воедино в ПМА )))

БОЛЬШОЕ СПАСИБО за идею и за разжовывание )))

А почему не объеденить имена и значения свойств в одну таблицу? Вот так

properties +----+-----------+--------- | id | id_parent | value +----+-----------+--------- | 1  | null      | Производитель | 2  | null      | Цвет | 3  | null      | Рост | 4  | 1         | Россия | 5  | 1         | Эквадор | 6  | 2         | Белый | 7  | 2         | черный | 8  | 3         | длинный | 9  | 3         | короткий

Ведь понятно, что где id_parent == null -это имя, а где цифра - это значение.

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

> has_prop может и нужная таблица, но не обязательная
Но все-таки нужная. Вот при заполнении БД, в самом начале, когда еще в таблице соответствий товаров и свойств нет ни одной строки и по этой таблице нельзя сделать выборку тех свойств, которые относятся к данному товару, как вывести страницу добавления товара? Например я добавляю в каталог розу, а свойства у меня есть и от открыток и от игрушек и от горшечных растений. Не выводить же их все, а потом разбираться какое отношение "влаголюбивость" имеет к открыткам. Кроме того Свойства могут быть с одним именем: Производитель для игрушек - фабрика заря, а для открыток - дизайн студия Лето. Не выводить же их все в одном выпадающем списке. И даже когда таблица соответствий заполнена, зачем к такой массивной дуре делать ненужные запросы. Я правильно рассуждаю?

Возник еще один вопрос, не вписывающийся в универсальность.
А если у товара появляется списочное свойство, которое само имеет несколько свойств?
Ну например "роза красная эквадор" и у нее есть 10 фоток. Но это не просто урлы, но у каждой есть еще название (напримар сорт), дата добавления и какое-то описание.
Тогда на каждое такое свойство доп. таблицу заводить?

Фотки вообще лучше отдельно организовывать, есть сущность (статья, отзыв, пользователь, товар), все они могут иметь фотографии, путь к которым хранится в одной таблице. Есть координирующая таблица, которая хранит идентификаторы сущностей, к которым фотографии прикрепляются... т.е. хотите работать с фотографиями каталога пишите

$id = block("catalog");

для новостей

$id = block("news");

для пользователей

$id = block("user");

Функция block() сама возвращает идентификатор блока, если такого блока нет, она его заводит и назначает ему идентификатор по auto_increment. Имена чем хороши, вы можете вводить пространства имен, скажем "catalog.flowers.rose" или "catalog.523". В функции же вы можете имена разбирать, вводить псевдонимы и т.п.

В результате у вас таблица фоток будет иметь примерно следующую структуру
id_photo, path, alt, name, description
Будет таблица, которая сообщает какие фотографии каким блокам принадлежат
id_photo, id_block

А уже для каждого конкретного блока вы задаете специальную организующую таблицу, которая связывает id_photo с идентификатором сущности по связи один к одному или один к многим. У каждого блока своя логика, пользователю может одна фотография нужна, а розам десять... но факт остается фактом, за связь группы таблиц фотографий и скажем группы таблиц товарных позиций (группы таблиц новостей, пользователей и т.п.) нужна будет отдельная таблица (её разрядность: один к одному, один ко многим будет определять количество фотографий на позицию).

По аналогии с этим могут организовываться комментарии и вообще любые объекты сайта, которые используются не одним, а сразу несколькими блоками. Осторожно: вы теряете гибкость, однако, зверски экономите на разработке обслуживающей инфраструктуры - времени на неё потратите вагон, но тоже один раз. Кроме того, не лишним будет ввести cron-задание, которое будет обходить структуру и удалять те, количество связей которых равно 0.

PS Возможно вы немного не то имели в виду, но фотографии слишком уж объект часто-используемый, мозг не поднимается думать в обобщении их лишь в рамках одного блока.

> PS Возможно вы немного не то имели в виду
Возможно и не то, но эта информация тоже будет совсем не лишней! Спасибо.
А что я имел ввиду, я сформулирую еще.... Когда сам разберусь )

В принципе ошибку я понял. Я зациклился на вашей фразе "у вас добавляется 2, ну 3, ну максимум 5 таблиц". Или не правильно ее понял. На самом деле 5 таблиц - это сама структура каталога. А доп информация - это еще куча таблиц, как надстройка над "базовым классом" каталогом. Верно?

И еще. У нас (в таблицах выше) нигде нет связи товара с каталогом. Если товар в 1ом разделе каталога, то связь можно и прямо в таблицу товаров добавить: 1 товар -> 1 раздел каталога. А если он во многих разделах, что лучше, дублировать товар и все его свойства но с другим каталогом или добавить еще одну таблицу связей товар -> каталог ?

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

В голове уже не помещается, а это еще только организация БД. Значит нужно, как вы говорите, ЧЕРТИТЬ (проектировать, рисовать связи). В связи с этим хочу спросить, вы для проектирования используете ручку и листок или какую-то программу удобную?

>В голове уже не помещается, а это еще только организация БД. Значит нужно, как вы говорите,
>ЧЕРТИТЬ (проектировать, рисовать связи). В связи с этим хочу спросить, вы для проектирования
>используете ручку и листок или какую-то программу удобную?
Хотел по-ехидничать :))), что следующим вашим вопросом будет, где держать эту гигантскую схему и тут же дать ответ на этот вопрос, сдержался, видать зря :))). Смотрите в сторону ErWin (только учтите, что он платный и весит больше 400Мб), позволяет не только рисовать схемы, но и проектировать базу, разбивать таблицы на подгруппы, так что можно отобразить или распечатать отдельную подгруппу "каталог", "пользователи", "параметры сайта", "фотографии", т.е. можете разбить базу данных на произвольное количество подгрупп и работать с ними отдельно. Понятно, что и всей схемой он тоже работает, и понятно, что она визуальная и позволяет прослеживать связи таблиц вплоть до того, какой ключ таблицы связан с каким ключом другой таблицы. Кроме того он генерирует рабочий дамп для MySQL (раньше его ценили за возможность комментирования, но это сейчас MySQL штатно позволяет делать).

Ок. Сенькс!

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

> AllFusion ERwin Data Modeler 7.3 (ERwin) + ERwin Validator 7.3
Это оно?

Да похоже, один должен быть 15 Мб, другой - 402 Мб.

Так и есть )
> CAEDM73-b1666.exe 401.65 MB (421168313)
> CAEDMV73-b5719.exe 15.34 MB (16085865)

Буем качать )

>В принципе ошибку я понял. Я зациклился на вашей фразе "у вас добавляется 2, ну 3, ну
>максимум 5 таблиц". Или не правильно ее понял. На самом деле 5 таблиц - это сама структура
>каталога. А доп информация - это еще куча таблиц, как надстройка над "базовым классом"
>каталогом. Верно?
Да, в большом проекте таблиц все-равно будет под сотню, но они не будут иметь тенденцию расти к тысячам просто за счет ввода новых подразделов каталога.

>И еще. У нас (в таблицах выше) нигде нет связи товара с каталогом. Если товар в 1ом разделе
>каталога, то связь можно и прямо в таблицу товаров добавить: 1 товар -> 1 раздел каталога. А
>если он во многих разделах, что лучше, дублировать товар и все его свойства но с другим
>каталогом или добавить еще одну таблицу связей товар -> каталог ?
Потому, что изначально связать была один ко многим (я так понял, именно она была нужна), много товаров в каталоге и товар принадлежит одному каталогу. Но действительно можно организовать связь многие ко многим, как вы пишите, тогда потребуется таблица связей, а в таблицу товаров не плохо добавить счетчик ссылок, а еще лучше под это дело завести отдельную таблицу, которую заполнять по cron - тогда товары, которые бы остались с 0 ссылок можно было бы удалять и у вас не происходило бы захламление каталога (так и сборщики мусора себя ведут и операционные системы и сложным каталогам это тоже никогда не вредит).

>Пока мысли в голове - путаются. Когда начинаешь писать - проясняется ) Вот щас пишу
>предыдущий абзац, а сам понимаю, что очевидно там нужно применить второе решение ) Но
>все же уточню.... Верно??? )
От задачи зависит, но вообще с связь многие ко многим (то что вы предложили, когда товар может быть в нескольких каталогах одновременно) более универсальна (и как ни странно более эффективная по скорости - просто эта промежуточная таблица состоит из одних чисел и она получается очень быстрой, главное суметь этим воспользоваться).

> а в таблицу товаров не плохо добавить счетчик ссылок
не очень понял что имеется ввиду под счетчиком ссылок?

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

PS Понятное дело, что все реализовывать не обязательно, или не обязательно реализовывать сразу, иначе будет долгострой, а у вас вероятно какие-то сроки имеют место быть :)))

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

Все, пока вроде все ясно. Есть над чем подумать! Еще раз спасибо!

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

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

Теперь не могу сообразить, как организовать составной товар?

Вот например букет:
У него есть базовые свойства - название, описание, фотографии, цена работы флориста, что-то еще.
Также в каталоге будут "открытые" товары (объекты): розы, хризантемы и т.п. И закрытые (для внутреннего использования) объекты: лента, бумажка, травка и т.п.
И есть у букета состав.
Хочу сделать именно состав не текстовый, а из объектов этого же каталога, чтоб можно было динамически его модифицировать (у букета будет несколько вариантов размеров), пересчитывать цену (например травка подорожала - не править же всю базу по каждому букету) и т.п.

Т.е. объект букет выглядит как-то так:

название => "sajdf"; описание => "werlklejgdfs"; фотографии => array(1,2,3,4,5); состав => array (       1 => array ('id_obect' => 1, 'count' => 7),       2 => array ('id_obect' => 17, 'count' => 3),       3 => array ('id_obect' => 24, 'count' => 1),       4 => array ('id_obect' => 11, 'count' => 1.3),     );

Как это можно организовать, подскажите?
_______
P.S. Ваше замечание
> 3. Если все вышеописаное вам показалось слишком простым, то не плохо будет хранить
> числовые, тестовые и списочные свойства в разных таблицах (указывая при создании
> свойства, каким значением оно обладает числовым, тестовым или списочным).
я принял на вооружение и типы свойств решил таки разделить ) Но "это" вписать не получается как-то.

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