Hacker News Reader

Comment by crgwbr | Hacker News Reader

I’ve been through this before: storing a couple addresses for a few hundred different countries in an otherwise very normalized RDBMS. We couldn’t figure out anyway to do it other than EAV tables and a defined attribute pattern for each country.

Even though that project is long in the past for me, I’m really curious how other people do this. Anyone come up with something better?

View on HN

At a past job, we basically did "both" - we'd store our best guess at a normalized address, and we'd store the text representation. For stuff like shipping labels, we'd use the text. For analytics, we'd lean on our best-guess normalization (and understand that there's some potentially significant error).

You can also lean on various APIs to normalize, but nothing that I'm aware of does this well on a global scale.

At that point you might as well just make an "Addresses" or "Address" table with a raw text field for the address. At the end of the day this thing is something that a human will read and map to the real world location.

We went the EAV route instead of just a free form text field because we still wanted to expose discrete fields to in the UI and have some front-end validation. But sure, from a data integrity standpoint it’s no better than a text field.

> At the end of the day this thing is something that a human will read and map to the real world location.

That is quite an assumption.

I personally would use a field with a structured format e.g. XML (most RDBMS systems can deal with XML easily) and different types of address field e.g. postcode, zip code etc.

Then you could use a strategy for each country when dealing with it in your application.

> Even though that project is long in the past for me, I’m really curious how other people do this. Anyone come up with something better?

I've always gone the other way. Bland fields (Lines 1-4, city, state, postcode, country) or - when I didn't need to validate it, a textarea for free form entry.

Most of the time validation has been required by the shipping provider, so freeform hasn't been possible. As the shipping providers haven't got anything 'better', we stick to their format and field lengths (which are terrible for out-of-country addresses)

Reply on news.ycombinator.com