3 ms·

The main problem was that we were unable to store the different types of addresses because of their different "layouts" in a "relational way."

hnfong 5y ago
I don't quite understand "was before RDBMS solutions supported JSON "... did (for example) MySQL not support storing JSONs as binary blobs or text before?
- gadrev 5y ago
  You could always store JSON as text, but you'd miss out on the ability to query over JSON efficiently, among some other things, see [1]. Maybe that's what made it useful/viable to GP. [1] https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
  - thatwasunusual 5y ago
    Correct. If only storing/retrieving was the goal, a text field would have been more than enough. But we also needed to retrieve data based on their address, and to generate reports on a daily basis, which made for being able to do "JSON queries" so much easier. So we put that logic in the application layer instead, something like: User - lookupByAddress(data) GermanUser inherits from User - overrides lookupByAddress(data) ...etc., so that we could be very specific about our queries depending on the country, or even _region_ in some countries.
the-dude 5y ago
I understand it might not be possible due to other contraints, but in smaller projects with international addresses I just used a text/memo field for the address and seperate field for the country. Addresses now can be entered the way the user prefers.
- thatwasunusual 5y ago
  This works fine for _small_ systems. But with large data sets, where you actually want to act upon the data, it's impossible to have it stored as text. One lazy solution would of course be to index that data into Elasticsearch (or similar solutions), but you will end up with database records that have missing or invalid indexed data at some point. A NoSQL solution is far from perfect, but for us it was _a lot_ better than just storing it as text somewhere.

hnfong 5y ago

I don't quite understand "was before RDBMS solutions supported JSON "... did (for example) MySQL not support storing JSONs as binary blobs or text before?

gadrev 5y ago
You could always store JSON as text, but you'd miss out on the ability to query over JSON efficiently, among some other things, see [1]. Maybe that's what made it useful/viable to GP. [1] https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
- thatwasunusual 5y ago
  Correct. If only storing/retrieving was the goal, a text field would have been more than enough. But we also needed to retrieve data based on their address, and to generate reports on a daily basis, which made for being able to do "JSON queries" so much easier. So we put that logic in the application layer instead, something like: User - lookupByAddress(data) GermanUser inherits from User - overrides lookupByAddress(data) ...etc., so that we could be very specific about our queries depending on the country, or even _region_ in some countries.

the-dude 5y ago

I understand it might not be possible due to other contraints, but in smaller projects with international addresses I just used a text/memo field for the address and seperate field for the country. Addresses now can be entered the way the user prefers.

thatwasunusual 5y ago
This works fine for _small_ systems. But with large data sets, where you actually want to act upon the data, it's impossible to have it stored as text. One lazy solution would of course be to index that data into Elasticsearch (or similar solutions), but you will end up with database records that have missing or invalid indexed data at some point. A NoSQL solution is far from perfect, but for us it was _a lot_ better than just storing it as text somewhere.