4 ms·

There are at least two aspects of this: 1) SQL is not entirely faithful to the underlying logical principles of the relational model. Chris Date's "Database in

by grzm 8y ago

There are at least two aspects of this: 1) SQL is not entirely faithful to the underlying logical principles of the relational model. Chris Date's "Database in Depth: Relational Theory for Practitioners"[0] or "SQL and Relational Theory: How to Write Accurate SQL Code"[1] are good texts that go into this in detail. There's plenty of stuff online if you search for it. For a very brief example of criticisms, you can take a look at Wikipedia: (https://en.wikipedia.org/wiki/SQL#Criticisms https://en.wikipedia.org/wiki/SQL#Criticisms). [0]: https://www.goodreads.com/book/show/573821.Database_in_Depth https://www.goodreads.com/book/show/573821.Database_in_Depth [1]: https://www.goodreads.com/book/show/6219481-sql-and-relational-theory https://www.goodreads.com/book/show/6219481-sql-and-relation... 2) Most SQL systems provide a 1-to-1 mapping of tables to underlying storage structure which places limits on performance, while relations are logical and independent of the implementation. Other than indexing strategies, there hasn't been a lot of work in production systems to truly abstract storage from logical representation. They've been kind of stuck in their thinking. This second point isn't as important as the first, however, and more off-topic with respect to your question.

paulddraper 8y ago
On the first point, I don't have either of those books, but IMO the Wikipedia complaints aren't compelling. On the second point, many SQL databases offer indexing [1] (as you said), clustering [2] (arranging a table according an index), and partitioning [3]. I think database designers are very conscious of the difference between the logical relational model and the implementation details. For example, this [4] conversation on the PostgreSQL mailing list about unique indexes vs. unique constraints. I also think that in practice there just aren't many other ways to abstract the logical model from the physical storage that gives you something substantial. IMO, this is one of the successes of the relational model: the logical model and the physical implementations don't need to differ for you to have a really good situation all around. [1] https://www.postgresql.org/docs/10/static/indexes-intro.html https://www.postgresql.org/docs/10/static/indexes-intro.html [2] https://www.postgresql.org/docs/10/static/sql-cluster.html https://www.postgresql.org/docs/10/static/sql-cluster.html [3] https://www.postgresql.org/docs/10/static/ddl-partitioning.html https://www.postgresql.org/docs/10/static/ddl-partitioning.h... [4] https://www.postgresql.org/message-id/CAO8h7BJMX5V1TqzScTx2Nr1jH5iUFG8A071y-g1b_kdzpu9PDw%40mail.gmail.com https://www.postgresql.org/message-id/CAO8h7BJMX5V1TqzScTx2N...
- ZenPsycho 8y ago
  the point is though, it's a little bit like a broken calculator. "The Relational Model" represents a logical model as a kind of pure piece of mathematics, and presents a system that isn't just dump disk persistence but actually a logic induction engine. However, this is not how SQL is used in practice. It is possible to implement the relational model in SQL, however if you assume SQL databases are supposed to be implementations of it in and of themselves, they violate the commutative, associative and distributive laws of the relational algebra- it's a bit like a calculator that sometimes gives you 2+2=5 (because it's faster to give wrong results sometimes). In addition, the ergonomics of a typical SQL database system do not make mathematical/logical soundness the easiest thing to achieve. Most developers don't know or care about logical induction or mathematical soundness, they just want a place to dump their objects. That said, out of the open source SQL database systems, postgresql actually does try to implement the relational model properly. But I don't have enough direct experience with it to say definitively how well it does. How many millions of dollars hss NULL != NULL cost the world?
  - paulddraper 8y ago
    > what is better?
    - ZenPsycho 8y ago
      well, obviously "worse is better". historically, SQL has been "Good enough" and was sponsored by IBM/Oracle that nobody has bothered to try and implement the relational model correctly, except for perhaps postgres (which used to have its own query language before bolting SQL onto its core engine) what I hope people get out of my comments is not about selecting the right system for today- A better thing does not necessarily exist yet-- But at least understand the relational model well enough to see what its utility is. Functional programming and Logic programming are coming back in fashion, and maybe someone here will build the better thing. I don't think "nosql" databases or graph databases are necessarily the better thing. (but they might be for certain usecases). Though I think relational db systems could learn something about developer ergonomics from them. Interfacing with a DB using a fortran inspired query language is archaic. Constructing the queries using string concatenation has been a security nightmare. You could build something waaayyy better off of prolog or mercury, full programming languages with the inductive logic engine at the core of their paradigm, with a database system integrated in, and expose a graphql api for your isomorphic react web app cos that's how we do in 2018. Even microsoft's linq is a massive improvement over using SQL. Language integration, strong typing. These are things we like, right?
MaxBarraclough 8y ago
> Most SQL systems provide a 1-to-1 mapping of tables to underlying storage structure which places limits on performance Is that the fault of the SQL language? Also, serious DBMSs are generally engineered by pretty serious people, no? Are they really implementing such a mapping for no good reason?
olavk 8y ago
> Most SQL systems provide a 1-to-1 mapping of tables That is a bit weird criticism, since the whole point of relational databases is that the physical storage structure is abstracted away. SQL does not prevent databases from using different storage structures.

by grzm 8y ago

paulddraper 8y ago

On the first point, I don't have either of those books, but IMO the Wikipedia complaints aren't compelling. On the second point, many SQL databases offer indexing [1] (as you said), clustering [2] (arranging a table according an index), and partitioning [3]. I think database designers are very conscious of the difference between the logical relational model and the implementation details. For example, this [4] conversation on the PostgreSQL mailing list about unique indexes vs. unique constraints. I also think that in practice there just aren't many other ways to abstract the logical model from the physical storage that gives you something substantial. IMO, this is one of the successes of the relational model: the logical model and the physical implementations don't need to differ for you to have a really good situation all around. [1] https://www.postgresql.org/docs/10/static/indexes-intro.html https://www.postgresql.org/docs/10/static/indexes-intro.html [2] https://www.postgresql.org/docs/10/static/sql-cluster.html https://www.postgresql.org/docs/10/static/sql-cluster.html [3] https://www.postgresql.org/docs/10/static/ddl-partitioning.html https://www.postgresql.org/docs/10/static/ddl-partitioning.h... [4] https://www.postgresql.org/message-id/CAO8h7BJMX5V1TqzScTx2Nr1jH5iUFG8A071y-g1b_kdzpu9PDw%40mail.gmail.com https://www.postgresql.org/message-id/CAO8h7BJMX5V1TqzScTx2N...

ZenPsycho 8y ago
the point is though, it's a little bit like a broken calculator. "The Relational Model" represents a logical model as a kind of pure piece of mathematics, and presents a system that isn't just dump disk persistence but actually a logic induction engine. However, this is not how SQL is used in practice. It is possible to implement the relational model in SQL, however if you assume SQL databases are supposed to be implementations of it in and of themselves, they violate the commutative, associative and distributive laws of the relational algebra- it's a bit like a calculator that sometimes gives you 2+2=5 (because it's faster to give wrong results sometimes). In addition, the ergonomics of a typical SQL database system do not make mathematical/logical soundness the easiest thing to achieve. Most developers don't know or care about logical induction or mathematical soundness, they just want a place to dump their objects. That said, out of the open source SQL database systems, postgresql actually does try to implement the relational model properly. But I don't have enough direct experience with it to say definitively how well it does. How many millions of dollars hss NULL != NULL cost the world?
- paulddraper 8y ago
  > what is better?
  - ZenPsycho 8y ago
    well, obviously "worse is better". historically, SQL has been "Good enough" and was sponsored by IBM/Oracle that nobody has bothered to try and implement the relational model correctly, except for perhaps postgres (which used to have its own query language before bolting SQL onto its core engine) what I hope people get out of my comments is not about selecting the right system for today- A better thing does not necessarily exist yet-- But at least understand the relational model well enough to see what its utility is. Functional programming and Logic programming are coming back in fashion, and maybe someone here will build the better thing. I don't think "nosql" databases or graph databases are necessarily the better thing. (but they might be for certain usecases). Though I think relational db systems could learn something about developer ergonomics from them. Interfacing with a DB using a fortran inspired query language is archaic. Constructing the queries using string concatenation has been a security nightmare. You could build something waaayyy better off of prolog or mercury, full programming languages with the inductive logic engine at the core of their paradigm, with a database system integrated in, and expose a graphql api for your isomorphic react web app cos that's how we do in 2018. Even microsoft's linq is a massive improvement over using SQL. Language integration, strong typing. These are things we like, right?

MaxBarraclough 8y ago

> Most SQL systems provide a 1-to-1 mapping of tables to underlying storage structure which places limits on performance Is that the fault of the SQL language? Also, serious DBMSs are generally engineered by pretty serious people, no? Are they really implementing such a mapping for no good reason?

olavk 8y ago

> Most SQL systems provide a 1-to-1 mapping of tables That is a bit weird criticism, since the whole point of relational databases is that the physical storage structure is abstracted away. SQL does not prevent databases from using different storage structures.