建模分層屬性

我正在嘗試找出零售商的數據模型。

該零售商在全國有幾家商店，它們使用以下層次結建構模：

Channel -> Zone -> City -> Store

每個商店都包含幾篇文章。每篇文章都有類似的屬性

• 啟動標誌（這表明文章的存在）
• 價錢
• 供應商
• 倉庫

現在，零售商可以在層次結構中的任何級別設置這些屬性。考慮以下情況：

• 在渠道級別為文章設置價格將應用於所有商店。
• 設置在更高級別的價格可以在任何其他級別被覆蓋。例如，在城市級別僅針對城市中的商店或針對特定商店。
• 這適用於上面列出的所有屬性。

到目前為止，他們已經使用 RDBMS 對其進行建模，方法是在層次結構的頂部定義全域規則並將異常單獨呼叫為單獨的行。比如說，價格表將在頻道級別為文章設置價格，並且任何級別的任何更改都將單獨指定。顯然，這在獲取儲存級別的屬性時效率不高。

樣本數據

假設 Channel、Zone、City 和 Store 統稱為實體。Channel 的 id 範圍 >= 4000，Zone >= 3000，City >= 2000，store 範圍從 1 到 1000。

下面給出了層次關係數據的一個子集：

Channel   | Zone     | City    | Store |
----------+----------+------------------
4001      | 3001     | 2001    | 13    |
4001      | 3001     | 2001    | 14    |
4001      | 3001     | 2002    | 15    |
4001      | 3002     | 2003    | 16    |
4001      | 3003     | 2006    | 74    |

價格表

ArticleID | EntityID | Price
----------+----------+----------
12345     | 4001     | 2.5
12345     | 2003     | 2.9
12345     | 74       | 3.0

此處，將為所有商店中的商品設置頻道 4001 的價格 2.5。接下來的兩行設置了一些商店的價格例外。城市 2003 設置的第二個價格 2.9 將僅適用於商店 16 中的商品，按照上面提到的層級關係。並且第三行直接為商店 74 中的商品設置了 3.0 的價格。

希望這可以讓您了解目前模型。那麼，您能否建議一種更好的儲存方式？

您可以形式化您已經在使用的方法，並使其更有效地維護和查詢。考慮這樣的 ERD：

請注意，您的所有層次結構都被展平為一組列。存在一種內捲關係，因此層次結構中的每個子實體都可以指向它的直接父實體。我還包括了左右訪問數字，這將真正幫助您查找價格。最後有一Level列用於儲存層次結構中的排名。

如果您不熟悉探視人數，請查看我對這個問題的回答，我在其中進行了相當多的詳細介紹。

那麼為什麼這比你現在做的更好呢？

1. 您只跟踪層次結構中的每個項目一次。與 4001 多次出現在頻道列中不同的是，您只有一條 4001 的記錄。
2. 該結構是靈活的，並允許在以後插入新的級別。它還允許不均勻的樹木，例如 ZONE 3001 被細分為三個 SUBZONE，並且城市位於那裡的子區域之下（但直接進入 3001 之外的區域）。
3. 您的所有價格都在一個地方（不需要任何超出正常 SQL 查詢的讀取邏輯。不必有根據 EntityID 的範圍將價格連接到不同實體表的邏輯，您有一個一致的價格檢索查詢。
4. 您不需要在標識符中編碼含義，因此當您在任何一個級別獲得太多實體時都不會遇到麻煩，從而破壞您的編碼方案。
5. 價格檢索查詢非常簡單：

-- Look up the left and right numbers for the store in question:
declare @StoreLeft int
declare @StoreRight int
select @StoreLeft = H.left, @StoreRight = H.right
from HIERARCHY H where H.ID = @DesiredStoreId

-- Look up the lowest level (most granular) price for this store:
select TOP 1
 P.price_amt
, P.currency
from PRICE P
 inner join HIERARCHY H
   on H.Left <= @StoreLeft and H.right >= @StoreRight
 inner join ARTICLE A
   on P.article_id = A.id
where A.id = @TheArticleInQuestion
order by H.Level ASC   -- ASC or DESC depends on how you count levels.

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