私たちは米国で特別支援学校とABA療法提供者のディレクトリを運営しています。事業者は、保護者が検索している場所で上位表示されるために料金を支払います。この機能の初版は、単一の整数カラムでした。それが実際に私たちにお金を損失させるまで問題なく機能していました。

ここでは、そのカラムとその不具合、そしてそれを置き換えたスキーマについて説明します。

素朴なモデル 

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

そして、各都市ページの背後にあるリゾルバ：

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

Enter fullscreen mode Exit fullscreen mode

これは午後1つで出荷できます。私たちはそうしました。約1年間は問題ありませんでした。

バグは「featured」という言葉に隠れている

「Featured」は事業者のプロパティではありません。それは事業者と場所の関係のプロパティです。

3つの州にまたがる40の町をカバーする臨床医を抱える事業者は、40の町すべてを購入したいわけではありません。彼らが欲しいのはオフィスのあるメトロエリアです。場合によっては2つのメトロエリアです。しかしfeatured_priorityはlisting行に存在するため、一度設定すると、その事業者は40の町すべてで他の事業者を上回るようになります。このカラムはグローバルです。販売されている製品はローカルです。

4つの問題が発生しました。被害の大きさの順に：

  1. 在庫を無料で提供してしまった。 1つの市場を販売すると、事業者が関わる他のすべての市場でも無料で上位表示されました。これらは事業者自身または競合他社に販売できた市場でした。
  2. 市場ごとの価格設定ができなかった。 密集したメトロエリアと人口4,000人の町は同じ整数だったため、同じ金額でした。
  3. キャンセルはオール・オア・ナッシングだった。 1つの契約を終了すると、事業者はすべての場所で同時に降格されました。
  4. 市場ごとの事実を記録する場所がなかった。 この配置はいつから始まったのか？交渉された順位は？この事業者がここに表示されたのは支払ったからか、それともここにオフィスがあるからか？

この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

以前は答えられなかったすべての質問が、今では単なるカラムになりました。事業者は1つのメトロエリアではultimate、隣のメトロエリアではfeatured、臨床医が訪れる他の80の町ではorganicになることができます。契約のキャンセルは1つの行を更新するだけです。価格設定はmarket_idから読み取られます。有料配置のアクティブなレポートは、考古学プロジェクトではなくwhere plan_tier <> 'organic'になります。

position_sourceもその役割を果たします。これは、listingが市場で表示される資格がある理由を記録します。これは、支払った金額とは異なる質問です。都市内のオフィスと都市に到達するサービスエリアは同じ主張ではなく、最終的には誰かがこれらを異なる方法で扱うよう依頼するでしょう。

移行後も間違え続けた2つのこと

coverage行への移行はモデリングを修正しました。しかし、私たちが自分のスキーマを2回誤読するのを止めることはできませんでした。

順位オーバーライドは相対的であり、スロットではない。 rank_override = 2は「このlistingを2位に配置する」と読めます。しかし、それはそうではありません。それはソートの入力なので、ページに1つのピン留めされた事業者があれば、どんな整数を与えても#1でレンダリングされます。実際に誰かを2位にするには、別の誰かを1位にピン留めする必要があります。後から見れば明らかで、誰かが「comparative」という言葉を口にする前に、1つの午後を無駄にしました。

coverageはページを生成しない。 私たちの都市ページは、少なくとも1つの事業者がその都市をアンカーする物理的な場所を持っている場合にのみ存在します。サービスエリアcoverageは「ここに表示される可能性があるのは誰か」に答えます。「ここは存在するか」には答えません。そのため、サービスエリアがアンカーされたlistingのない町に到達した事業者は、正しく設定されていても表示されず、404を指していました。生成されたページを持つシステムには何らかのバージョンがあります：資格ルールと存在ルールは別であり、片方だけを強制すると、構築されたことのないページでの配置を販売することになります。

移行メモ

退屈なカットオーバーを実現したいくつかのこと、それが目標です：

  • バックフィル、デュアルリード、その後の切り替え。 すべてが移行される間、数ヶ月間古いfeatured_priorityをフォールバック読み取りとして保持しました。それについて賢いことは何もなく、それがポイントです。
  • 多くの名前ができる前にティア名を正規化する。 私たちは3つの異なる製品時代のスラッグを蓄積しており、それらはすべて大まかに「有料」を意味し、さらにいくつかは学校と療法をティア名自体にエンコードしていました。1つの移行でそれらをクリーンなラダーに統合し、事業者タイプを常に属していた独自のカラムに移動しました。これを後で行うと、はるかに多くのコールサイトに触れることになったでしょう。
  • 解決を正確に1つの関数に配置する。 単一のlistings_for_city_page(city, state, type)が、特定の都市でどのcoverage行が勝つかを決定します。公開サイト、管理アプリ、バックグラウンドジョブはすべて独自のソートを書くのではなく、それを呼び出します。ランキングルールが変更されたとき、そしてそれは必ず変更されますが、それは1回だけ変更されます。

テスト

位置を販売するものを構築している場合、スキーマを1つの質問に対して実行してください：

2人の顧客が異なるものを購入できるか？

モデルが「デンバーで宣伝、ボルダーで通常」を表現できない場合、配置を販売しているわけではありません。グローバルフラグを販売していて、誰も違いに気づかないことを願っているのです。私たちは、何を無料で提供していたかの計算をしたときに気づきました。

スキーマよりもデータが欲しい場合

モデリングはさておき、これが存在する理由は、米国における特別支援サービスに関する公開情報が、クエリできない形式で州機関に散在していることです。私たちはその一部をクリーンアップし、登録なしで公開しています：

どちらも引用・再利用ともに無料です。これらで何か構築する場合は、ぜひご覧になりたいと思います。

私はSpecial Needs Care Networkで働いています。これは、米国の家族が特別支援学校やABA療法提供者を見つけるのを支援するディレクトリです。私たちのほとんどは、実際にクライアントを受け入れている事業者を見つける親にサービスを提供するための、退屈なデータモデリングです。