Hacker News Reader

Comment by manigandham | Hacker News Reader

Wrong solution and nothing to do with database design.

Why do the addresses even need to be in the ecommerce system to do the mailing? Couldn't they be in another table? Or a simple CSV? No reason to create fake customer records to send some spam. Something's missing in this story.

But if for some reason they are forced to use the same db, multiple customers sharing the same address doesn't mean they need to share the same row in the table. Keep the 1:many relation without the unique constraint. Customers can then have multiple addresses, even if they're shared with others.

Addresses are complicated too, so you might as well just have a country field + the rest and then use an API to standardize, catching errors and making queries easy.

View on HN

> Addresses are complicated too so you might as well just have a country field + the rest and then use an API to standardize, which also makes future queries easy.

This, big time. I worked on an ecommerce site that had to ship to Nigeria. No zip codes, often no street name or number. "Two doors north of the post office on the east side of Lagos." The couriers could find it.

There is a system called Plus Codes: https://plus.codes/

Designed as a way to address any location segment with a unique code, based on lat/long, and it's a great way to provide addressing for many areas that don't have formal systems.

Those, in a way, seem even worse compared to a system based on reference points. Where is G2HR+GW2 in relation to G2HR+GWD? Couriers in locations that use this system would need access to decode that into something that they can use to find a location, and people at locations would need a way to encode their current location. The current zip code system in the US is similar, with the exception of having been in use for about 50 years, and the number being relatively easier to remember.

The whole point of an addressing system is that it is structured. You current home address has nothing to do with reference points either, but it makes it easy to catalog and transfer in a standard format.

This is to help transition areas that are still based on local knowledge to a standard system, without the expense of official street names and building numbers.

> You current home address has nothing to do with reference points either,

But it does. Based on my address, you would know what city and state I am in, and in many cases even which direction in that city my street is (e.g. NW Whatever St). Then you'd know where on the street I am because 3350 is one block over from 2428. You'd also know which side of the street I am because 3350 is across the street from 3347.

Given a complete lack of an address system in rural areas, any structure is better than nothing.

If you're comparing address systems by how well local proximity can be inferred from a given address, then Lat/Long is the best system because it's global without language, national or political barriers, doesn't require formal naming, and has no arbitrary designations.

Open Location Codes are based on lat/long, but designed to use alphanumerics to make it easier to remember while also working interchangeably with lat/long and country designations.

Perhaps the developer page will help: https://plus.codes/developers

Using normal lat/long to read a map doesn't require you to do a base20 -> base10 conversion, where the characters are 23456789CFGHJMPQRVWX instead of 0123456789ABCDEFGHIJ, and where latitude and longitude are interleaved character by character, in your head.

Again, that's not the point of an address system. You do not use this to navigate (even with proximity built-in), but to transfer location data in a compact format. I don't see why this keeps getting misunderstood.

If you need to mark an area in a reliable, deterministic and politically neutral way, especially in the absence of any existing system, then this is a good option. If you need to drive your jeep to get somewhere, then continue using the lat/long that your GPS or map provides. If you need to translate between the two, then place codes make it easy because they require nothing but simple math.

Why did they choose that offer character set? I get excluding vowels to prevent profanity, but why exclude B, and not start from zero?

0 and 1 look like I and O, B looks like 8, and so on. They picked characters that can't be easily confused. The letters are also chosen so they won't spell meaningful words.

The relevant section of the definition is short, and an interesting read: https://github.com/google/open-location-code/blob/master/doc...

But you don't know whether you need to turn left or right to go from your street to Church street. The comparison can only be done on one dimension.

Most US sellers use some sort of USPS certified address validation and cleanup software to catch data entry errors and prevent delivery problems. So dumping everything but the country into a single big field isn't necessarily a viable design.

https://www.usps.com/nationalpremieraccounts/manageprocessan...

DO NOT DO THIS.

Have you ever moved into a new house or apartment on a new street or address? Effectively blocking users because you assume their address is corrupt because your system is out of date. At some point you have to choose what is better, trusting that your users are able to enter their address or that an API (from USPS or one of the dozen or so others out there) is able to understand what is and is not a valid address.

Don't trust your users. Use the address validation software, but if the address doesn't work allow the user to specifically choose to use their original entry. That's how most large ecommerce vendors operate now and it allows a manual override for new addresses.

And the address lists are updated very quickly now for any decent software. So it's only a problem for a very limited time.

Ok, have 2 fields. Input and Standardized. Confirm with the user if their input is not being standardized to make sure it isn't an error (which is explicitly called out in this article as a problem).

No reason to go to one extreme or another and lose sight of the actual problem.

Not just new users, due to some bitrot Pizza Hut has decided that my house, built in the 60's and probably occupied for 100+ years, no longer exists and they won't let me order. Not a big loss in this case, but imagine if this was someone like amazon deciding my address wasn't valid.

That's why I said "use an API to standardize". I don't see why this is incompatible with having a single field. Unless you actually need the component parts, it's much easier to manage than the schema needed for all the variations.

It's not really practical to call the API every time you need a normalized address. Normalize once and ask for user confirmation, then store the clean address with a separate field per component.

Sure, that's covered by "Unless you actually need the component parts"

Either way, yes you should store the clean standardized data and not the input. I didn't think that was confusing. Also you can get the best of both options with a JSON field.

Reply on news.ycombinator.com