Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
Будьте добры подскажите,
может я неправильно сделал структуру, и тем неменее, надо чтото решать.

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

В общем, вот что я сделал:
+
--таблица свойств предмета
declare @Properties as table(PropertyID int, PropertyName varchar(10))
insert into @Properties(PropertyID,PropertyName) 
select 1,'Ширина' union all 
select 2,'Длина' union all 
select 3,'Высота' union all 
select 4,'Цвет' union all 
select 5,'Вес'

--таблица предметов
declare @Subjects as table(SubjectID int, SubjectName varchar(10), DimensionGroup_ID int)
insert into @Subjects(SubjectID,SubjectName,DimensionGroup_ID) 
select 1,'Шкаф',1 union all 
select 2,'Шкаф',2 union all 
select 3,'Стол',1 union all 
select 4,'Стул',1

--таблица группы свойств предмета
declare @DimensionGroups as table(DimensionGroupsID int)
insert into @DimensionGroups(DimensionGroupsID)
select 1 union all 
select 2

--таблица значений группы свойств
declare @Dimensions as table(DimensionGroup_ID int, Property_ID int, DimensionValue varchar(10))
insert into @Dimensions(DimensionGroup_ID,Property_ID,DimensionValue) 
select 1, 1,'200' union all 
select 1, 2,'250' union all 
select 1, 3,'220' union all 
select 2, 1,'130' union all 
select 2, 2,'180' union all 
select 2, 3,'200'

--таблица значений свойств предмета
declare @SubjectValues as table(ValueID int, Property_ID int, Value varchar(10))
insert into @SubjectValues(ValueID,Property_ID,Value) 
select 1, 4,'Белый' union all 
select 2, 5,'60' union all 
select 3, 4,'Синий' union all 
select 4, 4,'Черный' union all 
select 5, 5,'70'

--таблица связи @Subjects <-> @SubjectValues
declare @SubjectValuesRel as table(Subject_ID int, Value_ID int)
insert into @SubjectValuesRel(Subject_ID,Value_ID) 
select 1, 1 union all 
select 1, 2 union all 
select 2, 4 union all 
select 2, 5 union all 
select 3, 5


теперь, чтобы получить все свойства предмета я делаю запрос через юнион:
SELECT
	s.SubjectID,s.SubjectName, pr.PropertyName, d.DimensionValue 
FROM 
	@DimensionGroups as dg INNER JOIN
	@Subjects as s on dg.DimensionGroupsID = s.DimensionGroup_ID INNER JOIN
	@Dimensions as d on dg.DimensionGroupsID = d.DimensionGroup_ID INNER JOIN
	@Properties as pr on d.Property_ID = pr.PropertyID 

UNION ALL                      
SELECT
	s.SubjectID,s.SubjectName, pr.PropertyName,sv.Value 
FROM
	@Properties AS pr INNER JOIN
	@Subjects AS s INNER JOIN
    @SubjectValuesRel as sr ON s.SubjectID = sr.Subject_ID INNER JOIN
    @SubjectValues as sv ON sr.Value_ID = sv.ValueID ON pr.PropertyID = sv.Property_ID
ORDER BY 1

SubjectID   SubjectName PropertyName DimensionValue
----------- ----------- ------------ --------------
1 Шкаф Вес 60
1 Шкаф Высота 220
1 Шкаф Длина 250
1 Шкаф Цвет Белый
1 Шкаф Ширина 200
2 Шкаф Вес 70
2 Шкаф Высота 200
2 Шкаф Длина 180
2 Шкаф Цвет Черный
2 Шкаф Ширина 130
3 Стол Вес 70
3 Стол Высота 220
3 Стол Длина 250
3 Стол Ширина 200
4 Стул Высота 220
4 Стул Длина 250
4 Стул Ширина 200

но, я никак не пойму, как можно вернуть предметы, у которых, скажем, Ширина=200.
Или, Цвет=Черный? Или Чёрный и с шириной 200...
Я то незнаю точно, в какой таблице лежит именно это айди свойства, т.к. часть значений свойств из @Properties лежит в @Dimensions, а часть в @SubjectValues.
Подскажите, как решить эту задачку?
11 окт 09, 04:17    [7770023]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
viktor zelenin,

-- Ширина 200
select *
from @Subjects x
where exists(
	select *
	from @Subjects s
		join @Dimensions d on s.DimensionGroup_ID = d.DimensionGroup_ID
		join @Properties p on d.Property_ID = p.PropertyID
	where x.SubjectID = s.SubjectID
	  and p.PropertyName = 'Ширина' and d.DimensionValue = 200)

-- Черный цвет
select *
from @Subjects x
where exists(
	select *
	from @Subjects s
		join @SubjectValuesRel r on s.SubjectID = r.Subject_ID
		join @SubjectValues v on v.ValueID = r.Value_ID
		join @Properties p on v.Property_ID = p.PropertyID
	where x.SubjectID = s.SubjectID
	  and p.PropertyName = 'Цвет' and v.Value = 'Черный')

-- Черный цвет + Ширина 200
select *
from @Subjects x
where exists(select *
			from @Subjects s
				join @SubjectValuesRel r on s.SubjectID = r.Subject_ID
				join @SubjectValues v on v.ValueID = r.Value_ID
				join @Properties p on v.Property_ID = p.PropertyID
			where x.SubjectID = s.SubjectID
			  and p.PropertyName = 'Цвет' and v.Value = 'Черный')
  and exists(select *
			from @Subjects s
				join @Dimensions d on s.DimensionGroup_ID = d.DimensionGroup_ID
				join @Properties p on d.Property_ID = p.PropertyID
			where x.SubjectID = s.SubjectID
			  and p.PropertyName = 'Ширина' and d.DimensionValue = 200)
11 окт 09, 04:55    [7770029]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
Roman S. Golubin,
спасибо, но я видимо неверно задал вопрос.

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

Т.е. может быть такое, что для шкафа фиксированные значения Ширина х Высота х Длина, а у стула Высота и Цвет. Например, имеет место такая постановка таблиц:
+
--таблица свойств предмета
declare @Properties as table(PropertyID int, PropertyName varchar(10))
insert into @Properties(PropertyID,PropertyName) 
select 1,'Ширина' union all 
select 2,'Длина' union all 
select 3,'Высота' union all 
select 4,'Цвет' union all 
select 5,'Вес'

--таблица предметов
declare @Subjects as table(SubjectID int, SubjectName varchar(10), DimensionGroup_ID int)
insert into @Subjects(SubjectID,SubjectName,DimensionGroup_ID) 
select 1,'Шкаф',1 union all 
select 2,'Шкаф',2 union all 
select 3,'Стол',1 union all 
select 4,'Стул',3

--таблица группы свойств предмета
declare @DimensionGroups as table(DimensionGroupsID int)
insert into @DimensionGroups(DimensionGroupsID)
select 1 union all 
select 2 union all
select 3

--таблица значений группы свойств
declare @Dimensions as table(DimensionGroup_ID int, Property_ID int, DimensionValue varchar(10))
insert into @Dimensions(DimensionGroup_ID,Property_ID,DimensionValue) 
select 1, 1,'200' union all 
select 1, 2,'250' union all 
select 1, 3,'220' union all 
select 2, 1,'130' union all 
select 2, 2,'180' union all 
select 3, 3,'330' union all
select 3, 4,'Белый' union all
select 2, 3,'200'

--таблица значений свойств предмета
declare @SubjectValues as table(ValueID int, Property_ID int, Value varchar(10))
insert into @SubjectValues(ValueID,Property_ID,Value) 
select 1, 4,'Белый' union all 
select 2, 5,'60' union all 
select 3, 4,'Синий' union all 
select 4, 4,'Черный' union all 
select 5, 1,'70'

--таблица связи @Subjects <-> @SubjectValues
declare @SubjectValuesRel as table(Subject_ID int, Value_ID int)
insert into @SubjectValuesRel(Subject_ID,Value_ID) 
select 1, 1 union all 
select 1, 2 union all 
select 2, 4 union all 
select 2, 2 union all 
select 4, 5


Вот как в таком случае вернуть предметы со всеми свойствами, у которых цвет= 'Белый'?
Нужно получить такой результат:
SubjectID   SubjectName PropertyName DimensionValue
----------- ----------- ------------ --------------
1 Шкаф Вес 60
1 Шкаф Высота 220
1 Шкаф Длина 250
1 Шкаф Цвет Белый
1 Шкаф Ширина 200
4 Стул Высота 330
4 Стул Цвет Белый
4 Стул Ширина 70
11 окт 09, 11:55    [7770109]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
iljy
Member

Откуда:
Сообщений: 8711
viktor zelenin,

вам нужно начать с обучения проектированию базы. Потому что ваш набор таблиц - это кошмар БД прогера у вас два набора таблиц с идентичной функциональностью (@DimensionGroups, @DimensionValues и @SubjectValues, @SubjectValuesRels), хотя вам бы за глаза хватило 3х таблиц:
declare @Properties as table(PropertyID int, PropertyName varchar(10))
declare @Subjects as table(SubjectID int, SubjectName varchar(10))

declare @SubjectValues as table(Subject_ID int, Property_ID int, Value varchar(10))

а если набор свойств у вас не планируется расширять неограничено - то и одной:

declare @Subjects as table(SubjectID int not null, SubjectName varchar(10) not null,
                                     Length int not null, Width int not null, Height int not null,
                                     Color varchar(10) null, Weight int null)

мой вам совет - переделайте пока не поздно. Иначе через некоторое время ваши коллеги вас проклянут ;)
11 окт 09, 19:36    [7770536]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
так я и задаю здесь вопрос, чтобы разобраться насколько я на верном пути.
для чего я это всё делаю,
допустим, есть предмет,
есть группа свойств для этого предмета, например, состоящая из Ширины, Высоты и Длины.
и есть свойство, не входящее в группу - оттенок.
теперь, чтобы мне перечислить все предметы с 50 оттенками, мне нужно внести 50 раз размеры и цвет.
типа,
200х150х330 Белый
200х150х330 Красный
200х150х330 Синий
200х150х330 Чёрный
...

поэтому я решил, что зачем мне постоянно вбивать один и тот же размер, когда эту группу свойств можно поместить в таблицу эталонов. И указать только ссылку на эту группу свойств.
А во второй таблице уже указывать только те свойства, которые динамичекие у предмета.
Или правильнее - это перечислять все размеры и оттенок для каждого предмета в одной таблице?
Наборы обязательных свойств у предметов различные, у кого-то их 3, как я уже написал ширина, высота, длина. У кого-то 2, например, только цвет и ширина.
11 окт 09, 22:57    [7770849]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
iljy,
у меня как раз такая труктуру бд, как вы показали:
iljy
declare @Properties as table(PropertyID int, PropertyName varchar(10))
declare @Subjects as table(SubjectID int, SubjectName varchar(10))

declare @SubjectValues as table(Subject_ID int, Property_ID int, Value varchar(10))


но я и хочу попробовать отказаться от этой схемы, т.к. там получается большая каша, образно, по 500 предметов с одинаковыми свойствами и только отдельные свойства разные.
11 окт 09, 23:06    [7770859]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
iljy
Member

Откуда:
Сообщений: 8711
viktor zelenin,

если у вас набор свойств заранее известен - я бы сделал одну таблицу с nullable полями. А что предметы с одинаковыми свойствами - ну и что? Группы ваши сделайте в одну строку с 3 полями, если уж решили на спичках экономить. Но проблем это вам принесет больше чем выгоды.
11 окт 09, 23:45    [7770899]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
Simpliest
Member

Откуда:
Сообщений: 90
viktor zelenin

Я то незнаю точно, в какой таблице лежит именно это айди свойства, т.к. часть значений свойств из @Properties лежит в @Dimensions, а часть в @SubjectValues.
Подскажите, как решить эту задачку?

А зачем было так делить? :(

Введи еще одно поле

propertyid
propertytype
propertyname

По propertytype будешь определять к какой таблице обращаться (Dimensions или SubjectValues)
12 окт 09, 02:58    [7771031]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли использовать две таблицы значений для одной таблицы свойств?  [new]
Ох...ий программизд
Guest
viktor zelenin,

может быть вам поможет, например, эта статья: База данных — хранилище объектов
12 окт 09, 06:39    [7771086]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить