The Featured Column That Cost Us Money (and the Schema That Fixed It)

The development team behind a US directory helping families find special needs schools and ABA therapy providers has shared a cautionary tale about database modeling mistakes that cost their company money. The platform allows providers to pay for promotional placement where parents search. For the initial version of this feature, the engineering team opted for the simplest approach by adding a single integer column to the listings table.

The original database schema was structured as follows:

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

And the city page resolver query looked like this:

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

That implementation could be shipped within a single afternoon and operated without issues for roughly a year. However, the team eventually realized their data model was actively leaking revenue because being featured is not an intrinsic property of a business itself; rather, it is a property of the relationship between a business and a specific location.

<

A provider with clinicians spanning forty towns across three states does not want to purchase all forty towns. They want the specific metro area where their physical office resides, perhaps two metros at most. Because featured_priority lived directly on the listing row, configuring that flag immediately caused the provider to outrank everyone across all forty towns. The column was global, whereas the product being sold was inherently local.

The original write-up further highlights complexities regarding rank overrides acting as sorting inputs rather than absolute slot assignments, alongside edge cases where service area coverage rules clashed with page existence checks, occasionally generating 404 errors for towns lacking anchored physical listings.

Source: Dev.to