我们运营一个美国特殊需求学校和 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

以及每个城市页面背后的解析器：

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

Enter fullscreen mode Exit fullscreen mode

你可以在一个下午完成这个功能。我们做到了。它正常运行了大约一年。

错误隐藏在 "featured" 这个词里

"Featured" 不是业务的属性。它是业务与地点之间关系的属性。

一个覆盖三个州四十个城镇的提供商并不想购买四十个城镇。他们只想要办公室所在的都市圈。也许是两个都市圈。但 featured_priority 存在于 listing 行上，所以一旦设置，该提供商就会在所有四十个城镇中排名第一。这是一个全局列。而销售的产品是本地的。

四件事出了问题，按对我们的伤害程度递增排列：

  1. 我们免费赠送了库存。 出售一个市场就意味着提供商在他们接触到的其他所有市场免费获得推广。这些市场本可以出售给他们或他们的竞争对手。
  2. 我们无法按市场定价。 一个密集的都市圈和一个 4000 人的小镇使用相同的整数，所以它们价值相同。
  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。要真正让某人排在第二位，你必须把别人固定在第一位。事后看来很明显，在有人说出 "comparative" 这个词之前，它浪费了一个下午。

Coverage 不会创建页面。 我们的城市页面只有在至少有一个提供商有物理位置锚定该城市时才存在。服务区域覆盖回答 "谁可能在这里显示。" 它不回答 "这里是否存在。" 所以，一个服务区域覆盖到一个没有锚定 listing 的城镇的提供商被正确配置但不可见，并指向 404。任何具有生成页面的系统都有某种版本的问题：资格规则和存在规则是分开的，如果你只执行其中一个，你就会在从未构建的页面上出售位置。

迁移说明

一些让切换变得无聊的事情，这是目标：

  • 回填、双重读取，然后切换。 我们保留了旧的 featured_priority 作为几个月后的回退读取。没有任何巧妙之处，这就是重点。
  • 在你有很多之前规范化层级名称。 我们积累了来自三个不同产品时代的 slugs，它们都大致意味着 "付费"，还有一些将学校与治疗编码到层级名称本身中。一次迁移将它们合并为一个清晰的阶梯，并将提供商类型移到它自己应该在的列中。稍后做这件事意味着要触及更多的调用站点。
  • 将解析放在一个函数中。 单个 listings_for_city_page(city, state, type) 决定给定城市哪个 coverage 行获胜。公共网站、管理应用和后台作业都调用它，而不是编写自己的排序。当排序规则改变时（它会改变），它只改变一次。

测试

如果你正在构建任何销售位置的东西，请用一个问题测试你的 Schema：

两个客户可以购买不同的东西吗？

如果你的模型不能表达 "在丹佛推广，在博尔德普通"，那么你不是在销售位置。你是在销售一个全局标志，并希望没有人注意到区别。当我们计算我们一直在免费赠送的东西时，我们注意到了。

如果你想要数据而不是 Schema

撇开建模不谈，这一切存在的原因是，美国关于特殊需求服务的公共信息分散在各州机构中，格式无人可以查询。我们清理其中的一些并公开发布，无需注册：

两者都可以免费引用和重复使用。如果你用它们构建了什么，我真的很想看到它。

我在 Special Needs Care Network 工作，这是一个帮助美国家庭寻找特殊需求学校和 ABA 治疗提供商的目录。我们所做的大部分工作是为父母找到真正接受客户的提供商提供乏味的数据建模服务。