6 ms·

> Our solution was to move the typing of this information to the application layer, and just store the arbitrary data in a NoSQL solution. This worked perfectly

by jhgb 5y ago

> Our solution was to move the typing of this information to the application layer, and just store the arbitrary data in a NoSQL solution. This worked perfectly, and to my knowledge it's still working without a glitch. > This was before RDBMS solutions supported JSON, and if I were to do this again, I'd probably just continue to use MySQL, PostgreSQL or whatever, and store it as JSON in the database. Both solutions sound like anti-patterns to me. I was under the impression that the correct solution in an RDBMS would be the introduction of a custom type for the address that would cover all of your use cases, and all the associated operations, indices, etc. (in PostgreSQL, maybe even with PostGIS support for spatial operations and spatial indices). That not only keeps your logic, data integrity checks etc. on the server but also doesn't need text munging for every operation.

nimchimpsky 5y ago
you want to store addresses using the geo coordinates ? Thats the worst idea I've heard in a while.
bradleyjg 5y ago
Like for so many questions, the answer is: it depends. For some applications an address is just an opaque string that needs to be spit back out at an appropriate point. If you have such an application it’s probably not worth over engineering some elaborate solution because someday someone might want to do something more elaborate with that data. YAGNI
thatwasunusual 5y ago
> I was under the impression that the correct solution in an RDBMS would be the introduction of a custom type for the address [...] We were using MySQL, and MySQL didn't (doesn't?) have user defined types, so it was really not an option. (This was back in 2013, btw.)
squeaky-clean 5y ago
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.

by jhgb 5y ago

nimchimpsky 5y ago

you want to store addresses using the geo coordinates ? Thats the worst idea I've heard in a while.

bradleyjg 5y ago

Like for so many questions, the answer is: it depends. For some applications an address is just an opaque string that needs to be spit back out at an appropriate point. If you have such an application it’s probably not worth over engineering some elaborate solution because someday someone might want to do something more elaborate with that data. YAGNI

thatwasunusual 5y ago

> I was under the impression that the correct solution in an RDBMS would be the introduction of a custom type for the address [...] We were using MySQL, and MySQL didn't (doesn't?) have user defined types, so it was really not an option. (This was back in 2013, btw.)

squeaky-clean 5y ago

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.