Re:SQL Anti-Patterns You Should Avoid
by virissimo on Saturday October 18, 2025 @08:32PM
(#45631316)
There are ZIP codes that overlap a city and also an unincorporated area. Furthermore, there are zip codes that overlap different states. A data model that renders these unrepresentable may come back to bite you.
FYI this is not true in the US. Zip codes identify postal routes not locations
Re:SQL Anti-Patterns You Should Avoid
by bdangubic on Saturday October 18, 2025 @09:55PM
(#45631599)
saying zipcodes uniquely identify city/state/region is like saying John uniquely identifies a human :)
these kinds of things are almost never true in the real world.
EDIT: TIL that there are cross-state ZIP codes.
This assumption got me in trouble as a junior analyst years ago. I was asked to analyze our customer base and wrote something like the below. Management congratulated me on finding thousands more customers than we'd ever had before.
SELECT zipcode.rural_urban_code, COUNT(*) AS n_customer FROM customer INNER JOIN zipcode USING(zipcode) GROUP BY 1;
At scale, repeated low-cardinality columns matter a great deal.