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.
There are many people defending relational databases, and rightly so, but I have never heard anyone defend the syntax of SQL. It's just that everyone standardised on it as the language of relational databases in the 1980s. Its inertia is hard to beat.
The json in my example would be generated in client code from the rows returned by the query, which I represented as CSV. The client's representation could just as easily be objects or protobufs or whatever, but I figured json would be a convenient way to portray nested data.
It's worth avoiding json on the wire if possible, because the extra layer of encoding will complicate things. Any good postgres integration will know how to safely deserialize and discriminate between (e.g.) DateTime and DateTimeWithTimeZone. But with json, everything's stringified.
Personally I am using an architecture similar to https://sive.rs/pg in my personal projects and I am very happy with it. It is important that you put data and procedures/views in different schemas so that you can use migrations for your data but automatically delete and recreate the procedures/views during deployment. Also use pgtap or something similar for testing.
That's a cool approach that could work well if you don't need realtime data validation such as in a UI. I would love to find a solution that allows the same validation rules to be used in the DBMS as well as in the backend and frontend code.
I like this point of view but putting logic in the database also has downsides - the tooling in particular is bad. No debugger, hard to write tests, can't canary changes, bad or non-existent autocompletion, lack of linters, etc.
The tooling keeps getting better so I'm bullish on that side.
For canaries there's growing support for branching the database which can help.
But in the end, this like all things requires balance. Putting everything in there can lead to pain down the road, for example I wouldn't follow the http part.
My use case is with app's local sqlite and I have a lot of code transforming the returned rows into JSON. It works but feels slower and to divorced from the data.
I don't know where you are from (I'm really curious though), but where I live there are more than two political parties and more than a few mega corporations to buy or rent from. You seem to have an extremely distorted idea of what live is like in "western countries".
Can you please share your country of residence? Because in Germany I really don’t feel the choice. There are few political parties, but I don’t feel this variety helps in any way. There are few mega corporations for everything else, just check the list of richest germans.
Edit: I might be another troll, but from last few elections I don’t feel any progress. As an engineer I see continuous offshoring of well paid positions to cheaper EU countries. As self employed electrician I see regulatory and tax madness.
I lived in the UK for 10 years, I've also lived in a number of other countries, from democracies, communist (Vietnam), and varying degrees of democratic and economic freedoms.
I'm aware there are more than exactly 2 parties in the ballots in many western countries. It's not about the numbers, but whether any of those choices really give the people real alternatives, or just different ways to screw the majority of the people.
As you can probably can see from the above interaction, people resort very quickly to ad hominem attacks.
> One of the ironies of this kind of advice is that it's best for people who already have a lot of experience and have the judgement to apply it. For instance, how do you know what the "simplest thing" is?
I think the author kind of mentions this: "Figuring out the simplest solution requires considering many different approaches. In other words, it requires doing engineering."
Agreed! The author is clearly an experienced and talented software engineer.
But the irony, in my opinion, is that experienced engineers don't need this advice (they are already "doing engineering"), but junior engineers can't use this advice because they don't have the experience to know what the "simplest thing" is.
Still, the advice is useful as a mantra: to remind us of things we already know but, in the heat of the moment, sometimes forget.
I like this. I had a rule of three: figure out three qualitatively different ways to solve the problem - different in kind, not just in choice of tools. Once you have three you start to understand the trade-offs. And you can come up with others quite easily.
Yep. Engineering almost always involves experimenting for suitability of multiple approaches, configurations, and other concerns. Measure, measure, and measure some more while considering nonfunctional requirements/concerns... something no LLM can (yet) do. (I don't hold out hope that there won't soon be some fully-autonomous coding/systems management LLMs that can create a tight Prompt/REPL/Test loop to take requirements and feedback directly from users.)
Germany is the only Western European country with an Eastern European capital. As a result, Berlin was poorer than the rest of the country, but this is no longer the case. Since reunification, Berlin has had the fastest growing economy in Germany and is now close to Hesse in terms of GDP per capita.
Berlin is odd... for several factors. Just looking at the last 100 years, I fail to see any capital (that was not directly a war zone) be: not capital, capital, divided, part of 2 countries, an exclave...
In practice, it is even less. Current state-of-the-art algorithms compress the English Wikipedia using just 0.8 bits per character: https://www.mattmahoney.net/dc/text.html
Why do you think Wikipedia is an accurate representation of the English language? Is it just because it's large?
As an encyclopedia, it has an intentionally limited and factual way of describing things, which lacks a lot of important parts of language like poetry, allegory, metaphor, slang, regional dialects, and so on.
The fact that it can be compressed down so much probably just means it has a ton of repetition.
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.
reply