如何建模依賴類別（父子關係）？

理念/目標

我有兩個表：（Article代表一個產品）和Category. 關係是這樣的：anArticle只能分配給一個Category，但 aCategory可能至少有一個Articles，因此，1:n (n >= 1)。

現在，我想建構一個最多包含 5 個相關Category條目的類別樹。類別層次結構是恆定的。我給每個人Category一個level欄位，告訴我我在樹中的深度（1-5，1 是主要類別，5 是可能的最後一葉）。現在我仍然需要知道如何導航……我認為我可以設置一個parent_category指向上層類別的欄位。但是，主要類別沒有parent_category分別設置為null。

使用這種方法，我可以查詢每個級別的數據庫。這是有道理的，因為我想瀏覽我的類別，然後在輸入類別時查看子類別（想像一個 URL，如：）%host%/category/sub-category。

例子

請注意：此處表示的數據是任意的。但是結構將與下面的範例相同（我省略了一些其他欄位，因為它們對這個問題無關緊要）：

文章

| 編號 | 姓名 | fk_category |
| -- | --------------- | ----------- |
| 1 | 蘋果 | 4 |
| 2 | 橙汁 | 3 |

類別

| 編號 | 姓名 | 水平 | 家長 |
| -- | -------- | ----- | ------ |
| 1 | 水果 | 1 | 空 |
| 2 | 國內 | 2 | 1 |
| 3 | 果汁 | 3 | 2 |
| 4 | 隨便| 3 | 2 |

URL%host%/fruits/domestic將被解析為 SQL 查詢（此處簡化），例如：

SELECT name
FROM Category
WHERE level = 3

然後給我名字：“果汁”和“隨便”。

URL%host%/fruits/domestic/whatever將被解析為 SQL 查詢（此處簡化），例如：

SELECT name
FROM Article
WHERE fk_category = 4

這應該給我“Apple”（……以及該類別中的任何其他文章）。

我還不確定我應該如何實現它，Category 也知道它是否是一個沒有子節點的節點。我可以設置一個booleanfor has_children。

問題

你會如何模擬這種關係？可以按照我的想法進行建模嗎？有更好的選擇嗎？是否違反任何規則？

首先，您必須將節點從屬關係與節點本身分開。您需要一個trees具有兩列的數據透視表：nodeID -- parentID. 等於 NULL 的節點parentID是某棵樹的根節點。數據透視表中trees可以包含不止一棵樹。此表需要一個主鍵 (nodeID) 和一個唯一的多列鍵 (nodeID, parentID) - 每個節點都應該列出一次，並且每個節點只能有一個父節點。一個額外的限制 - 節點不能將自己稱為父節點。

CREATE TABLE trees (
 nodeID INT UNSIGNED NOT NULL,
 parentID INT UNSIGNED, -- nullable for root nodes
 PRIMARY KEY ( nodeID ), -- each node is listed once
 UNIQUE KEY  ( nodeID, parentID ), -- no duplicates
 CONSTRAINT treeselfref CHECK ( nodeID <> parentID ) -- can't be parent for itself
)

然後，您需要一組用於節點操作的程序：

AddNode( nodeID, parentID )
DeleteNode( nodeID ) -- you have to manage orphan childs
DeleteBranch( nodeID ) -- and all its childs/subchilds
GetParent( nodeID )  -- for the given node
GetChilds( parentID ) -- whos parent is parentID
GetBranch( parentID ) -- childs and their childs up to the leaves, ranked
GetLeaves( nodeID ) -- all leaves attached to the node
GetPath( nodeID ) -- full subordination path from the root up to the node, ranked
MergeBranches( nodeIDa, nodeIDb )
MoveBranch( nodeID, newParentID )
. . . . . 

一套精確的程序及其邏輯取決於項目/類別的性質。所以這裡是該GetLeaves( nodeID )過程的範常式式碼，僅用於說明基本原理：

WITH RECURSIVE cte (nodeID, parentID) AS 
( SELECT w.*   
   FROM trees AS w 
  WHERE w.nodeID = _nodeID  -- seed node for branch 
 UNION ALL
 SELECT q.*
   FROM cte   AS z
   JOIN trees AS q ON q.parentID = z.nodeID  -- childs, recurrent
) SELECT s.nodeID
   FROM cte        AS s
   LEFT JOIN trees AS r ON r.parentID = s.nodeID
 WHERE r.nodeID IS NULL; -- nodes not listed as parents for other nodes i.e. leaves

以下是GetPath( nodeID )返回從樹根到給定節點的所有父節點列表的過程程式碼，帶有秩（距離）：

WITH RECURSIVE cte (rank, nodeID, parentID) AS 
( SELECT 0 AS rank, w.*
   FROM trees AS w 
  WHERE w.nodeID = _nodeID
 UNION ALL
 SELECT z.rank+1, q.*
   FROM cte   AS z
   JOIN trees AS q  ON q.nodeID = z.parentID
) SELECT s.*, f.name 
   FROM cte        AS s
   JOIN nodes      AS n ON n.ID = s.nodeID
   JOIN categories AS f ON f.ID = n.categoryID
  ORDER BY s.rank DESC

當為 nodeID = 123 呼叫時，它將返回如下​​內容：

+----+------+--------+----------+
|rank|nodeID|parentID|name      |
+----+------+--------+----------+
|  5 | 1    | NULL   | root     |
|  4 | 7    | 1      | edible   |
|  3 | 11   | 7      | fruit    |
|  2 | 31   | 11     | domestic |
|  1 | 42   | 31     | apple    |
|  0 | 123  | 42     | mcintosh |
+----+------+--------+----------+

引用自：https://dba.stackexchange.com/questions/283782