SQL Serverでディレクトリ/階層/ツリー構造をデータベースに格納する方法

ディレクトリ構造、階層構造、ツリー構造は、ファイルシステムや組織構造など、様々な場面で利用されています。これらの構造をSQL Serverデータベースに格納するには、いくつかの方法があります。

方法

1. 隣接リスト

2. 閉包表

3. パス名

4. XML

最適な方法の選択

最適な方法は、データ構造、データ量、クエリパターンによって異なります。

• 上記以外にも、Nested SetsやMaterialized Pathなど、様々な方法があります。
• データベース設計は、パフォーマンスや拡張性などを考慮して慎重に行う必要があります。

注意

• 専門的な知識が必要となる場合もありますので、必要に応じて専門家に相談してください。

CREATE TABLE [dbo].[Directory] (
  [Id] INT IDENTITY(1, 1) NOT NULL,
  [ParentId] INT NULL,
  [Name] VARCHAR(50) NOT NULL,
  [Description] VARCHAR(255) NULL,
);

INSERT INTO [dbo].[Directory] ([ParentId], [Name], [Description])
VALUES (NULL, 'トップ', '組織のトップレベル'),
       (1, '部署1', '営業部'),
       (1, '部署2', '人事部'),
       (2, 'チーム1', '営業チーム1'),
       (2, 'チーム2', '営業チーム2'),
       (3, '担当者1', '山田太郎'),
       (3, '担当者2', '佐藤花子');

-- 子ノードを取得
SELECT *
FROM [dbo].[Directory]
WHERE [ParentId] = @ParentId;

-- 親ノードを取得
SELECT *
FROM [dbo].[Directory]
WHERE [Id] IN (
  SELECT [ParentId]
  FROM [dbo].[Directory]
  WHERE [Id] = @Id
);

-- 階層関係を全て取得
SELECT *
FROM [dbo].[Directory]
WHERE [ParentId] IN (
  SELECT [Id]
  FROM [dbo].[Directory]
  WHERE [ParentId] = @ParentId
);

• 他の方法を試す場合は、テーブル構造やクエリを変更する必要があります。

CREATE TABLE [dbo].[DirectoryClosure] (
  [Id] INT IDENTITY(1, 1) NOT NULL,
  [AncestorId] INT NOT NULL,
  [Name] VARCHAR(50) NOT NULL,
  [Description] VARCHAR(255) NULL,
);

INSERT INTO [dbo].[DirectoryClosure] ([AncestorId], [Name], [Description])
VALUES (NULL, 'トップ', '組織のトップレベル'),
       (1, '部署1', '営業部'),
       (1, '部署2', '人事部'),
       (2, 'チーム1', '営業チーム1'),
       (2, 'チーム2', '営業チーム2'),
       (3, '担当者1', '山田太郎'),
       (3, '担当者2', '佐藤花子'),
       (1, '部署3', '開発部'),
       (4, 'チーム3', '開発チーム1'),
       (4, 'チーム4', '開発チーム2');

-- 子ノードを取得
SELECT *
FROM [dbo].[DirectoryClosure]
WHERE [AncestorId] = @Id;

-- 親ノードを取得
SELECT *
FROM [dbo].[DirectoryClosure]
WHERE [Id] IN (
  SELECT [AncestorId]
  FROM [dbo].[DirectoryClosure]
  WHERE [Id] = @Id
);

-- 階層関係を全て取得
SELECT *
FROM [dbo].[DirectoryClosure]
WHERE [AncestorId] IN (
  SELECT [Id]
  FROM [dbo].[DirectoryClosure]
  WHERE [AncestorId] = @Id
);

パス名

CREATE TABLE [dbo].[DirectoryPath] (
  [Id] INT IDENTITY(1, 1) NOT NULL,
  [Path] VARCHAR(255) NOT NULL,
  [Name] VARCHAR(50) NOT NULL,
  [Description] VARCHAR(255) NULL,
);

INSERT INTO [dbo].[DirectoryPath] ([Path], [Name], [Description])
VALUES ('/', 'トップ', '組織のトップレベル'),
       ('/トップ/部署1', '部署1', '営業部'),
       ('/トップ/部署2', '部署2', '人事部'),
       ('/トップ/部署1/チーム1', 'チーム1', '営業チーム1'),
       ('/トップ/部署1/チーム2', 'チーム2', '営業チーム2'),
       ('/トップ/部署1/担当者1', '担当者1', '山田太郎'),
       ('/トップ/部署1/担当者2', '担当者2', '佐藤花子');

-- 子ノードを取得
SELECT *
FROM [dbo].[DirectoryPath]
WHERE [Path] LIKE @Path + '/%';

-- 親ノードを取得
SELECT *
FROM [dbo].[DirectoryPath]
WHERE [Path] = SUBSTRING(@Path, 1, LEN(@Path) - CHARINDEX('/', REVERSE(@Path), 1))

-- 階層関係を全て取得
SELECT *
FROM [dbo].[DirectoryPath]
WHERE [Path] LIKE @Path + '%';

XML

CREATE TABLE [dbo].[DirectoryXml] (
  [Id] INT IDENTITY(1, 1) NOT NULL,
  [Xml] XML NOT NULL,
);

INSERT INTO [dbo].[DirectoryXml] ([Xml])
VALUES ('<Directory><Top><Department Name="営業部"><Team Name="営業チーム1"/><Team Name="営業チーム2"/></Department><Department Name="人事部"/></Top></Directory>');

-- 子ノードを取得
SELECT *
FROM [dbo].[DirectoryXml]
WHERE [Xml].exist('//Department[@Name="営業部"]/Team') = 1;

-- 親ノードを取得
SELECT *
FROM [dbo].[DirectoryXml]
WHERE [Xml].exist('//Team[@Name="営業チーム1"]/..') = 1;

-- 階層関係を全て取得
SELECT *