Прошу совета по построению базы данных в MS SQL Server 7.0.

Игорь
Дата: 13.12.2000 21:11:17
Друзья помогите, стоит реальная задача, не знаю, как подойти. Создаю базу данных в MS SQL Server 7.0, пишу прогу на делфях.
Вот задача:
Есть таблица изделий, каждое изделие может быть описано свойствами. Вот тут и начинаются проблемы,
свойств у изделия может быть разное количество, где-то от 2 до 100 свойств.
Все свойства хранятся в таблице-справочнике свойств изделий.
Как мне в таблице изделий описать такое изделие (особенно его свойства), какие поля и типы данных создавать, а может создать
какие нибудь дополнительные таблицы и т.д.?
И еще нужно будет производить выборку изделий в базе данных по набору свойств, допустим, есть 5 или 10 свойств и мне
нужен список всех изделий, у которых эти свойства есть. Как мне передавать в процедуру список
свойств для выборки (не делать же процедуру с сотней входных переменных)?
Если кто сталкивался с подобными проблемами,
намыльте алгоритмчик построения такой базы данных или идею, плиз, на Dubodeloff@mail.ru

Дубоделов Игорь

P.s.
Вообще идеально было бы иметь в распоряжении динамические массивы.
baxxtor
Дата: 13.12.2000 23:02:33
как вариант:

Заведи таблицу значений свойств (таблица представлена логически, чтобы показать в какую сторону думать):

table values(
value_id <ID> <pk>,
value_type <enum>, -- 1 - integer, 2 - string
str_val <string>,
int_val <integer>,
....
)

value_type показывет какое именно значение в записи "играет".

Далее таблица свойств:

table properties(
property_id <ID> <pk>,
property_name <string>
)

таблица изделий:

table products(
product_id <ID> <pk>,
product_name <string>
)

table products_properties(
product_id <ID> <pk,fk>,
property_id <ID> <pk,fk>,
value_id <ID> <fk>
)

Здесь pk - первичныей ключ, fk - внешний (foreign) ключ. на какие поля/таблицы сделаны внешние ключи я надеюсь объяснять не надо.
Игорь
Дата: 14.12.2000 07:04:55
А можно поподробнее о логической таблице(значений свойств) table values, что она делает, спасибо.
Спасибо.
Игорь.
Fompro
Дата: 14.12.2000 08:18:02
Строится кросс-таблица изделие-свойства, где свойства: Property
(Id,
Name,
Other Information ...
CONSTRAINT PRIMARY KEY Id)
Изделие: Products
(Id,
Other Information ...
CONSTRAINT PRIMARY KEY Id)
изделие-свойства:
Id_Prod,
Id_Prop,
FOREIGN KEY Id_Prod REFERENCE Products (Id),
FOREIGN KEY Id_Prop REFERENCE Property (Id))
Т.о. Вы получаете отношение многие-ко-многим.
Вы можете исп-ть бинарную запись ключей (флагов) в соотв. поле табл. изделий, соотв. Работа происходит по маске - р.е.
SELECT ... FROM Products WHERE flags&0x3=0x3.
В последнем случае, для выборки в хр.пр-ре Вам надо будет передавать только маску. Кстати, не знаю как в Дельфи, но в PowerBuilder набор св-ств очень красиво переводится в набор CheckBox'ов.
Можете посмотреть в Technet статью "Maximizing Performance Using Binary Columns and Bitwise Operations in MS SQL Server"
Oleg F
Дата: 14.12.2000 12:05:54
Таблица записей вида <код_изделия, название_изделия,....>
и таблица записей вида <код_изделия, код_свойства, значение_свойства>.

Первая таблица связана со второй отношением "один-ко-многим" по полю код_изделия.
У второй таблицы поле код_свойства контролируется по словарю свойств (с помощью foreign key).
Аналогично контролируется связь между описанными двумя таблицами по коду свойства.
baxxtor
Дата: 14.12.2000 18:50:00
все очень просто таблиза содержит значения ваших свойств. Скажем имеет сл. строки

ID value_type str_val int_val date_val
=== ============ ========= ========= ==========
1 1 'string' null null
2 2 null 123 null
3 2 null 345 null
4 3 null null '2/2/2001'

вот так это должно выглядеть.
VadimB
Дата: 15.12.2000 11:42:41
А как при такой структуре данных
"... производить выборку изделий в базе данных по набору свойств, допустим, есть 5 или 10 свойств и мне
нужен список всех изделий, у которых эти свойства есть. Как мне передавать в процедуру список
свойств для выборки (не делать же процедуру с сотней входных переменных)?"
baxxtor
Дата: 16.12.2000 05:49:25
...но похоже сейчас сделаю. Народ ну не могу я все решить, надо и тому, кому задача поставленна головой подумать. итак. как искать? вариант следующий (первое, что пришло в голову за 10 минут):

делается сохраненная процедура с одним входным параметром (строковым). Возвращать она будет рекордсет. Итак, в строковый параметр помещается строка вида: "1,3,7,111" Это не что иное как индексы тех свойств которые должны быть у продукта, чтобы он попал в результат.

Далее на первом этапе эта строчка парсится и пишется во временную таблицу:

table #temp_index(
property_id <ID>
)

далее пишется _один_ селект с джойном на таблицу products_properties который возвращает product_id, sum(property_id) и делает групировку по product_id. Что мы теперь получили? Правильно! ИД продукта и сколько свойств (число) из требуемых совпало.

продукт сумма
========= =======
1 3
2 1
>4 4
>7 4

Что теперь надо сделать? Правильно оставить только те строки которые имеют сумму свойств равному числу свойств во входящем параметре (в нашем случае - 4). Т.е. в этот селект надо добавить что-то типа having sum(property_id) = @prop_number

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

Надеюсь, что ответил достаточно подробно.
Garya
Дата: 16.12.2000 11:14:31
Мне приходилось решать аналогичную задачу. Вкратце идеи таковы.
Номенклатурный справочник делается иерархическим. Как к узлам дерева, так и к его листьям привязываются характеристики. Естественно, имеется и справочник единиц измерений. Кажый лист дерева номенклатурного справочника обязан иметь выражение в некой базовой единице измерений - к этой единице производится приведение любого прихода/расхода. Имеется и специальная структура, позволяющая переводить единицы измерений, которая делится на две подструктуры - взаимосвязь между единицами измерений, не зависящими от номенклатуры (например, 1 кг = 1000 г) и единицы измерения, выражение которых зависит от номенклатуры (например, 1 упаковка = 100 коробок, но только такой-то номенклатуры), а также связь между теми и этими (1кг = 50 коробок такой-то номенклатуры = 1000 шт такой-то номенклатуры).
В отдельной таблице хранится полный перечень наименований всех характеристик, которые могут быть задействованы в системе (естественно, справочник обновляемый). Для каждого наименования задается ТИП характеристики.
Всего могут быть три типа характеристик - числовые, перечисление и множество.
Для числовых характеристик дополнительно задается базовая единица измерения. Числовые характеристикы выражаются некоторым числовым значением. Например, могут существовать в этом перечне характеристик такие: "Мощность" (ед.изм "Ватт"), Масса (Ед.изм "кг"), Высота (ед.изм. "м") и т.п. Значения числовых характеристик могут быть заданы только в единицах измерения, не зависящих от номенклатуры и только в тех величинах, которые приводятся к базовой единице измерения (например, масса может быть задана также в тоннах или в граммах, которые автоматически пересчитываются в базовую - кг).
Для характеристик типа "Перечисление" задается перечень значений (строковых), которые она может принимать. Особенность этой характеристики - значение выбирается ОДНО из введенного перечня значений. К характеристикам подобного вида можно отнести, к примеру "Цвет". Его значение может выбираться из перечня "Белый", "Черный", "Серебристый", ... и т.д. Единица измерения для таких характеристик, естественно, не задается. Если значение такой характеристики для некоторой номенклатуры определено, то оно обязано содержать ссылку на РОВНО ОДНО значение из предварительно заданного перечня значений (не больше и не меньше).
Характеристики типа "Множество" имеют много общего с "Перечислением" - для них так же вводится перечень допустимых значений. Однако, ЗНАЧЕНИЕ подобной характеристики задается как СОВОКУПНОСТЬ ссылок на различные составляющие заранее заданного перечня значений. Эта совокупность может быть и пустой, а может сожержать и более одного значения из заранее подготовленного перечня значений. К характеристикам такого типа может относиться, к примеру "Область применения", имеющая перечень значений "Химическая промышленность", "Машиностроение", "Водоснабжение", "Торговля",... и т.д. В номенклатурном справочнике может находиться оборудование, которое находит применение одновременно в нескольких областях.
Наверное, нет смысла объяснять, что перечень возможных значений привязывается к наименованию характеристики с помощью связи "один-ко-многим".
И еще нюансы. Если номенклатурный справочник будет НЕ иерархическим, то я не завидую тому юзеру, кто будет заколачивать его содержимое вместе со всеми характеристиками. Для иерархического справочника номенклатуры необходимо предусмотреть механизм НАСЛЕДОВАНИЯ характеристик от родительской номенклатурной группы с одновременной возможностью его подправить. Что это такое? Необходимо как для конкретной номенклатуры (лист дерева), так и для номенклатурной группы (узел дерева) иметь возможность задать:
- Перечень характеристик, которые имеют место для данной номенклатуры или номенклатурной группы (в виде ссылок на справочник наименований характеристик).
- Перечень значений характеристик (для числовых - числа, для остальных ссылки на на значения из перечня возможных значений).
Необходимо четко понимать, что может быть зафиксированы два разных факта: 1) Для некоторой номенклатурной группы определен перечень НАИМЕНОВАНИЙ характеристик, но не заданы их ЗНАЧЕНИЯ. Значения задаются уже для конкретной номенклатуры или для номенклатурных групп более низкого уровня. 2) Для некоторой номенклатурной группы могут заданы как НАИМЕНОВАНИЯ, так и ЗНАЧЕНИЯ характеристик. В этом случае все дочерние элементы данной группы автоматически получают заданные на данном уровне ЗНАЧЕНИЯ определенных характеристик.
Как работает наследование? Допустим, в номенклатурный справочник заводится номенклатурная группа "Насосы" (узел дерева). Для данной номенклатурной группы задается перечень характеристик: "Принцип действия" (вибрационный, центробежный, помповый и т.д.), "Область применения" (см.выше), "Глубина всасывания" (числовая, м), "Подача" (куб.л/мин). Естественно, ЗНАЧЕНИЯ этих характеристик НЕ задаются. Далее в виде дочерней группы в данную группу помещается "Насосы ЭЦВ". В момент помещения этой группы в справочник она НАСЛЕДУЕТ перечень характеристик родительской группы (представляете, что было бы, если бы его пришлось повторно наколачивать?). Это значит, что для группы "Насосы ЭЦВ" автоматически создается копия всех ссылок со ссылок родительской группы. Далее конкретно для этой группы перечень характеристик можно подправить. Можно дополнить его характеристиками, которые имеет только эта группа насосов, например "Исполнение" (взрывозащищенное, морозостойкое,... и т.д.). А для некоторых характеристик, ЗНАЧЕНИЕ которых одинаково для всей номенклатурной группы, можно задать и значения. Например, в качестве значения характеристики "Область применения" можно ввести "Водоснабжение".
При добавлении записей уже внутрь этой номенклатурной группы они унаследуют от нее расширенный перечень характеристик, а также ЗНАЧЕНИЯ некоторых характеристик (области применения). Для листьев дерева IMHO следует задать требование обязательного определения ЗНАЧЕНИЙ характеристик. При их вводе в справочник они наследуют у родителя перечень характеристик вместе со значениями некоторых из них. А значения характеристик, которые не опредеоены в родительской группе, должны запросить в диалоге при добавлении записи. Если значение не задано, либо данная характеристика должна быть исключена из перечня характеристик листа дерева, либо не должна сохраниться сама номенклатура в номенклатурном справочнике.
Подобная структура позволяет, к примеру, отыскивать номенклатуру в справочнике по заданным значениям характеристик (а так же в наличии на складах). Значения для поиска числовых характеристик задаются в виде диапазона (от...-до...), для характеристик вида "Перечисление" и "Множества" - в виде конкретных значений из перечней допусьтимых значений этих характеристик. Структура достаточно динамичная - позволяет пополнять перечень характеристик, а также дополнять и корректировать перечни допустимых значений (например, добавить цвет "серо-буро-малиновый", если его в перечне нет, а необходимость в нем возникла). Комбинирует в себе удоство ввода (за счет использования механизма наследования) и отсутсвие жесткой привязки к иерархии дерева, поскольку как значения, так и перечни характеристик у дочерних элементов могут быть подправлены. Следует также учесть, что одни и те же характеристики могут быть привязаны совершенно к разным частям дерева. Например, х-ка "мощность" может быть как у двигателя, так и у агрегата с двигателем, находящегосы в другом поддереве номенклатурного справочника. Поиск номенклатуры по заданному значению мощности позволит отфильтровать как двигатели, так и агрегаты. Если нужно произвести поиск в определенной номенклатурной группе, его можно сузить дополнительным заданием вершины поддерева.
Есть еще множество мелких нюансов, но главное - идея. Развивайте, если понравилась.
VadimB
Дата: 18.12.2000 08:44:44
Уважаемый Garya
Меня очень заинтересовало выше решение.
Применялось ли раньше это решение?
Может дадите ссылку или более подробную информацию?
Заранее благлдарен
VadimBazylev@Mail.ru
http://www.newmarket.ru/