[解決済み] 階層テーブルの設計

質問事項

例えば、次のような情報をデータベースに保存しなければならないとします。

さて、私のデータベースのテーブルは、このように設計され、構造化されます。

後日、別のサブカテゴリーレベルを追加しなければならない場合、データベース構造をまったく変更することなく、どのようにこれを実現できるでしょうか。

カラムをテーブルの行データとして定義し、後でピボットを使って詳細を抽出するという方法を聞いたことがあるのですが、そのような方法でいいのでしょうか？

どなたかご教示いただくか、適切な方向に導いていただけないでしょうか。よろしくお願いします。

:)

解決方法は？

新しいレベルが生成される場合、テーブルにカラムを追加することは困難です。最良の方法は Hierarchy table を維持するために Parent-Child relationship .

テーブル : Items

 x----x------------------x------------x
 | ID |      Items       | CategoryId |
 |----x------------------x------------x
 | 1  |   Pepsi          |     3      |
 | 2  |   Coke           |     3      |
 | 3  |   Wine           |     4      |
 | 4  |   Beer           |     4      |     
 | 5  |   Meals          |     2      |
 | 6  |   Fried Rice     |     2      |
 | 7  |   Black Forest   |     7      |
 | 8  |   XMas Cake      |     7      |
 | 9  |   Pinapple Juice |     8      |
 | 10 |   Apple Juice    |     8      |
 x----x------------------x------------x

テーブル : Category

<イグ

カテゴリテーブルでは、カテゴリを n レベルを設定します。で Items テーブルには、最下層のカテゴリーを格納することができます。例えば、次のようなケースを考えてみましょう。 Pepsi - その categoryId カテゴリテーブルでは、親は JOIN を使用し、Hierarchy クエリを使用して親の親を検索します。

で Category テーブルでは、カテゴリに ParentId がNULL（つまり親Idがない）の場合、その親Idは MainCategory で、その他のアイテムは ParentId の下になります。 SubCategory .

EDIT :

なぜなら、現在のスキーマでは、サブカテゴリの数が変化し続ける可能性があるため、最初のテーブルにカラムを追加することができないからです。テーブルを作成しても リース・ジョーンズ の回答では、2つのテーブルを文字列で結合する必要があります。文字列で結合する場合の問題点は、テーブル上の Sub category または Main category という名前を付けると、すべてのテーブルを変更しなければならず、将来的にトラブルになる可能性があり、良いデータベース設計とは言えません。そこで、以下のようなパターンを採用することをお勧めします。

以下は、子アイテムの親を取得するクエリです。

DECLARE @ITEM VARCHAR(30) = 'Black Forest'

;WITH CTE AS
(
    -- Finds the original parent for an ITEM ie, Black Forest 
    SELECT I.ID,I.ITEMS,C.CategoryId,C.Category,ParentId,0 [LEVEL]
    FROM #ITEMS I
    JOIN #Category C ON I.CategoryId=C.CategoryId   
    WHERE ITEMS = @ITEM

    UNION ALL

    -- Now it finds the parents with hierarchy level for ITEM  
    -- ie, Black Forest. This is called Recursive query, which works like loop
    SELECT I.ID,I.ITEMS,C.CategoryId,C.Category,C.ParentId,[LEVEL] + 1
    FROM CTE I
    JOIN #Category C ON C.CategoryId=I.ParentId 
)
-- Here we keep a column to show header for pivoting ie, CATEGORY0,CATEGORY1 etc
-- and keep these records in a temporary table #NEWTABLE
SELECT ID,ITEMS,CATEGORYID,CATEGORY,PARENTID,
'CATEGORY'+CAST(ROW_NUMBER() OVER(PARTITION BY ITEMS ORDER BY [LEVEL] DESC)-1 AS VARCHAR(4)) COLS,
ROW_NUMBER() OVER(PARTITION BY ITEMS ORDER BY [LEVEL] DESC)-1 [LEVEL] 
INTO #NEWTABLE
FROM CTE
ORDER BY ITEMS,[LEVEL]
OPTION(MAXRECURSION 0)

以下は、上記のクエリの結果です。

説明

• Black Forest に該当します。 Cake .
• Cake に該当する Bakery .
• Bakery に該当する Food .

このように、任意の数のレベルに対して子や親を作成することができます。ここで、親を Food と Beverage は、例えば、次のようになります。 Food Industry を追加するだけです。 Food Industry に Category テーブルを維持し Food Industry's としてのID ParentId に対して Food と Beverage . 以上です。

さて、ピボットを行いたい場合は、以下の手順で行います。

1. 列から値を取得し、その値をピボットで列として表示します。

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + COLS + ']', '[' + COLS + ']')
               FROM    (SELECT DISTINCT COLS,[LEVEL] FROM #NEWTABLE) PV 
               ORDER BY [LEVEL] 

2. 次に、以下のPIVOTクエリを使用します。

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT ITEMS, CATEGORY, COLS  
                 FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 MIN(CATEGORY)
                 FOR COLS IN (' + @cols + ')
            ) p
            ORDER BY ITEMS;' 

EXEC SP_EXECUTESQL @query

• ここをクリック 結果を表示する

ピボットの後、以下の結果が得られます。

ノート

1. 項目に関係なくすべてのレコードを取得したい場合は、以下のように WHERE の中にある CTE . クリックはこちら をクリックすると結果が表示されます。

2. ピボットテーブルの列の順序を次のように指定しました。 DESC つまり、トップレベルの親......アイテムの親が表示されます。もし、アイテムの親を最初に表示し、次のレベルの親を表示し、最後にトップレベルの親を表示したい場合は、次のように変更します。 DESC の中で ROW_NUMBER() から ASC . ここをクリック をクリックすると結果が表示されます。