5 ms·

relational databases are secretly supposed to be logic programming languages like prolog, each “table”, (relation) representing a predicate function. each row (

relational databases are secretly supposed to be logic programming languages like prolog, each “table”, (relation) representing a predicate function. each row (tuple) representing a fact about the world that satisfies the predicate. with proper structure and labelling you can ask very sophisticated questions. somewhere along the line people forgot this and typically just dump spreadsheets in, to satisfy the fields in a form or an app screen, then think they need a graph database to represent relationships. it is unfortunate that SQL based systems became accidentally synonymous with the “relational model” as it actually mostly gets in the way of implementing the relational model. oh well.

swingline-747 8y ago
No, that's an misinformed, all-or-nothing, gross-mischaracterization. Data management systems are a toolbox. The core tenent, distilling ACID, of RDBMSes is referential integrity: that what a foreign key points to can't simply disappear and leave a dangling reference. If you have a database of customers and their orders, you want orders destroyed with that customer, and the referring customer can't simply vanish. There are several delete dependent policies, depending on the use-case. Key-Value stores (NoSQL) typically make no such guarantees. KVSes and Document store are fantasic for less critical and more nondeterministic data like user profiles, likes and tags... things that don't matter as much, as say, bank accounts transactions. Only a fool would use eventual consistency where it could be gamed by making maximum simulateneous withdrawls of say a $1000 at multiple locations. This is where atomicity and consistency are very important. Use the right underlying data guarantees for the task at hand... not like a consulting company whom tried to turn a DBMS into a NoSQL by having a single database table store everything. IIRC the columns looked like this: Key | Value | Type | Notes Good luck indexing that pile of slow, and say hello to full-table scans for nearly every fetch.
- michaelmior 8y ago
  > more nondeterministic data What does that mean?
- ZenPsycho 8y ago
  I am not entirely sure what this is in response to. did you hit reply on the wrong comment perchance?
avodonosov 8y ago
Not secretly
- ZenPsycho 8y ago
  well, it's not a secret, maybe just a truth nobody seems to want to know or care about.
paulddraper 8y ago
> SQL based systems became accidentally synonymous with the “relational model” as it actually mostly gets in the way of implementing the relational model Please explain? What makes SQL bad a relational databases, and what is better?
- 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...
zimablue 8y ago
Datomic can be run on top of SQL, which is kind of ironic I guess. relational algebra => implemented incompletely in a SQL RDBMS => with an /actual/ logic programming language thrown on top of THAT using a couple of tables as KV (I think that's how it works).

swingline-747 8y ago

No, that's an misinformed, all-or-nothing, gross-mischaracterization. Data management systems are a toolbox. The core tenent, distilling ACID, of RDBMSes is referential integrity: that what a foreign key points to can't simply disappear and leave a dangling reference. If you have a database of customers and their orders, you want orders destroyed with that customer, and the referring customer can't simply vanish. There are several delete dependent policies, depending on the use-case. Key-Value stores (NoSQL) typically make no such guarantees. KVSes and Document store are fantasic for less critical and more nondeterministic data like user profiles, likes and tags... things that don't matter as much, as say, bank accounts transactions. Only a fool would use eventual consistency where it could be gamed by making maximum simulateneous withdrawls of say a $1000 at multiple locations. This is where atomicity and consistency are very important. Use the right underlying data guarantees for the task at hand... not like a consulting company whom tried to turn a DBMS into a NoSQL by having a single database table store everything. IIRC the columns looked like this: Key | Value | Type | Notes Good luck indexing that pile of slow, and say hello to full-table scans for nearly every fetch.

michaelmior 8y ago
> more nondeterministic data What does that mean?
ZenPsycho 8y ago
I am not entirely sure what this is in response to. did you hit reply on the wrong comment perchance?

avodonosov 8y ago

Not secretly

ZenPsycho 8y ago
well, it's not a secret, maybe just a truth nobody seems to want to know or care about.

paulddraper 8y ago

> SQL based systems became accidentally synonymous with the “relational model” as it actually mostly gets in the way of implementing the relational model Please explain? What makes SQL bad a relational databases, and what is better?

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

zimablue 8y ago

Datomic can be run on top of SQL, which is kind of ironic I guess. relational algebra => implemented incompletely in a SQL RDBMS => with an /actual/ logic programming language thrown on top of THAT using a couple of tables as KV (I think that's how it works).