8 ms·

Great article, and I this particularly brought back memories for me: > [...] in some instances you might work with vast quantities of data, or deal with transa

Great article, and I this particularly brought back memories for me: > [...] in some instances you might work with vast quantities of data, or deal with transactional systems that just don’t easily fit the operational limitations of relational databases. And in those cases, you should consider moving some, or all, of your data into a non-relational database. I've worked with a big application that utilised this approach with good results. The overall problem was that we had an application tailored for a specific country, but needed to expand to other countries. One of the specific problems was that we needed to store addresses and related data differently for each of the countries' users, and this didn't fit well with the current database schema. 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. So, yes, you can get the best of both worlds, but I would _never_ use a NoSQL solution to store all the application's data, independent of the type of application.

jseban 5y ago
I think your solution here was to move the typing to the application layer, which kind of makes sense because that's where you know the locale. But why also move the data to NoSQL, I don't see what that would add. If you had already removed the typing from the relation DB I think that would have worked as well, or am I missing something?
- thatwasunusual 5y ago
  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.
cpursley 5y ago
This is exactly what I do - store location components as jsonb in Postgres
- ransom1538 5y ago
  IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these blobs and creating relational tables anyway. Sure you could do metrics separately -- but good luck predicting what stats you will want in the future. Need an admin portal? With blobs you are coding all these custom admin pages. With mysql, I love when new people join and they can see and play with actual tables - slap in a good sql viewer and BAM you now have an admin portal. VP wants stats? give them mysql access and slack any sql they want: done .
  - willvarfar 5y ago
    I often put json blobs into MySQL tables. Modern MySQL has support for accessing the fields in the json from normal sql. You can also create computed columns that deref the json, and even index it. Other rdbms often have even better json support.
    - CRConrad 5y ago
      Those don't sound like "blobs" to me -- that stands for "Binary Large OBject"; JSON is text, not binary, isn't it?
  - jeremyjh 5y ago
    I haven't used mysql this way but we do lots of reporting on jsonb data using the provided json functions of postgres. It is very easy to extract a (nested) key, or extract a set of key/values to a table value that you join to just as if it were a separate table.
  - kroolik 5y ago
    > Some VP will ask "what weekday do users comment the most". This is why people invented read models. Because the data model that works for the app and makes it easy to work with there might not necessarily work for analytics. Generate analytics data from you app model so that both world can move independently. > Need an admin portal? With blobs you are coding all these custom admin pages. Admin portal with direct access to database columns? No thanks! IMHO, admin portal should hook into you business logic so that all the constraints are validated. Unless your app is just a CRUD.
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."
mamcx 5y ago
> [...] in some instances you might work with vast quantities of data, or deal with transactional systems that just don’t easily fit the operational limitations of relational databases. And in those cases, you should consider moving some, or all, of your data into a non-relational database. This is WRONG. With capital letters. Confuse limitations of (the codebase/the enterprise/the specific RDBMS) with the MODEL: > And in those cases, you should consider moving some, or all, of your data into a non-relational database. Making data "non-relational" is not a cure for the above. Is more often than not, a regression (like some on this thread: Using JSON instead of regular tables!). --- Now where we must focus instead is on make RDBMs more powerful (or more exactly: less constrained that cause less powers). RDBMS are hurt more for the interfacing (SQL) and the design decisions of 40 years ago, but not because the "relational model" is the reason performance, scalability or anything like that suffer. P.D: And also: Is incredible how many truly think the "relational model" is agains dynamic data. Even using SQL, that is a subset of it, is HIGHLY dynamic! What is a trouble, is that current RDBMS lack a easy way to embed relations in relation, forcing to detour to sub-optimal, ad-hoc, bug-ridden hacks.
- marcosdumay 5y ago
  > Making data "non-relational" is not a cure for the above. ACID imposes a limitation on scalability, there is no way to escape that. If you want to claim that approximately nobody reaches that scale, that's true, and if you want to claim that you don't need to ditch your data schema, that's also true. But relations aren't worth much without global coherence, and global coherence requires ACID, and will only work up to some size (that fits almost everyone, but not literally everyone).
  - mamcx 5y ago
    Yeah, that is right. But is tangential to be relational. You can have relational data in a causal way. Now, i see that some think "relations" are PK/FKs and that only GLOBAL coherence is the only way to make relations to work. If we can take a JSON and make it valid and correct only in this replica in this moment of time, relational data can too.
- 5y ago

jseban 5y ago

I think your solution here was to move the typing to the application layer, which kind of makes sense because that's where you know the locale. But why also move the data to NoSQL, I don't see what that would add. If you had already removed the typing from the relation DB I think that would have worked as well, or am I missing something?

thatwasunusual 5y ago
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.

cpursley 5y ago

This is exactly what I do - store location components as jsonb in Postgres

ransom1538 5y ago
IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these blobs and creating relational tables anyway. Sure you could do metrics separately -- but good luck predicting what stats you will want in the future. Need an admin portal? With blobs you are coding all these custom admin pages. With mysql, I love when new people join and they can see and play with actual tables - slap in a good sql viewer and BAM you now have an admin portal. VP wants stats? give them mysql access and slack any sql they want: done .
- willvarfar 5y ago
  I often put json blobs into MySQL tables. Modern MySQL has support for accessing the fields in the json from normal sql. You can also create computed columns that deref the json, and even index it. Other rdbms often have even better json support.
  - CRConrad 5y ago
    Those don't sound like "blobs" to me -- that stands for "Binary Large OBject"; JSON is text, not binary, isn't it?
- jeremyjh 5y ago
  I haven't used mysql this way but we do lots of reporting on jsonb data using the provided json functions of postgres. It is very easy to extract a (nested) key, or extract a set of key/values to a table value that you join to just as if it were a separate table.
- kroolik 5y ago
  > Some VP will ask "what weekday do users comment the most". This is why people invented read models. Because the data model that works for the app and makes it easy to work with there might not necessarily work for analytics. Generate analytics data from you app model so that both world can move independently. > Need an admin portal? With blobs you are coding all these custom admin pages. Admin portal with direct access to database columns? No thanks! IMHO, admin portal should hook into you business logic so that all the constraints are validated. Unless your app is just a CRUD.

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."

mamcx 5y ago

> [...] in some instances you might work with vast quantities of data, or deal with transactional systems that just don’t easily fit the operational limitations of relational databases. And in those cases, you should consider moving some, or all, of your data into a non-relational database. This is WRONG. With capital letters. Confuse limitations of (the codebase/the enterprise/the specific RDBMS) with the MODEL: > And in those cases, you should consider moving some, or all, of your data into a non-relational database. Making data "non-relational" is not a cure for the above. Is more often than not, a regression (like some on this thread: Using JSON instead of regular tables!). --- Now where we must focus instead is on make RDBMs more powerful (or more exactly: less constrained that cause less powers). RDBMS are hurt more for the interfacing (SQL) and the design decisions of 40 years ago, but not because the "relational model" is the reason performance, scalability or anything like that suffer. P.D: And also: Is incredible how many truly think the "relational model" is agains dynamic data. Even using SQL, that is a subset of it, is HIGHLY dynamic! What is a trouble, is that current RDBMS lack a easy way to embed relations in relation, forcing to detour to sub-optimal, ad-hoc, bug-ridden hacks.

marcosdumay 5y ago
> Making data "non-relational" is not a cure for the above. ACID imposes a limitation on scalability, there is no way to escape that. If you want to claim that approximately nobody reaches that scale, that's true, and if you want to claim that you don't need to ditch your data schema, that's also true. But relations aren't worth much without global coherence, and global coherence requires ACID, and will only work up to some size (that fits almost everyone, but not literally everyone).
- mamcx 5y ago
  Yeah, that is right. But is tangential to be relational. You can have relational data in a causal way. Now, i see that some think "relations" are PK/FKs and that only GLOBAL coherence is the only way to make relations to work. If we can take a JSON and make it valid and correct only in this replica in this moment of time, relational data can too.
5y ago