我們經營一份美國特殊需求學校與 ABA 治療提供者名錄。提供者付費以在父母搜尋的地方曝光。這個功能的最初版本只是一個整數欄位，直到它讓我們損失金錢為止。

以下是那個欄位、它的失敗，以及我們用來取代它的 schema。

原始模型 

create table listings (
  id               uuid primary key,
  name             text,
  city             text,
  state            text,
  featured_priority int default 0   -- higher sorts first
);

Enter fullscreen mode Exit fullscreen mode

以及每個城市頁面背後的 resolver：

select *
from listings
where city = $1 and state = $2
order by featured_priority desc, name asc;

Enter fullscreen mode Exit fullscreen mode

你可以在一個下午完成這個功能。我們做到了。它運行了約一年。

Bug 藏在「featured」這個字裡

「Featured」並非企業本身的屬性。它是企業與地點之間關係的屬性。

一個服務涵蓋三州四十個城鎮的提供者，並不想購買四十個城鎮。他們想要的是他們診所所在的都會區，也許是兩個都會區。但 featured_priority 存在於 listing 那一列，因此一旦設定，該提供者就會在所有四十個城鎮中排名第一。這個欄位是全域的，而我們出售的產品卻是區域性的。

有四件事出了問題，按傷害程度排序如下：

  1. 我們送出了庫存。 出售一個市場，卻免費讓提供者在他們接觸到的其他市場也得到曝光。這些市場本來可以賣給他們自己，或是賣給競爭對手。
  2. 我們無法依市場定價。 密集的都會區與人口四千的小鎮使用相同的整數，因此價值相同。
  3. 取消合約是全有或全無。 結束一筆交易，會同時讓該提供者在所有地方降級。
  4. 我們沒有地方存放每個市場的細節。 這個曝光是從何時開始？協商的排名是多少？這個提供者出現在這裡是因為付費，還是僅因為他們恰好在此設有辦公室？

第四點是真正的關鍵，而且它不只適用於名錄：每當你想把一個事實附加到一個旗標上時，這個旗標本來就應該是一個資料列。

修正：曝光是一條邊，而不是一個節點 

create table market_coverage (
  listing_id      uuid references listings(id),
  market_id       uuid references markets(id),
  plan_tier       text not null
                  check (plan_tier in ('organic','featured','premium','ultimate')),
  position_source text not null,  -- 'primary_location' | 'service_area' | 'metro'
  rank_override   int,
  placed_since    date,
  primary key (listing_id, market_id)
);

Enter fullscreen mode Exit fullscreen mode

之前無法回答的每個問題，現在都只需查詢一個欄位即可。一個提供者可以在一個都會區是 ultimate，在隔壁的都會區是 featured，而在他們的治療師會前往的另外八十個城鎮是 organic。取消一筆交易只需更新一行資料。定價可以從 market_id 讀取。報告活躍的付費曝光只需 where plan_tier <> 'organic'，而不是進行考古挖掘。

position_source 也發揮了作用。它記錄了為什麼一個 listing 符合在某個市場顯示的資格，這與他們付費的方案是不同的問題。市區內的辦公室，與服務範圍涵蓋市區，是不同的主張，而最終會有人要求你對它們進行不同的處理。

遷移後我們仍犯的兩個錯誤

改用 coverage 資料列修正了模型，但並未阻止我們兩次誤讀自己的 schema。

Rank override 是相對的，而不是一個固定位置。 rank_override = 2 看起來像是「把這個 listing 放在第 2 位」。它並不是這樣。它是一個排序的輸入，因此即使你在頁面上固定了一個提供者，它也會出現在第 1 位，無論你給它什麼整數。要真正讓某人排到第二位，你必須把另一個提供者固定在第一位。這事後看來很明顯，但在有人大聲說出「比較」這個詞之前，我們浪費了一個下午。

Coverage 不會憑空產生一個頁面。 我們的城市頁面只有在至少有一個提供者在該城市有實體地點時才存在。服務範圍的 coverage 回答的是「誰可以顯示在這裡」。它無法回答「這裡是否存在」。因此，一個服務範圍涵蓋了某個沒有 anchored listing 的城鎮的提供者，其設定是正確的，但卻是不可見的，並指向一個 404。任何使用動態產生頁面的系統都會遇到類似問題：資格規則與存在規則是分開的，如果你只執行其中一個，你就會在一個從未建立的頁面上出售曝光位置。

遷移說明

有一些讓切換過程平淡無奇的事情，這也是我們的目標：

  • 回填、雙重讀取，然後切換。 我們保留了舊的 featured_priority 作為後備讀取，持續了數個月，直到所有東西都遷移完成。這沒有什麼巧妙之處，而這正是重點。
  • 在數量變多之前，先正規化等級名稱。 我們累積了來自三個不同產品時代的 slugs，它們都大致意味著「付費」，另外還有一些 slugs 將學校與治療編碼在等級名稱中。一次遷移將它們合併成一個清晰的階梯，並將提供者類型移到它自己本來就該在的欄位中。如果之後再做，就必須觸及更多的呼叫點。
  • 將解析邏輯放在單一函式中。 單一的 listings_for_city_page(city, state, type) 函式決定了給定城市中哪個 coverage 資料列勝出。公開網站、管理應用程式以及背景工作都呼叫它，而不是各自撰寫排序邏輯。當排序規則改變時（它一定會改變），只需修改一次。

測試

如果你正在建立任何出售位置的東西，請用這個問題來檢驗你的 schema：

兩個客戶是否可以購買不同的東西？

如果你的模型無法表達「在丹佛曝光，在博爾德是普通」，那麼你出售的就不是曝光位置。你出售的是一個全域旗標，並希望沒有人注意到其中的差異。我們在計算我們一直以來送出去的東西後，注意到了這一點。

如果你想要的是資料而不是 schema

撇開建模不談，這一切存在的原因是，美國關於特殊需求服務的公開資訊散落在各州機構中，其格式無法被查詢。我們清理了其中一些並公開發布，無需註冊：

兩者均可免費引用與重複使用。如果你用它們建立了什麼，我真的很想看看。

我在 Special Needs Care Network 工作，這是一個幫助美國家庭尋找特殊需求學校與 ABA 治療提供者的名錄。我們所做的大多是乏味的資料建模，目的是幫助家長找到真正接受客戶的提供者。