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

Откуда:
Сообщений: 103
Дорогие друзья,

хотел бы провети дисскусию касательного того как наиболее оптимально организовать хранение информации об иерархии объектов (деревьях) в базе данных. Под объектом понимается запись в базе .

Собственно тема разбивается на две

1. Хранение
2. Выборка данных

Итак
1. Хранение. Некоторые из возможных решений:

1.1 Указание идентификатора родительского объекта. Таким образом для каждой записи создается два поля ID - идентификатор , PARENT_ID - дентификатор родителя.
Плюсы - очевидная структура.
Минусы - необходимость в дополнительном поле, невозможность включить объект в несколько деревьев.

1.2 Путь по дереву сохраняется в идентификаторе. Примером может служить нумерация читаемых вами пунктов. Т.е. 1,11, 12 и.т.д.
Плюсы - такой подход часто применяется на практике например в бух. учете в плане счетов, подход допускает вывод данных отсортированых в иерархическом порядке (см.2.2)

1.3. Хранение иерархии в отдельной таблице.

Понятно что выбор конкретного способа хранения должен определяться конкретными требованиями. Но все же возникает вопрос:
1.4 Как преобразовать 1.1 в 1.2 и наоборот ? Возможно ли такое преобразование в ХП и без применения курсоров?

2. Выборка. Тут у меня больше вопросов.

2.1 Существует ли изящный способ выбирать иерархии объектов из базы?
2.2 Как выбрать данные так чтобы они были отсортированы иерархическом порядке?
2.3 Как максимально перенести фунциональность по восстановлению иерархии в ХП?
2.4 Применяет ли кто нибудь рекурсии в этом процессе?
2.5 Приспособленны ли для подобных задач объктные базы данных ?

Жду ваших комментариев.
12 сен 05, 13:30    [1868667]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
Crimean
Member

Откуда:
Сообщений: 13147
1.3. решает 2.*
12 сен 05, 13:33    [1868678]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Поиск "деревянных" топиков
12 сен 05, 13:34    [1868688]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
Брюлик
Member

Откуда:
Сообщений: 690
Some examples



IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO

IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO

CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)

CREATE INDEX idx_nci_mgrid ON Employees(mgrid)

INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO

CREATE FUNCTION dbo.ufn_GetSubtree
(
@mgrid AS int
)
RETURNS @tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS

BEGIN

DECLARE @lvl AS int, @path AS varchar(900)
SELECT @lvl = 0, @path = '.'

INSERT INTO @tree
SELECT empid, mgrid, empname, salary,
@lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @mgrid

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1

INSERT INTO @tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @tree AS T
ON E.mgrid = T.empid AND T.lvl = @lvl - 1
END

RETURN

END
GO

SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO

/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/

/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/

/*
employee
-----------------
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
12 сен 05, 13:46    [1868779]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
M.Kap.
Member

Откуда:
Сообщений: 103
pkarklin
Поиск "деревянных" топиков


Угу. Пасибки.

Специально формулировал "иерархии" т.к. немного шире.

В любом случае может новые мысли появились.
12 сен 05, 13:51    [1868810]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
M.Kap.
Member

Откуда:
Сообщений: 103
Брюлик
Some examples ...


интересно.
12 сен 05, 13:56    [1868854]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Хранение иерархий  [new]
lenkinn
Member

Откуда: Москва, Россия
Сообщений: 2
Подскажите пожалуйста, как тут можно вместо "|" все это дело пронумеровать в виде:
1. Nancy
1.1. Andrew
1.1.1. Steven
1.1.2. Michael
1.2. Janet
1.2.1. Robert
1.2.1.1. David
1.2.1.1.1. James
1.2.1.2. Ron
1.2.1.3. Dan
1.2.2. Laura
1.2.3. Ann
1.3. Margaret
1.3.1. Ina
14 мар 12, 13:12    [12245715]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
lenkinn
Подскажите пожалуйста, как тут можно вместо "|" все это дело пронумеровать в виде:
1. Nancy
1.1. Andrew
1.1.1. Steven
1.1.2. Michael
1.2. Janet
1.2.1. Robert
1.2.1.1. David
1.2.1.1.1. James
1.2.1.2. Ron
1.2.1.3. Dan
1.2.2. Laura
1.2.3. Ann
1.3. Margaret
1.3.1. Ina
Можно написать функцию, кот. "развернёт" в строку весь путь от листа к корню.
15 мар 12, 12:49    [12252276]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
lenkinn,
with t as 
(select empid, empName, cast('1' as nvarchar(max)) as lvl 
   from Employees
   where empid = 1
 union all
 select Employees.empid, Employees.empName, t.lvl + '.' + cast(ROW_NUMBER() over (order by Employees.empid) as nvarchar(max)) as lvl
   from Employees
        inner join t on t.EmpId = Employees.mgrid)
        select lvl, Name from t
15 мар 12, 17:34    [12255757]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
Ctrl-C
Guest
hierarchyid
http://msdn.microsoft.com/ru-ru/library/bb677290.aspx


не?
15 мар 12, 19:45    [12256476]     Ответить | Цитировать Сообщить модератору
 Re: Хранение иерархий  [new]
lenkinn
Member

Откуда: Москва, Россия
Сообщений: 2
Minamoto, Ctrl-C спасибо! то что надо
16 мар 12, 12:09    [12259198]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить