> It affects performance too -- SQL wants storage locality by table, but applications often want locality by user.

This problem gets substantially worse in geographically distributed databases, like Spanner or CockroachDB, where joining tables whose data is stored in different localities can incur a significant latency penalty (in the hundreds of milliseconds).

They both offer an interesting solution to the problem called interleaved tables. CockroachDB docs on the subject are here [0], and Spanner's docs are here [1].

Interleaved tables don't fix the fact that SQL will hand you back a flat table when you have hierarchical data, but they do fix the spatial locality problem. Here's an example, from the CockroachDB docs, of how users and their order data can be interleaved, exactly the way it sounds like you've wanted:

    /customers/1
    /customers/1/orders/1000
    /customers/1/orders/1002
    /customers/2
    /customers/2/orders/1001
    /customers/2/orders/1003

[0]: https://www.cockroachlabs.com/docs/stable/interleave-in-pare...

[1]: https://cloud.google.com/spanner/docs/schema-and-data-model#...