Шаблон суміжних списків

Споконвічна проблема, що виникає при роботі з ієрархічними даними, полягає в методології їх зберігання, оскільки ієрархію не можна назвати природною схемою для реляційної моделі Існують два основні методи: суміжний список і матеріалізований шлях

Основні шаблони суміжних списків

Досі найпоширенішою схемою зберігання ієрархічних даних залишаються шаблони суміжних списків, неформально звані парами даних і моделями з власним обєднанням Вони зберігають ключ поточного вузла та його безпосереднього предка в рядку поточного вузла (в цьому розділі ми будемо звертатися до елементів пари даних як до поточного і батьківському вузлам)

Ця схема добре відома вам по таблиці Employee навчальної бази даних Northwind У ній в полях EmployeelD і ReportsTo зберігаються відповідно ідентифікатори співробітника і його безпосереднього начальника (Рис 121)

Рис 121 Таблиця Employee бази даних Northwind є прекрасним прикладом використання схеми шаблону суміжних списків для зберігання організаційної діаграми

Між працівником, виступаючим в ролі начальника, і співробітником, який перед ним звітує, існує відношення один до багатьох. Начальник може мати багато підлеглих, проте кожен працівник має тільки одного безпосереднього начальника Будь-який працівник може бути одночасно і начальником і підлеглим

Стовпець ReportsTo є зовнішнім ключем таблиці і посилається на стовпець Employee ID тієї ж таблиці У стовпці Report sTo зберігається ідентифікатор Employee ID безпосереднього начальника Якщо вузол працівника є поточним, то стовпець Report sTo вказує на батьківський вузол Цей стовпець може містити порожнє значення, оскільки глава всієї організації ні перед ким не звітує

З точки зору начальника, його ідентифікатор зберігається в полі Report sTo всіх його підлеглих

Варіації суміжних списків

Базова схема шаблону суміжних списків виявляється корисною в ситуаціях, коли існує лише ставлення один батько-безліч вузлів, проте цього недостатньо для серйозних виробничих ієрархій На щастя, базовий шаблон пари даних можна легко видозмінити для роботи зі складними ієрархіями У табл 121 представлені три варіації схеми суміжних списків і області їх застосування

Таблиця 121 Ієрархічні схеми

Шаблон

Для чого придатний

Базовий суміжний список або матеріалізований шлях

Суміжний список з двома батьками або подвійний матеріалізований шлях

Асоціативні таблиці

Обєктно-орієнтовані класи, прості організаційні діаграми, дерева видів

Генеалогії

Списки матеріалів, складні організаційні діаграми

Подвійні предки

Біологічні генеалогії (тобто ми не розглядаємо прийомних батьків) вимагають зберігання двох предків: чоловічої та жіночої особини, що, в свою чергу, вимагає наявності двох зовнішніх ключів Навчальна база даних Family використовує цю модель Так як зовнішні ключі посилаються на ту ж таблицю, вони повинні бути додані при її створенні Наступний код витягнутий з сценарію створення бази даних Family:

CREATE TABLE dboPerson (

PersonID INT NOT NULL

PRIMARY KEY NONCLUSTERED,

LastName VARCHAR(15) NOT NULL,

FirstName VARCHAR(15) NOT NULL,

SrJr VARCHAR(3) NULL,

MaidenName VARCHAR(15) NULL,

Gender CHAR(l) NOT NULL,

FatherID INT NULL,

MotherlD INT NULL,

DateOfBirth DATETIME NULL,

DateOfDeath DATETIME NULL

CREATE CLUSTERED INDEX IxPersonName ON dboPerson (LastName, FirstName)

ALTER TABLE dboPerson ADD CONSTRAINT

FK_Person_Father FOREIGN KEY (FatherlD) REFERENCES dboPerson (PersonID)

ALTER TABLE dboPerson ADD CONSTRAINT

FK_Person_Mother FOREIGN KEY (MotherlD) REFERENCES dboPerson (PersonID)

У базі даних Family кожен запис посилається на два батьківських вузла: біологічної матері та біологічного батька Обидва зовнішніх ключа вказують на один і той же первинний ключ (рис 122)

Puc 122 Шаблон суміжного списку з двома батьками можна використовувати для зберігання генеалогий (зокрема, він використаний в навчальній базі даних Family)

Можна довести, що даний шаблон порушує першу нормальну форму, оскільки обидва стовпці-MotherlD і FatherlD – зберігають зовнішній ключ PersonID Однак відносини до матері і до батька унікальні, і різниця в поколіннях призводить до того, що кожен зовнішній ключ реально вказує на відмінне підмножина таблиці Person, що може і повинно підтримуватися тригером

Якщо ви хочете завантажити ці навчальні бази даних і поекспериментувати з ЩцТТ’ В ними, а також випробувати на них створені мною запити, що використовують раз-N / Wce ™ ві варіації шаблону суміжних списків, зайдіть на сайт цієї книги за адресою

^     wwwSQLServerBiblecom

Асоціативні таблиці

При наявності відносин багато до багатьох між поточними і батьківськими вузлами потрібна наявність асоціативної таблиці (аналогічним чином реалізуються відносини багато до багатьох і в будь-який інший моделі) Наприклад, замовлення може містити безліч товарів, а будь-який товар може входити в безліч замовлень Тому таблиця рядків замовлення виступає в ролі асоціативної між таблицями замовлень і товарів

Аналогічна ситуація виникає на виробництві при розробці схем для списків матеріалів Наприклад, компонент а2 3 може використовуватися при виробництві безлічі інших компонентів, будучи при цьому сам виготовлений їх декількох інших складових У даному контексті будь-який компонент одночасно є і дочірнім, і батьківським для інших компонентів

У ієрархічному списку матеріалів сам цей список виступає в ролі суміжній таблиці між поточним і батьківськими вузлами, які зберігаються в одній і тій же таблиці Material (рис 123)

Рис 123 Схема списку матеріалів використовує асоціативну таблицю, що зберігає інформацію про те, з яких складових створюється кожен компонент

Шаблон матеріалізованого шляху

Шаблон матеріалізованого шляху являє собою ще один прекрасний метод зберігання ієрархічних даних і навігації по ним У своїй основі він зберігає денормалізован-ний список всіх предків поточного вузла, включаючи всі їх покоління Наприклад, у добре знайомій таблиці Employee бази даних Northwind стовпець MaterializedPath зберігає шлях в організаційній діаграмі від кореневого до поточного вузла Цей стовпець був доданий в таблицю виключно з метою демонстрації моделі матеріалізованого шляху-само це рішення не вимагає наявності стовпця ReportsTo

EmployeelD LastName FirstName ReportsTo MaterializedPath

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Davolio           Nancy           2                    21

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Fuller              Andrew         NULL             2

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Leverling         Janet             2                    23

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Peacock           Margaret       2                    24

5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Buchanan                               Steven                             2                                          25

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Suyama                                  Michael 5                                                                     256

7&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp King                                        Robert                              5                                          257

8&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Callahan                                 Laura                               2                                          28

9&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dodsworth Anne                                                             5                                          259

Шаблон матеріалізованого шляху має свої переваги і недоліки Позитивним є те, що існує кілька хороших методів маніпуляції ієрархією і її аналізу за допомогою строкових функцій Обмеження like в реченні where швидко знайде всіх, хто звітує перед містером Buchanan без необхідності проходження по всіх рівнях ієрархії

SELECT * FROM Employees WHERE MaterializedPath Like 25_

Результат виконання запиту буде наступним:

EmployeelD LastName                           FirstName ReportsTo MaterializedPath

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Suyama                                 Michael 5                                                                     256

7&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp King                                        Robert                              5                                          257

9&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dodsworth Anne 5 259

Незважаючи на всю привабливість шаблону матеріалізованого шляху, він обмежений тільки базовими ієрархіями, де у кожного вузла існує тільки один предок Це виключає реалізацію з його допомогою списків матеріалів і складних організаційних діаграм

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*