6 ms·

I used to work bit with MongoDB (over 10 years ago) and it did wonders in making me fall back into love with relational databases. I finally saw the light of Co

by stared 1y ago

I used to work bit with MongoDB (over 10 years ago) and it did wonders in making me fall back into love with relational databases. I finally saw the light of Codd! SQL, as a language is clunky, true. It can be patched here are there, either by PipeSQL or by various ORMs. I agree, that it would be wonderful to have standardized tooling for generating JSON like in the post. Yet, with relational databased you can separate concerns of: what is your data and what you want to display. If you use JSON-like way to store data, it can do the job until you want to change data or queries.

mrlongroots 1y ago
Agree: flat/relational structure is highly efficient to store, query planning/optimization/scaling become a lot easier, the language is clunky but relational algebra is beautiful, but... I think pushing multiple joins to what is still a relational database and getting a complex output isn't the worst idea in the world, as a higher-level layer on top of a regular database. On the other hand, "it needs four queries/RTTs" is not the worst thing in the world. It needn't be the goal of a system to achieve theoretical minimum performance for everything. Let those who truly have the problem in prod push the first patch.
arnsholt 1y ago
My stint with MongoDB was brief, but I too came away with a deeper appreciation of SQL bases. I feel it's a bit like a good type system: yes, there absolutely is an upfront cost, but over time it really does save you from stupid runtime problems. It's especially important when the bugs are in the data storage layer, because if a bug causes bad data to be written to your database, not only do you need to find and fix the bug, you also have to figure out how to deal with (possibly lots of) bad data. Also, modern SQL is an incredibly powerful language. Good SQL can save you from lots of dumb data munging code if you know how to wield your database properly. Especially for analytical queries, but also more typical application code IMO.
- anonymars 1y ago
  I have spent many years beating the drum that a few minutes spent on constraints today save many, many, many hours of painful data cleanup later And in fact, good constraints also can improve query performance. If the optimizer knows this column is unique, or that column is guaranteed to have a corresponding value in the joined table, it can do all sorts of tricks...
da_chicken 1y ago
Yes, nothing will make you appreciate the relational model more than using a database where the designer broke first normal form. Or where the developer thought the data in the database was theirs and it was only ever going to be used by just their application. Something else I find confusing is that every developer seems to want every database query for a single object to return a single row with a thousand columns, and anything multi row or with multiple results is way too complicated to handle. This goes double for report writing software. I really wonder what we're doing with database providers (drivers) that makes people want to develop ORMs when ORMs consistently feel like 10 ton gorillas. If the database world is so disparaging of the "row by excruciating row" processing, why do the drivers exclusively present data that way and no other?
- hdjrudni 1y ago
  I can't figure out why when I do `t1 left join t2` and it's not 1:1 or 1:0, I get duplicate t1 rows. Why can't either the driver or SQL find a way to represent a single t1 with multiple t2 (this is of course trivial to represent in JSON or any programming language with arrays and dicts). I don't mind that the tables in relational databases are essentially 2d matrices. I can do the hierarchy with the joins. I just need a way to get the data out in a sane format. Last time I brought this up ~15 years ago everyone hated on me. But everyone knows this is ultimately the data we want to get out of the system.
  - da_chicken 1y ago
    I mean, I understand why it does that currently. It's because query results are always relations (tables), and relations are defined as an unordered set of records. By definition you can't use row order meaningfully in a relation, and by extension row relationships. So it duplicates the t1 rows in order to show which records from t1 correlate to the records in t2. However, the issue is that relations are great inside the RDBMS, but they are not necessarily the best presentation of data outside the RDBMS. MS Access, as maligned as it is, has been able to display subsheets in a collapsible hierarchy [0] for decades at this point. Really, there should be a way to tell the RDBMS to output data as hierarchies based on the join without having to jump through the, quite frankly, completely ridiculous subquery syntax bullshit that JSON and XML formatting currently requires. Even if it only works in 95 out of 100 cases; even if you have to manually tell the query somehow what the key fields are to agglomerate based on. It should be way easier than having to stitch strings together like you're still using printf() for everything at all times. [0]: https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName.asp https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName....
andyferris 1y ago
100%. The shame, to me, is that SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. The relational model allows for any given value (field or cell value) to be itself a relation, but SQL doesn't make it easy to express such a query or return such a value from the database (as Jamie says - often the API server has to "perform the join again" to put it in nested form, due to this limitation).
- bazoom42 1y ago
  > The relational model allows for any given value (field or cell value) to be itself a relation First normal form explicitly forbids nested relations though. Relational algebra does not support nested relations for this reason. But perhaps nesting relations might make sense as the final step, just like sorting, which is not supported by the pure relational model either.
  - marcosdumay 1y ago
    If your relational algebra comes with normative opinions about data normalization, there's something really strange with the entire way you think about mathematics. Usually, relational algebra doesn't have many restrictions about the type of the atomic values it deals with, what makes sequences and relations perfectly valid candidates. But yeah, there are many reasons for you to normalize your data at rest.
    - taffer 1y ago
      AFAIK relational algebra and relational calculus are based on FOL (first-order logic) which predicates atomic elements. For nested elements you would need HOL (higher-order logic) which is mathematically hard to optimize. As FOL is as powerful as HOL but easier to optimise, it has become the basis for relational databases. The trade-off is that you have to normalise your data so that you can efficiently query and apply constraints to it. But yes, just because your data at rest should be flat relations, that doesn't mean that the results of your queries need to be flat. I think querying a well-normalised database and returning nested JSON makes a lot of sense.

by stared 1y ago

mrlongroots 1y ago

Agree: flat/relational structure is highly efficient to store, query planning/optimization/scaling become a lot easier, the language is clunky but relational algebra is beautiful, but... I think pushing multiple joins to what is still a relational database and getting a complex output isn't the worst idea in the world, as a higher-level layer on top of a regular database. On the other hand, "it needs four queries/RTTs" is not the worst thing in the world. It needn't be the goal of a system to achieve theoretical minimum performance for everything. Let those who truly have the problem in prod push the first patch.

arnsholt 1y ago

My stint with MongoDB was brief, but I too came away with a deeper appreciation of SQL bases. I feel it's a bit like a good type system: yes, there absolutely is an upfront cost, but over time it really does save you from stupid runtime problems. It's especially important when the bugs are in the data storage layer, because if a bug causes bad data to be written to your database, not only do you need to find and fix the bug, you also have to figure out how to deal with (possibly lots of) bad data. Also, modern SQL is an incredibly powerful language. Good SQL can save you from lots of dumb data munging code if you know how to wield your database properly. Especially for analytical queries, but also more typical application code IMO.

anonymars 1y ago
I have spent many years beating the drum that a few minutes spent on constraints today save many, many, many hours of painful data cleanup later And in fact, good constraints also can improve query performance. If the optimizer knows this column is unique, or that column is guaranteed to have a corresponding value in the joined table, it can do all sorts of tricks...

da_chicken 1y ago

Yes, nothing will make you appreciate the relational model more than using a database where the designer broke first normal form. Or where the developer thought the data in the database was theirs and it was only ever going to be used by just their application. Something else I find confusing is that every developer seems to want every database query for a single object to return a single row with a thousand columns, and anything multi row or with multiple results is way too complicated to handle. This goes double for report writing software. I really wonder what we're doing with database providers (drivers) that makes people want to develop ORMs when ORMs consistently feel like 10 ton gorillas. If the database world is so disparaging of the "row by excruciating row" processing, why do the drivers exclusively present data that way and no other?

hdjrudni 1y ago
I can't figure out why when I do `t1 left join t2` and it's not 1:1 or 1:0, I get duplicate t1 rows. Why can't either the driver or SQL find a way to represent a single t1 with multiple t2 (this is of course trivial to represent in JSON or any programming language with arrays and dicts). I don't mind that the tables in relational databases are essentially 2d matrices. I can do the hierarchy with the joins. I just need a way to get the data out in a sane format. Last time I brought this up ~15 years ago everyone hated on me. But everyone knows this is ultimately the data we want to get out of the system.
- da_chicken 1y ago
  I mean, I understand why it does that currently. It's because query results are always relations (tables), and relations are defined as an unordered set of records. By definition you can't use row order meaningfully in a relation, and by extension row relationships. So it duplicates the t1 rows in order to show which records from t1 correlate to the records in t2. However, the issue is that relations are great inside the RDBMS, but they are not necessarily the best presentation of data outside the RDBMS. MS Access, as maligned as it is, has been able to display subsheets in a collapsible hierarchy [0] for decades at this point. Really, there should be a way to tell the RDBMS to output data as hierarchies based on the join without having to jump through the, quite frankly, completely ridiculous subquery syntax bullshit that JSON and XML formatting currently requires. Even if it only works in 95 out of 100 cases; even if you have to manually tell the query somehow what the key fields are to agglomerate based on. It should be way easier than having to stitch strings together like you're still using printf() for everything at all times. [0]: https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName.asp https://www.fmsinc.com/free/NewTips/Access/SubdatasheetName....

andyferris 1y ago

100%. The shame, to me, is that SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. The relational model allows for any given value (field or cell value) to be itself a relation, but SQL doesn't make it easy to express such a query or return such a value from the database (as Jamie says - often the API server has to "perform the join again" to put it in nested form, due to this limitation).

bazoom42 1y ago
> The relational model allows for any given value (field or cell value) to be itself a relation First normal form explicitly forbids nested relations though. Relational algebra does not support nested relations for this reason. But perhaps nesting relations might make sense as the final step, just like sorting, which is not supported by the pure relational model either.
- marcosdumay 1y ago
  If your relational algebra comes with normative opinions about data normalization, there's something really strange with the entire way you think about mathematics. Usually, relational algebra doesn't have many restrictions about the type of the atomic values it deals with, what makes sequences and relations perfectly valid candidates. But yeah, there are many reasons for you to normalize your data at rest.
  - taffer 1y ago
    AFAIK relational algebra and relational calculus are based on FOL (first-order logic) which predicates atomic elements. For nested elements you would need HOL (higher-order logic) which is mathematically hard to optimize. As FOL is as powerful as HOL but easier to optimise, it has become the basis for relational databases. The trade-off is that you have to normalise your data so that you can efficiently query and apply constraints to it. But yes, just because your data at rest should be flat relations, that doesn't mean that the results of your queries need to be flat. I think querying a well-normalised database and returning nested JSON makes a lot of sense.