3 ms·

Yes - I have to agree. Codd was right in that if you want transactional semantics that are both quick and flexible, you'll need to _store_ your data in normali

Yes - I have to agree. Codd was right in that if you want transactional semantics that are both quick and flexible, you'll need to _store_ your data in normalized relations. The system of record is unwieldly otherwise. The article is right that this idea was taken too far - queries do not need to be restricted to flat relations. In fact the application, for any given view, loves heirarchical orginization. It's my opinion that application views have more in common with analytics (OLAP) except perhaps latency requirements - they need internally consistent snapshots (and ideally the corresponding trx id) but it's the "command" in CQRS that demands the normalized OLTP database (and so long as the view can pass along the trx id as a kind of "lease" version for any causally connected user command, as in git push --force-with-lease, the two together work quite well). This issue is of course that SQL eshews hierarchical data even in ephemeral queries. It's really unfortuante that we generate jsonb aggregates to do this instead of first-class nested relations a la Dee [1] / "third manifesto" [2]. Jamie Brandon has clearly been thinking about this a long time and I generally find myself nodding along with the conclusions, but IMO the issue is that SQL poorly expresses nested relations and this has been the root cause of object-relation impedence since (AFAICT) before either of us were born. [1] https://github.com/ggaughan/dee https://github.com/ggaughan/dee [2] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf

reaanb2 1y ago
In my view, the O/R impedance mismatch derives from a number of shortcomings. Many developers view entities as containers of their attributes and involved only in binary relationships, rather than the subjects of n-ary facts. They map directly from a conceptual model to a physical model, bypassing logical modeling. They view OOP as a data modeling system, and reinvent network data model databases and navigational code on top of SQL.
- mcphage 1y ago
  In that case, the mismatch is between "What developers need" and "What SQL provides".
  - CRConrad 1y ago
    Or maybe between "What developers want / think they need" and "What SQL provides".
9rx 1y ago
> The article is right that this idea was taken too far The biggest mistake was thinking we could simply slap a network on top of SQL and call it a day. SQL was originally intended to run locally. You don't need fancy structures so much when the engine is beside you, where latency is low, as you can fire off hundreds of queries without thinking about it, which is how SQL was intended to be used. It is not like, when executed on the same machine, the database engine is going to be able to turn the 'flat' data into 'rich' structures any faster than you can, so there is no real benefit to it being a part of SQL itself. But do that same thing over the network and you're quickly in for a world of hurt.
- Mikhail_Edoshin 1y ago
  But the very title of Codd's paper mentions shared data banks, doesn't it? Concurrent access and thus networking was there from the beginning. One of reasons SQL is the way it is (declarative) is because it shields the user from the underlying concurrency: there is no notion of it at all, to the user things look as if he was the only client of the database, while in reality he's just one of many.
  - 9rx 1y ago
    > But the very title of Codd's paper mentions shared data banks, doesn't it? Shared as in on a single mainframe, yes. Remember, Codd was a mainframe guy — even helping to design one before starting work on the relational model. It wasn't until Oracle came along did anyone really think it would be a good idea to slap networking directly on top of SQL. > Concurrent access and thus networking was there from the beginning. Again, the trouble is the high latency that networks introduce. That wasn't there from the beginning. Mainframes are designed for low-latency. That is a relatively new constraint that Codd didn't need to think about... ...But we in the internet age do. The high latency means that, in many cases, we can't realistically use SQL as it was intended. Which is why we have all ended up building bespoke DMBSes that speak things like JSON instead of SQL. Granted, there have been some efforts to bring richer data structures to SQL, including JSON support, but they're all pretty hacky, frankly. More ideal would have been to design a better language for the client/server database model from the start, but we are no doubt in too deep now. Worse is better applies.
kragen 1y ago
I agree about first-class nested relations, but I don't agree about transactions. Codd was writing 10 years before the idea of transactional semantics was formulated, and transactions are in fact to a great extent a real alternative to normalization. Codd was working to make inconsistent states unrepresentable in the database, but transactions make it a viable alternative to merely avoid committing inconsistent states. And I'm not sure what you mean by "quick", but anything you could do 35 years ago in 10 milliseconds is something you can do today in 100 microseconds.
- andyferris 1y ago
  It's not about just _transactions_. What you wrote is 100% correct. It's specifically about _fast_ transactions in the OLTP context. When talking about the 1970s (not 1990s) and tape drives, rewriting a whole nested dataset to apply what we'd call a "small patch" nowadays wasn't a 10 millisecond job - it could feasibly take 10s of seconds or minutes or hours. That a small patch to the dataset can happen almost instantly - propagated to it's containing relation, and a handful of subordinate index relations - was the real advance in OLTP DBs. (Of course this never has and never will help with "large patches" where the dataset is mostly rewritten, and this logic doesn't apply to the field of analytics). Perhaps Codd "lucked out" here or perhaps he didn't have the modern words to describe his goal, but nonetheless I think this is why we still use flat relations as our systems of record. Analytical/OLAP systems do vary a lot more!
  - kragen 1y ago
    Hmm, but I think people doing OLTP in the 01970s were largely using things like IMS, which used ISAM, on disk, to be able to do small updates to large nested datasets very quickly? And for 20+ years one of the major criticisms of relational databases was that they were too slow? And that even today the remaining bastions of IMS cite performance as their main reason for not switching to RDBMSes? I think that if you're processing your transactions on tape drives, your TP isn't OL; it's offline transaction processing. I think Codd's major goal was decoupling program structure from on-disk database structure, not improving performance. There's a lot of the history I don't know, though.

reaanb2 1y ago

In my view, the O/R impedance mismatch derives from a number of shortcomings. Many developers view entities as containers of their attributes and involved only in binary relationships, rather than the subjects of n-ary facts. They map directly from a conceptual model to a physical model, bypassing logical modeling. They view OOP as a data modeling system, and reinvent network data model databases and navigational code on top of SQL.

mcphage 1y ago
In that case, the mismatch is between "What developers need" and "What SQL provides".
- CRConrad 1y ago
  Or maybe between "What developers want / think they need" and "What SQL provides".

9rx 1y ago

> The article is right that this idea was taken too far The biggest mistake was thinking we could simply slap a network on top of SQL and call it a day. SQL was originally intended to run locally. You don't need fancy structures so much when the engine is beside you, where latency is low, as you can fire off hundreds of queries without thinking about it, which is how SQL was intended to be used. It is not like, when executed on the same machine, the database engine is going to be able to turn the 'flat' data into 'rich' structures any faster than you can, so there is no real benefit to it being a part of SQL itself. But do that same thing over the network and you're quickly in for a world of hurt.

Mikhail_Edoshin 1y ago
But the very title of Codd's paper mentions shared data banks, doesn't it? Concurrent access and thus networking was there from the beginning. One of reasons SQL is the way it is (declarative) is because it shields the user from the underlying concurrency: there is no notion of it at all, to the user things look as if he was the only client of the database, while in reality he's just one of many.
- 9rx 1y ago
  > But the very title of Codd's paper mentions shared data banks, doesn't it? Shared as in on a single mainframe, yes. Remember, Codd was a mainframe guy — even helping to design one before starting work on the relational model. It wasn't until Oracle came along did anyone really think it would be a good idea to slap networking directly on top of SQL. > Concurrent access and thus networking was there from the beginning. Again, the trouble is the high latency that networks introduce. That wasn't there from the beginning. Mainframes are designed for low-latency. That is a relatively new constraint that Codd didn't need to think about... ...But we in the internet age do. The high latency means that, in many cases, we can't realistically use SQL as it was intended. Which is why we have all ended up building bespoke DMBSes that speak things like JSON instead of SQL. Granted, there have been some efforts to bring richer data structures to SQL, including JSON support, but they're all pretty hacky, frankly. More ideal would have been to design a better language for the client/server database model from the start, but we are no doubt in too deep now. Worse is better applies.

kragen 1y ago

I agree about first-class nested relations, but I don't agree about transactions. Codd was writing 10 years before the idea of transactional semantics was formulated, and transactions are in fact to a great extent a real alternative to normalization. Codd was working to make inconsistent states unrepresentable in the database, but transactions make it a viable alternative to merely avoid committing inconsistent states. And I'm not sure what you mean by "quick", but anything you could do 35 years ago in 10 milliseconds is something you can do today in 100 microseconds.

andyferris 1y ago
It's not about just _transactions_. What you wrote is 100% correct. It's specifically about _fast_ transactions in the OLTP context. When talking about the 1970s (not 1990s) and tape drives, rewriting a whole nested dataset to apply what we'd call a "small patch" nowadays wasn't a 10 millisecond job - it could feasibly take 10s of seconds or minutes or hours. That a small patch to the dataset can happen almost instantly - propagated to it's containing relation, and a handful of subordinate index relations - was the real advance in OLTP DBs. (Of course this never has and never will help with "large patches" where the dataset is mostly rewritten, and this logic doesn't apply to the field of analytics). Perhaps Codd "lucked out" here or perhaps he didn't have the modern words to describe his goal, but nonetheless I think this is why we still use flat relations as our systems of record. Analytical/OLAP systems do vary a lot more!
- kragen 1y ago
  Hmm, but I think people doing OLTP in the 01970s were largely using things like IMS, which used ISAM, on disk, to be able to do small updates to large nested datasets very quickly? And for 20+ years one of the major criticisms of relational databases was that they were too slow? And that even today the remaining bastions of IMS cite performance as their main reason for not switching to RDBMSes? I think that if you're processing your transactions on tape drives, your TP isn't OL; it's offline transaction processing. I think Codd's major goal was decoupling program structure from on-disk database structure, not improving performance. There's a lot of the history I don't know, though.