[bazoom42]

The motivation behind first normal form is to keep the query language simple and powerful at the same time. Allowing nested tables would require extensions to the query language but wouldn’t give any additional expressive power since you can already express the same relationships using foreign keys.

Also I dont see how it isn’t hierachical? Nested tables create a hierachical structure just like nested records in a hierachical database. What is the difference?

I consider Date and Darwen more like fan-fiction writers than spiritual sucessors. It is fine they propose a modified version of the relational model, but they are doing it in a weird way where they try to redefine Codds words to mean the opposite of what he is saying, instead of just noting how they disagree. Like if Codd was a prophet whose words cannot be directly contradicted.

[jimwhite42]

> Also I dont see how it isn’t hierachical? Nested tables create a hierachical structure just like nested records in a hierachical database. What is the difference?

I haven't seen any reasonable uses of nested relations in base relvars, I think they are slightly different to nested records if you can query them declaratively instead of via imperative looking nested loop code.

But nested tables can be used in queries - the group and ungroup operators are pretty useful. It's a straightforward way to define (and even implement) group by aggregates for instance.

> Allowing nested tables would require extensions to the query language but wouldn’t give any additional expressive power since you can already express the same relationships using foreign keys.

Everything is a tradeoff. There are lots of queries that you can write using correlated subqueries in SQL, but I think would be much simpler if you could use group and ungroup.

I don't really follow your last paragraph at all. I would much rather use Date and Darwen's relational theory than Codd's. I think they have the right amount of respect for Codd, and definitely aren't shy to highlight where they differ from him, or to contradict him.

Do you have an example of them trying to redefine something in the way you say?

[bazoom42]

For example Date redefines first normal form to allow nested tables, while in Codds paper it is defined as eliminating nested tables. Why don’t they just give their concept a different name then? It just creates confusion.

[jimwhite42]

Can you say where he does this? I only remember him referring to first normal form as something historical, that he doesn't use that particular normal form directly at all.

[ttfkam]

It's not 1992 anymore. https://youtu.be/8Fb5Qgpr03g?si=Jc7dpuVgws3POiXA

It may upset someone's sense of "purity", but the SQL standard and thoughts about the relational model have long since moved on from this dogmatic view of atoms, and no, foreign keys can't perfectly model what folks need. Eventually the rubber hits the road, theory and practice diverge, and different approaches are clearly needed.

There is a place for a document atom in a database (JSON, XML, etc. that don't map well or efficiently to a traditional relational model). For arrays. (Putting in a separate table with its own PK to join is somehow more flexible? Just use unnest(…) for that.) For label trees. For geometries (GIS). For references to tables on other servers (SQL/MED). For tuples. For UUIDs. For ranges. For intervals.

You may call Date and Darwen's work fan fiction, but I have seen tremendous efficiency gains over the last few decades from engines drawing upon their insights without compromising data integrity. In fact, exclusion constraints with timestamp ranges is one area where the old strict relational model would be wholly ineffective for data integrity and avoiding race conditions cascading out to the application level.

https://www.cybertec-postgresql.com/en/postgresql-exclusion-...

[bazoom42]

I belive in using the right tool for the job, and this might somtimes be document databases, key-value stores, xml or json values or whatever is needed to solve the problem.

But let me point out that hierachical databases (which document databases, xml etc are variants of) predate the relational model (and certainly predate Dates work) so it it worth to be aware of the challenges and limitations which caused the development of the relational model as an alternative.

[ttfkam]

> I belive in using the right tool for the job

And more of these tools (RDBMSs) are able to perform more jobs. There's definitely use cases for correlating parts of documents with traditional relational data. Quite often there's no need to choose between a relational database engine and a document database engine when the engines support both and more.

Hybrids tend to blur the lines for definitions.