Neat idea and solves e.g. the N+1 problem.

But doesn’t that just shift the DB logic (denormalization, filtering, aggregation) into the application code?

I do think the idea is pretty neat, although it’s pretty close to returning structured data like json.

A slight disclaimer that these people are smarter than me, and know better about what we are talking about, so I may be wrong here on some assumptions. But I do get a bit of feeling they are trying to solve a trivial problem, at least in their use case. Ultimately there are only so many lecturers, and so many man lectures at a given time. The total data amount wouldn’t be so much, and you can easily group by and sort on client side to achieve the original table which is show on a per lecturer basis. A little redundancy is in my opinion preferred over a query that returns 3 tables that then needs additional complicated work. I also find arguments about overlapping names to not be something the database should be handling, it falls on the data owners/manager instead. Academia is a wild west at times, but either this table is presentation only or a link to lecturer or lecture. And in the latter case, you’ll already throw in the ids so they can be used in an URL to some other site.

While this can have significant less bandwidth, it also risks falling as soon as more data is introduced, as you’re putting the large join operations on the client when you can get free optimizations from the SQL engine you use. I know not having duplicate data could be a thing for something where I work, where essentially we have hourly breakdowns but fetch at least the entire day for a single set of parameters. So that means 24x data for a surprisingly high amount of columns. When we only need 2 of them on the hourly level! But in this case, the data doesn’t strictly need many joins as it has a lot of the information itself, along with there being too much data to join on the client side anyways for this to feel ideal. I feel you’ll increase the complexity a bit too much as well. A big advantage of sql is how easy it is to understand what you are getting.

Its somewhat of a solved problem, if the performance becomes a problem, since we can return nested data anyways. So we can already today technically return a row where the hour(I think, never tried a date before) and value columns have arrays instead of a single value. We just haven’t done it because it is not a big enough problem yet.

An interesting concept, and I do agree that the post-join cost is something that we can probably safely ignore. But as I was reading it I was curious if a better way to start conceptually approaching the solution is to consider an n+1 table approach where any tables referenced will be returned (filtered to relevant rows and optionally omitting extraneous columns) along with an additional table containing necessary key references and any of the computed aggregates etc… this might shift the select phrase to instead of defining all desired columns to only specifying additionally needed columns.

But… I do have some objections to this concept it seems to place an extremely heavy value on the initial schema that SQL does not and causes difficulties in some scenarios when a single table is joined against multiple times for different purposes. I think it’d become difficult for the front end to decipher and rebuild the relationships without very heavy lifting.

It’s a really interesting idea and flips the established return structure on its head in a way I don’t hate.

You can already return complex data structures using jsonagg and recursive queries. I can see wanting to make that easier and more intuitive to do. Current sql does not restrict you to just 2D table data structures though.

“an sequel”? Tell me I haven’t been saying it wrong this whole time 💀