4 ms·

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

by 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
      Yes, I said that the complete type would have to have components developed by others as well, since I can't provide input for other countries. Doesn't mean that a minimum complexity formalization is impossible (in fact, mathematically, by enumeration one such formalization must exist). An "as-given" component is obviously always going to work for you.
      - wbl 5y ago
        What's the point vs repeating what people give?
      - dlainhart 5y ago
        Addresses are first and foremost a social convention. Simple mathematics will not help you understand something with so much implicit complexity and contradiction.
  - avianlyric 5y ago
    > In my country's case, it's quite clearly defined by law. I theory the same is true in my country. But clearly someone forgot to inform the populous that not using the official standard is criminal, because I’ve seen plenty of “valid” addresses that don’t follow the standard. These addresses are clearly encoding local conventions, which makes decoding using the official standard not only impossible, but nonsensical, because manipulations that assume the standard will produce unusable addresses. Best part is, i know this because i was responsible for the system that was then to munge these addresses into the “official” format for tax reasons. That code is 99% edge cases, 0.09% “well shit, just shove it all in the last line and hope for the best” and 0.01% “official” standard.
- fragmede 5y ago
  Address Line 1 Address Line 2 Is the only way to do this, btw. any other options are going to be broken in other locations. I’m thinking specifically of apartment/unit number, eg Street address Apartment number This seems equivalent, but due to country specific differences, street and apartment is impossible to do correctly, or at least way harder than using line 1, line 2. This is because sometimes apartment number should come first, other times street needs to come first so the user has to second-guess your system in order to get mail delivered, while numbered lines are (hopefully) less ambiguous.
  - avianlyric 5y ago
    Yeah this is one of my big frustrations with many address systems. Street address Apartment number Is an (i think) and American convention. Here in the UK you would have Flat Number, Building Name Street Name (or possibly Street Number Street Name) Having addresses printed with the lines in the opposite order looks wrong to me. Additionally the American convention seems to assume all addresses are pretty simple, with there being an XOR relationship between street number and building name.
    - dlainhart 5y ago
      > Additionally the American convention seems to assume all addresses are pretty simple, with there being an XOR relationship between street number and building name. It doesn't assume anything of the sort. American addresses are a reference tag to direct the postal service where to send your mail. That's it.
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

by jhgb 5y ago

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
    Yes, I said that the complete type would have to have components developed by others as well, since I can't provide input for other countries. Doesn't mean that a minimum complexity formalization is impossible (in fact, mathematically, by enumeration one such formalization must exist). An "as-given" component is obviously always going to work for you.
    - wbl 5y ago
      What's the point vs repeating what people give?
    - dlainhart 5y ago
      Addresses are first and foremost a social convention. Simple mathematics will not help you understand something with so much implicit complexity and contradiction.
- avianlyric 5y ago
  > In my country's case, it's quite clearly defined by law. I theory the same is true in my country. But clearly someone forgot to inform the populous that not using the official standard is criminal, because I’ve seen plenty of “valid” addresses that don’t follow the standard. These addresses are clearly encoding local conventions, which makes decoding using the official standard not only impossible, but nonsensical, because manipulations that assume the standard will produce unusable addresses. Best part is, i know this because i was responsible for the system that was then to munge these addresses into the “official” format for tax reasons. That code is 99% edge cases, 0.09% “well shit, just shove it all in the last line and hope for the best” and 0.01% “official” standard.
fragmede 5y ago
Address Line 1 Address Line 2 Is the only way to do this, btw. any other options are going to be broken in other locations. I’m thinking specifically of apartment/unit number, eg Street address Apartment number This seems equivalent, but due to country specific differences, street and apartment is impossible to do correctly, or at least way harder than using line 1, line 2. This is because sometimes apartment number should come first, other times street needs to come first so the user has to second-guess your system in order to get mail delivered, while numbered lines are (hopefully) less ambiguous.
- avianlyric 5y ago
  Yeah this is one of my big frustrations with many address systems. Street address Apartment number Is an (i think) and American convention. Here in the UK you would have Flat Number, Building Name Street Name (or possibly Street Number Street Name) Having addresses printed with the lines in the opposite order looks wrong to me. Additionally the American convention seems to assume all addresses are pretty simple, with there being an XOR relationship between street number and building name.
  - dlainhart 5y ago
    > Additionally the American convention seems to assume all addresses are pretty simple, with there being an XOR relationship between street number and building name. It doesn't assume anything of the sort. American addresses are a reference tag to direct the postal service where to send your mail. That's it.

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