7 ms·

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get thing

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at <reasonable address>". We have one address that's legally in two countries at the same time. One address is just a whole city. Like the entirety of the city, but also it still needs to be considered a separate place from the city. It of course depends on your use cases and etc. But I find addresses can be like storing names in a lot of contexts. i.e. Just take the bytes the user gives you and alert them if some service downstream complains, but don't require they change it to meet your requirements. There are systems to help offer standardized addresses and you can display them as suggestions to the user. But sometimes you get a multi billion dollar company telling you "Maybe the address is legally X, but the bus stops at the yellow sign across the street, and we get 30 customers calling for refunds every week because they didn't get on the bus. So either accommodate this change or we'll need to find a new partner."

jhgb 5y ago
I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't do with a custom type that you can do with a JSON blob. It's just that the custom type is more likely to be much more efficient for the task, and it also keep integrity checks as part of the type's implementation. And considering how often one needs to manipulate addresses in business settings, it seems a bit of a no-brainer to me that there should be some king of first-class support for addresses, just like there's for example first-class support for datetimes with time zones these days.
- avianlyric 5y ago
  > I understand these issues. I seriously doubt you do. The closest you can get to “standard address format” is: Address Line 1 Address Line 2 Address Line n Postal code (which can be blank) Postal Area (which can be blank) Country There is no way to build “first-class” support for addresses. Because theres no such thing as a valid or invalid address, only whether or not someone can find the correct location by reading the address. Of course, that person should be a local and intimately familiar with local address conventions. Conventions that will change how you describe flat numbers, street name, and address line ordering. > And considering how often one needs to manipulate addresses in business settings, Anyone who’s ever had to deal with real addresses would know this is the one thing you avoid doing with addresses. It’s pretty much impossible to correctly “manipulate” an address, because again, there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city. The best you can hope for when you’re forced to mutilate and address, is make the mutilation simple and obvious enough that the human actually delivering the post can un-mutilate it when they read it.
  - jhgb 5y ago
    I don't see how this changes anything. You can accommodate any address format, or any finite union of multiple address formats, including any computed or materialized views of the address with a custom type (AND including an "I give up" default for when everything else fails). > there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city Well, that may be an international issue. In my country's case, it's quite clearly defined by law. A type for an international case might by necessity be a union of unions.
    - wbl 5y ago
      I know what to write on my envelopes so they get to me. You do not. Your job is to make it possible for things you send to get to me, not the other 3 units in the apartment. Good luck with the Oakland addresses in Berkeley.
      - jhgb 5y ago
- mattbuilds 5y ago
  This is one of those things that sounds good in theory and the reality gets in the way and is way too messy.
- hug 5y ago
  This sounds to me like a "just draw the rest of the owl" solution. You're right in that there's nothing you can do with JSONB that you can't do with a custom type, but the larger problem is "do I even know how to make a custom type that might fit all of the various inconsistencies that I might face with this data?" The answer, for me, is no. I have no idea, and I'm not going to pretend. I can throw it all into a giant chunk of JSON and leave explicit note that address handling isn't, and at least that way no one actually thinks that it is, which is probably safer for everyone involved.
  - jhgb 5y ago
    In my case, I actually do know that; there's even a legal definition of what an address is and there's a national registry of all addresses. The problem is that I can't solve this for every country myself since I only have national knowledge (which is sufficient for my needs, fortunately).
    - psd1 5y ago
      Sounds like, for your purposes, you can define an address type that you can map to columns. But I think you're the outlier. We have fairly regular addresses in the UK. You can enforce a country and a postcode. You can enforce at least two lines of the local address. That's where it ends. I live right now I'm a house that's described as one village everywhere except by the local council, who address us by the other village. We're in a lane between the two. But the lane is impassable in the middle except with an off-road vehicle. Google maps doesn't know that. Visitors get lost even with GPS and precise co-ordinates! Before moving out here, I've frequently had to just enter "London" twice due to aggressive validation. I've even entered "London, London, Greater London". It works, but I know there's a senior engineer somewhere who's obstinate and wrong. Hell, I lived somewhere where flats B, C and D for our house number were a different door to A, because properties can be converted. What is the value of structuring this data? Store a country, a postal code, and a multiline local field, and don't try to validate that field except maybe to clean up surplus whitespace.
      - jhgb 5y ago
vharuck 5y ago
I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis. Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of work that's never really finished. Find a good service, hand them your garbage addresses, and feel confident they'll do better than you could.
- jhgb 5y ago
  My idea is not in contradiction with your idea. In fact your idea is a part of my idea.
  - ByteJockey 5y ago
    Given that the person started their comment with: > I've come around to this view, too. I don't think they were disagreeing with you.
    - jhgb 5y ago
      Concurring with a disagreeing view is disagreeing, isn't it?
      - dlainhart 5y ago
        No.
        jhgb 5y ago
        That was a rhetorical question. Of course it is.
- afandian 5y ago
  This is why two successive Deliveroo drivers went to the wrong street this week. The first 'tried to deliver' and gave up, and I had to run after the replacement one. Despite the address and postcode being both correct and unambiguous the driver followed a pin to a badly geocoded coordinate, and didn't even look at the street name. I'd encourage anyone (especially Deliveroo) to make more effort to model addresses in the correct locale (UK in my case) rather than taking these shortcuts.

jhgb 5y ago

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't do with a custom type that you can do with a JSON blob. It's just that the custom type is more likely to be much more efficient for the task, and it also keep integrity checks as part of the type's implementation. And considering how often one needs to manipulate addresses in business settings, it seems a bit of a no-brainer to me that there should be some king of first-class support for addresses, just like there's for example first-class support for datetimes with time zones these days.

avianlyric 5y ago
> I understand these issues. I seriously doubt you do. The closest you can get to “standard address format” is: Address Line 1 Address Line 2 Address Line n Postal code (which can be blank) Postal Area (which can be blank) Country There is no way to build “first-class” support for addresses. Because theres no such thing as a valid or invalid address, only whether or not someone can find the correct location by reading the address. Of course, that person should be a local and intimately familiar with local address conventions. Conventions that will change how you describe flat numbers, street name, and address line ordering. > And considering how often one needs to manipulate addresses in business settings, Anyone who’s ever had to deal with real addresses would know this is the one thing you avoid doing with addresses. It’s pretty much impossible to correctly “manipulate” an address, because again, there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city. The best you can hope for when you’re forced to mutilate and address, is make the mutilation simple and obvious enough that the human actually delivering the post can un-mutilate it when they read it.
- jhgb 5y ago
  I don't see how this changes anything. You can accommodate any address format, or any finite union of multiple address formats, including any computed or materialized views of the address with a custom type (AND including an "I give up" default for when everything else fails). > there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city Well, that may be an international issue. In my country's case, it's quite clearly defined by law. A type for an international case might by necessity be a union of unions.
  - wbl 5y ago
    I know what to write on my envelopes so they get to me. You do not. Your job is to make it possible for things you send to get to me, not the other 3 units in the apartment. Good luck with the Oakland addresses in Berkeley.
    - jhgb 5y ago
mattbuilds 5y ago
This is one of those things that sounds good in theory and the reality gets in the way and is way too messy.
hug 5y ago
This sounds to me like a "just draw the rest of the owl" solution. You're right in that there's nothing you can do with JSONB that you can't do with a custom type, but the larger problem is "do I even know how to make a custom type that might fit all of the various inconsistencies that I might face with this data?" The answer, for me, is no. I have no idea, and I'm not going to pretend. I can throw it all into a giant chunk of JSON and leave explicit note that address handling isn't, and at least that way no one actually thinks that it is, which is probably safer for everyone involved.
- jhgb 5y ago
  In my case, I actually do know that; there's even a legal definition of what an address is and there's a national registry of all addresses. The problem is that I can't solve this for every country myself since I only have national knowledge (which is sufficient for my needs, fortunately).
  - psd1 5y ago
    Sounds like, for your purposes, you can define an address type that you can map to columns. But I think you're the outlier. We have fairly regular addresses in the UK. You can enforce a country and a postcode. You can enforce at least two lines of the local address. That's where it ends. I live right now I'm a house that's described as one village everywhere except by the local council, who address us by the other village. We're in a lane between the two. But the lane is impassable in the middle except with an off-road vehicle. Google maps doesn't know that. Visitors get lost even with GPS and precise co-ordinates! Before moving out here, I've frequently had to just enter "London" twice due to aggressive validation. I've even entered "London, London, Greater London". It works, but I know there's a senior engineer somewhere who's obstinate and wrong. Hell, I lived somewhere where flats B, C and D for our house number were a different door to A, because properties can be converted. What is the value of structuring this data? Store a country, a postal code, and a multiline local field, and don't try to validate that field except maybe to clean up surplus whitespace.
    - jhgb 5y ago

vharuck 5y ago

I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis. Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of work that's never really finished. Find a good service, hand them your garbage addresses, and feel confident they'll do better than you could.

jhgb 5y ago
My idea is not in contradiction with your idea. In fact your idea is a part of my idea.
- ByteJockey 5y ago
  Given that the person started their comment with: > I've come around to this view, too. I don't think they were disagreeing with you.
  - jhgb 5y ago
    Concurring with a disagreeing view is disagreeing, isn't it?
    - dlainhart 5y ago
      No.
      - jhgb 5y ago
        That was a rhetorical question. Of course it is.
afandian 5y ago
This is why two successive Deliveroo drivers went to the wrong street this week. The first 'tried to deliver' and gave up, and I had to run after the replacement one. Despite the address and postcode being both correct and unambiguous the driver followed a pin to a badly geocoded coordinate, and didn't even look at the street name. I'd encourage anyone (especially Deliveroo) to make more effort to model addresses in the correct locale (UK in my case) rather than taking these shortcuts.