Доброго времени суток всем!
Возник вот какой вопрос:
Есть электронный магазин, который торгует всякой всячиной. Есть в базе табличка Products, где содержится вся инфа по позициям.
Каждую категорию товара можно охарактеризовать общими для всех товаров свойствами (id, название, изображение, цена...), и свойствами, индивидуальными для каждой категории
(для телеков это диагональ, частота развертки..., для телефонов — наличие bluetooth, фотокамеры..., ну и т.д.) для этого у меня в таблице Categories есть поле detailsTable,
в которое кладется название таблицы с индивидуальными для категории параметрами, а в таблице Products — categoryID, ссылающееся на id категории. Вот не знаю, как
теперь из хранимки все это вытаскивать. на входе процедуры — id категории. Нужно на выходе получить некоторые поля из Products плюс все поля из соответветствующей этой категории
detailsTable. Не представляю, можно ли это сделать средствами SQL? Или придется в клиентском коде запросы генерить (что не хотелось бы)? И вообще, как такие задачи решаются, помогите советом плиз?
Здравствуйте, strelochnik, Вы писали:
S>Каждую категорию товара можно охарактеризовать общими для всех товаров свойствами (id, название, изображение, цена...), и свойствами, индивидуальными для каждой категории
Абсолютно такую же задачу решаю сейчас, хотя намного больше видов объектов.
Классика:
Products(ProductID[PK],<необязательно> ProdTypeID(FK:ProductTypes), Name, Price, Picture)
ProductsTVAttr(ProductID[PK,FK:Products), ScreenSize, ScanningFreq)
ProductsCellPhoneAttr(ProductID[PK,FK:Products), HasBT, HasCamera, ColorDepth)
<необязательно>
ProductTypes(ProdTypeID[PK], Name)
Вытаскивать:
SELECT *
FROM
Products as p
INNER JOIN
ProductsTVAttribs as ptv
ON ptv.ProductID = p.ProductID
Если важно, чтоб в телевон не попали атрибуты телевизора, то TypeID добавляешь во все таблицы и ключём будет (ProductID, ProdTypeID) а в таблицах Products[...]Attr делаешь на TypeID ограничение.
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Здравствуйте, strelochnik, Вы писали:
S>Доброго времени суток всем! S>Возник вот какой вопрос: S>Есть электронный магазин, который торгует всякой всячиной. Есть в базе табличка Products, где содержится вся инфа по позициям. S>Каждую категорию товара можно охарактеризовать общими для всех товаров свойствами (id, название, изображение, цена...), и свойствами, индивидуальными для каждой категории S>(для телеков это диагональ, частота развертки..., для телефонов — наличие bluetooth, фотокамеры..., ну и т.д.) для этого у меня в таблице Categories есть поле detailsTable, S>в которое кладется название таблицы с индивидуальными для категории параметрами, а в таблице Products — categoryID, ссылающееся на id категории. Вот не знаю, как S>теперь из хранимки все это вытаскивать. на входе процедуры — id категории. Нужно на выходе получить некоторые поля из Products плюс все поля из соответветствующей этой категории S>detailsTable. Не представляю, можно ли это сделать средствами SQL? Или придется в клиентском коде запросы генерить (что не хотелось бы)? И вообще, как такие задачи решаются, помогите советом плиз?
Такие задачи решаются при помощи паттерна проектирования баз данных Entity-Attribute-Value. Я только что закончил проектирование базы данных для Интернет-магазинов с использованием этого паттерна.
В простейшем виде структура БД для товаров выглядит примерно так:
содержит описания атрибутов (свойств), которые могут иметь товары. Например, это могут быть "Цвет" (для одежды), "Диагональ" (для телевизоров) и так далее. Атрибут имеет тип, например, текстовый или числовой.
предназначена для моделирования связи "многие-ко-многим" между таблицами Product и Attribute. То есть какие атрибуты разрешено использовать для каких товаров + свойства конкретного атрибута для конкретного товара (например, в каком виде значения этого атрибута показываются пользователю).
содержит конкретные значения атрибутов товаров. Я использую SQL Server 2008 и столбец Attribute_Value имеет тип sql_variant. Это позволяет хранить в одном и том же столбце значения различных типов. При этом нужно следить, чтобы товарам не были присвоены значения атрибутов, которые не разрешены для этого товара. (Это можно реализовать при помощи триггера, который при вставке данных проверяет их правильность на основе данных из таблицы Product_Attribute).
Атрибуты и их значения для конкретного товара можно извлечь простым запросом:
select attr.Name as Attribute_Name,
attr_val.Value as Attribute_Value
from Attribute attr inner join Product_Attribute_Value attr_val
on attr.Attribute_Id = attr_val.Attribute_Id
where attr_val.Product_Id = <...>
Значение типа sql_variant потом можно преобразовать в нужный тип в программе-клиенте.
Кроме этого, можно легко искать товары, то есть выполнять запросы типа "Найти все товары где Цвет=Красный и Размер=XL". Для этого используется простой запрос с использованием реляционного деления.
Конечно, это очень упрощенная схема. На самом деле все выглядит сложнее сложнее. Например, в моей базе более 30 таблиц предназначено для хранения метаданных — описаний атрибутов и их значений. Атрибуты имеют различные типы (String, int и так далее) и каждому типу соответствует своя таблица. Кроме этого, наша БД многоязыковая и для значений многих атрибутов хранятся также их переводы на все используемый языки и т.д.
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, strelochnik, Вы писали:
S>>Каждую категорию товара можно охарактеризовать общими для всех товаров свойствами (id, название, изображение, цена...), и свойствами, индивидуальными для каждой категории ZAM>Абсолютно такую же задачу решаю сейчас, хотя намного больше видов объектов. ZAM>Классика: ZAM>Products(ProductID[PK],<необязательно> ProdTypeID(FK:ProductTypes), Name, Price, Picture) ZAM>ProductsTVAttr(ProductID[PK,FK:Products), ScreenSize, ScanningFreq) ZAM>ProductsCellPhoneAttr(ProductID[PK,FK:Products), HasBT, HasCamera, ColorDepth) ZAM><необязательно> ZAM>ProductTypes(ProdTypeID[PK], Name)
ZAM>Вытаскивать: ZAM>
ZAM>SELECT *
ZAM>FROM
ZAM>Products as p
ZAM>INNER JOIN
ZAM>ProductsTVAttribs as ptv
ZAM>ON ptv.ProductID = p.ProductID
ZAM>
ZAM>Если важно, чтоб в телевон не попали атрибуты телевизора, то TypeID добавляешь во все таблицы и ключём будет (ProductID, ProdTypeID) а в таблицах Products[...]Attr делаешь на TypeID ограничение.
Такая структура неудобна для практического использования, потому что для каждого нового типа товаров нужно создавать отдельную таблицу.
ZAM>SELECT *
ZAM>FROM
ZAM>Products as p
ZAM>INNER JOIN
ZAM>ProductsTVAttribs as ptv
ZAM>ON ptv.ProductID = p.ProductID
ZAM>
Спасибо. Проблема в том, что перед тем как выполнять запрос, нужно вычислить эту самую detailsTable (ProductsTVAttribs, ProductsTVAttribs...) по CategoryID
положить ее в переменную, и эту переменную каким-то образом использовать в вышеприведенном запросе — вот я и сомневаюсь, что такое возможно средствами sql
Т.е. при таком подходе уж очень неудобно выковыривать данные. Пожалуй, паттерн EAV, который посоветовал Flying Dutchman — то что нужно. Попробую его внедрить )
Здравствуйте, Flying Dutchman, Вы писали:
FD>Такая структура неудобна для практического использования, потому что для каждого нового типа товаров нужно создавать отдельную таблицу.
Невижу ничего неудобного в создании таблицы. Вроде БД из таблиц состоит. Насколько мне известно, все популярные движки БД, позволяют запросом создать таблицу и связь. Всёравно надо генерить отображение, так почему же не сгенерить и таблицу.
И где сказано, что каждый день будут новые типы товаров создаваться? Есть какое-то множество типов и их используют постоянно, изредка добавляя новые. Использование обобщённой таблицы/таблиц наоборот сложнее, т.к. данные получаются не струтурированные в схеме а сваленные в кучу в несколько таблиц. Да и при больших объёмах, данная схема начинает тормозить, т.к. на каждый экземпляр товара надо N-записей со значениями параметров создавать, вместо одной. И подгрузка параметров осуществляется слиянием двух уникальных кластерных индексов, что, как-раз, мега-быстро.
Хотя на малых объёмах (у меня-то 64-битный индекс нужен, т.к. в 32-битный не влезет всё) так можно.
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Здравствуйте, strelochnik, Вы писали:
S>Спасибо. Проблема в том, что перед тем как выполнять запрос, нужно вычислить эту самую detailsTable (ProductsTVAttribs, ProductsTVAttribs...) по CategoryID
Если тебе нужна таблица по всем товарам и всем значениям атрибутов которые у них есть, то конечно да. Но, прости, не понимаю зачем тебе такая таблица? Количество столбцов в ней будет равно сумме количеств всех типов атрибутов у всех типов товаров — это же кошмар как много. Обычно же, в магазине выбираешь категорию и тебе выводят таблицу с параметрами данной категории. Или я ищу товары, а мне в результирующем списке выводят список основных параметров со ссылками на страницы с полной детализацией. :-) Да и для ввода в поиске значений спец-параметров, надо всёравно генерить поля ввода...
Ну м.б. надо сделать выборку (Имя, Тип, Атрибуты), где Атрибуты="атрибут_имя_1=значение; ...; атрибут_имя_N=значение" тогда EAV прощще.
S>положить ее в переменную, и эту переменную каким-то образом использовать в вышеприведенном запросе — вот я и сомневаюсь, что такое возможно средствами sql :xz:
Есть таблица категорий, человек выбирает из списка интересующую категорию, ID-этой категории определяет какой хранимкой получить список товаров с их параметрами, легко написать генератор таких хранимок. В сущности отличие только в том с какой таблицей сделать INNER JOIN.
S>Т.е. при таком подходе уж очень неудобно выковыривать данные. Пожалуй, паттерн EAV, который посоветовал Flying Dutchman — то что нужно. Попробую его внедрить )
Если объёмы не большие, то всё будет работать нормально. Я не спорю, просто я никогда не считал что сложность БД определяется количеством таблиц, неумелыми руками можно и 10 таблиц превратить в кашу.
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, strelochnik, Вы писали:
S>>Спасибо. Проблема в том, что перед тем как выполнять запрос, нужно вычислить эту самую detailsTable (ProductsTVAttribs, ProductsTVAttribs...) по CategoryID ZAM>Если тебе нужна таблица по всем товарам и всем значениям атрибутов которые у них есть, то конечно да. Но, прости, не понимаю зачем тебе такая таблица? Количество столбцов в ней будет равно сумме количеств всех типов атрибутов у всех типов товаров — это же кошмар как много. Обычно же, в магазине выбираешь категорию и тебе выводят таблицу с параметрами данной категории. Или я ищу товары, а мне в результирующем списке выводят список основных параметров со ссылками на страницы с полной детализацией. Да и для ввода в поиске значений спец-параметров, надо всёравно генерить поля ввода... ZAM>Ну м.б. надо сделать выборку (Имя, Тип, Атрибуты), где Атрибуты="атрибут_имя_1=значение; ...; атрибут_имя_N=значение" тогда EAV прощще.
S>>положить ее в переменную, и эту переменную каким-то образом использовать в вышеприведенном запросе — вот я и сомневаюсь, что такое возможно средствами sql ZAM>Есть таблица категорий, человек выбирает из списка интересующую категорию, ID-этой категории определяет какой хранимкой получить список товаров с их параметрами, легко написать генератор таких хранимок. В сущности отличие только в том с какой таблицей сделать INNER JOIN.
S>>Т.е. при таком подходе уж очень неудобно выковыривать данные. Пожалуй, паттерн EAV, который посоветовал Flying Dutchman — то что нужно. Попробую его внедрить ) ZAM>Если объёмы не большие, то всё будет работать нормально. Я не спорю, просто я никогда не считал что сложность БД определяется количеством таблиц, неумелыми руками можно и 10 таблиц превратить в кашу.
извиняюсь — скайлинк тупит по вечерам
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, strelochnik, Вы писали:
S>>Спасибо. Проблема в том, что перед тем как выполнять запрос, нужно вычислить эту самую detailsTable (ProductsTVAttribs, ProductsTVAttribs...) по CategoryID ZAM>Обычно же, в магазине выбираешь категорию и тебе выводят таблицу с параметрами данной категории.
Правильно — вот я и хочу выцепить содержимое таблички, соответствующей определенной категории товаров, идентификатор которой я передаю в процедуру. ZAM> Да и для ввода в поиске значений спец-параметров, надо всёравно генерить поля ввода...
все генерится — код цепляет из xml как и что генерить, но это уже отдельная история )
ZAM>Есть таблица категорий, человек выбирает из списка интересующую категорию, ID-этой категории определяет какой хранимкой получить список товаров с их параметрами, легко написать генератор таких хранимок. В сущности отличие только в том с какой таблицей сделать INNER JOIN.
Генераторы — это понятно — примерно так и сделано, просто хотелось покрасивее одной хранимкой разрулить.
Здравствуйте, strelochnik, Вы писали:
S>Генераторы — это понятно — примерно так и сделано, просто хотелось покрасивее одной хранимкой разрулить.
Ну раз так сильно хочется одной хранимкой, то оно конечно пожалуйста. Хотя у меня тож одна хранимка: даю ей Type, а она в IF'е выбирает с чем джойнить — собсно эта хранимка и догенеряется по мере попадания новых категорий. :-) Но правда день на написание шаблона хранимки и генератора её и таблиц пришлось потратить. Ну да ладно... если тормозить буит — пиши. :)
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, Flying Dutchman, Вы писали:
FD>>Такая структура неудобна для практического использования, потому что для каждого нового типа товаров нужно создавать отдельную таблицу. ZAM>Невижу ничего неудобного в создании таблицы. Вроде БД из таблиц состоит. Насколько мне известно, все популярные движки БД, позволяют запросом создать таблицу и связь. Всё равно надо генерить отображение, так почему же не сгенерить и таблицу.
Таблицы, конечно, очень просто создавать. Даже тысячами. Только сопровождать будет сложнее.
Для примера, у нас одна и та же структура базы данных используется для десятков (или сотен) веб-магазинов. Все магазины разные — начиная от мебельного и кончая магазином для аквалангистов. Если для каждого магазина создавать свои таблицы, то это будет полный хаос, который невозможно будет поддерживать.
ZAM>И где сказано, что каждый день будут новые типы товаров создаваться? Есть какое-то множество типов и их используют постоянно, изредка добавляя новые. Использование обобщённой таблицы/таблиц наоборот сложнее, т.к. данные получаются не струтурированные в схеме а сваленные в кучу в несколько таблиц.
Наоборот, получается проще. Мы имеем фиксированную структуру базы. Данные извлекаются простыми запросами. Если для каждого вида товара создавать свою таблицу, то придется писать больше различных запросов и использовать динамический SQL, что усложняет программирование.
ZAM>Да и при больших объёмах, данная схема начинает тормозить, т.к. на каждый экземпляр товара надо N-записей со значениями параметров создавать, вместо одной. И подгрузка параметров осуществляется слиянием двух уникальных кластерных индексов, что, как-раз, мега-быстро. ZAM>Хотя на малых объёмах (у меня-то 64-битный индекс нужен, т.к. в 32-битный не влезет всё) так можно.
У нас такая схема работает нормально, не тормозит (в некоторых магазинах несколько десятков тысяч различных товаров).
Здравствуйте, ZAMUNDA, Вы писали:
ZAM>Здравствуйте, strelochnik, Вы писали:
ZAM>если тормозить буит — пиши.
магазинчик молодой еще — категорий пока немного — самое время опробовать EAV на нем — интересно просто.
А разрастется — начнет тормозить — всегда есть вариант на генераторах
Здравствуйте, strelochnik, Вы писали:
S>Здравствуйте, ZAMUNDA, Вы писали:
ZAM>>Здравствуйте, strelochnik, Вы писали:
ZAM>>если тормозить буит — пиши.
S>магазинчик молодой еще — категорий пока немного — самое время опробовать EAV на нем — интересно просто. S>А разрастется — начнет тормозить — всегда есть вариант на генераторах
У нас с EAV (простая версия) работают десятки (или даже сотни) магазинов, так что можешь смело пробовать. Сейчас разрабатываем новую версию базы данных на основе более сложной (усовершенствованой) EAV-модели, но снижения производительности не ожидаем.
Спасибо! кажется это то, что что мне нужно. Пойду разбираться 
Да и для ввода в поиске значений спец-параметров, надо всёравно генерить поля ввода... 