SQL Hierarchie Díl 1 – jak správně vytvořit parent-child strukturu v tabulce?

Někdy potřebujeme v SQL vytvořit tabulku, která obsahuje hierarchii. Hierarchie je struktura (někdy se označuje jako parent-child hierarchie), která obsahuje závislosti mezi jednotlivými členy. Můžeme si to představit jako organizaci ve firmě. Firma má zaměstnance a ti mají svého nadřízeného. Nadřízení zaměstnanci mají opět svého nadřízeného a takto můžeme pokračovat až k CEO firmy, který už žádného nadřízeného nemá.

Hierarchie – Jak vytvořit správně parent-child strukturu v SQL tabulce?

Představme si tabulku se zaměstnanci. Hierarchie se vytváří v rámci 1 tabulky formou tzv. self-reference. To znamená, každého zaměstnance máme definovaného primárním klíčem (id_zamestnanec) a v dalším sloupci (id_nadrizeny) budeme mít cizí klíč, který povede na primární klíč id_zamestnanec.

Příklad: Vytvoříme tabulku pro firmu, která má celkem 15 zaměstnanců z toho jsou 4 manažeři a 1 ředitel firmy.

1) Nachystáme si tabulku „zamestnanci“ a mezi jednotlivými zaměstnanci definujeme vztah nadřízenosti a podřízenosti pomocí pole id_nadrizeny:

CREATE TABLE Temp.dbo.zamestnanci (
  id_zamestnanec INT PRIMARY KEY,
  id_nadrizeny INT,
  pozice VARCHAR(255),
  jmenoprijmeni VARCHAR(255),
  plat MONEY
)

INSERT INTO Temp.dbo.zamestnanci (
  id_zamestnanec,
  id_nadrizeny,
  pozice,
  jmenoprijmeni,
  plat
)
VALUES
  (1,11,'zamestnanec A','Jan Novák',20000),
  (2,11,'zamestnanec B', 'Petra Černá',23000),
  (3,11,'zamestnanec C', 'Petr Holomek',21000),
  (4,12,'zamestnanec D', 'František Netáhlo',25000),
  (5,12,'zamestnanec E', 'Vlastimil Táhlo',19000),
  (6,12,'zamestnanec F', 'Aleš Nedělám',27000),
  (7,13,'zamestnanec G', 'Zbyšek Pohoda',24000),
  (8,13,'zamestnanec H', 'Daniela Zuřivá',22000),
  (9,14,'zamestnanec I', 'Lenka Malá',21500),
  (10,14,'zamestnanec J', 'Pavel Držgrešle',29000),
  (11,15,'manager 1', 'Milan Flákač',35000),
  (12,15,'manager 2', 'Martin Nedoma',40000),
  (13,15,'manager 3', 'Robert Robertek',31000),
  (14,15,'manager 4', 'Miloš Novák',50000),
  (15,NULL,'ředitel firmy', 'Jan Přísný',90000)

2) Přidáme cizí klíč – selfreferenci na pole id_nadrizeny:

ALTER TABLE [dbo].[zamestnanci] WITH CHECK ADD CONSTRAINT [FK_zamestnanci_zamestnanci] FOREIGN KEY([id_nadrizeny])
REFERENCES [dbo].[zamestnanci] ([id_zamestnanec])

3) Ve výsledku máme takovou hezkou tabulku obsahující cizí klíč sama do sebe:

Vytvoření hierarchie - ukázka

V některém z dalších článků připomenu, jak se na parent-child strukturu dotazovat skripty pomocí rekurzivních dotazů a jak se vypořádat s hierarchiemi při reportování přes Reporting Services a Power BI.

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
Stránkonoš.cz - webové stránky za rozumnou cenu
SQL Hierarchie Díl 1 – jak správně vytvořit parent-child strukturu v tabulce?
5 (100%) 1 vote

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.