4 ms·

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

> 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.
- bazoom42 1y ago
  Sure, treating nested relations as “atoms” is supported naturally, but presumably you would want to query the content of these nested relations, which is where it gets complicated. The straighforward translation of a hierarchical database into relational form is to represent nested structures as nested relation. But if you cant query these nested relations the database is useless. So you either need to normalize to 1NF or extend the query language to query nested relations.
  - marcosdumay 1y ago
    You could reasonably want to evaluate data in those relations, to compose data to use at the top level. You could also reasonably run queries within the internal relations. You could probably be able to promote those relations up on the hierarchy by using them in joins. But I don't think it's reasonable to mix queries of several levels. (I can't even imagine how the syntax for that would look like.)

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.
bazoom42 1y ago
Sure, treating nested relations as “atoms” is supported naturally, but presumably you would want to query the content of these nested relations, which is where it gets complicated. The straighforward translation of a hierarchical database into relational form is to represent nested structures as nested relation. But if you cant query these nested relations the database is useless. So you either need to normalize to 1NF or extend the query language to query nested relations.
- marcosdumay 1y ago
  You could reasonably want to evaluate data in those relations, to compose data to use at the top level. You could also reasonably run queries within the internal relations. You could probably be able to promote those relations up on the hierarchy by using them in joins. But I don't think it's reasonable to mix queries of several levels. (I can't even imagine how the syntax for that would look like.)