6 ms·

Two common mistakes when using databases

96 points by Rexxar 17y ago

aaw 17y ago
A well thought-out post and a good read, but both of the author's points about the limitations of relational databases are a little misleading. The first, "Every single element in a relation (aka table) has to be exactly the same type" is true in many relational databases, but isn't necessary: SQLite, for example, is implemented with a nice form of dynamic typing where individual columns can take on multiple data types. Second, "SQL isn't even Turing-complete" is sort of a folk theorem in computer science that isn't necessarily true anymore, especially with all of the proprietary SQL dialects in existence: the original standard, SQL92, is basically equivalent to relational algebra, which has the expressive power of first-order logic - it's so weak it can't even express concepts like graph reachability. But constructs like recursive queries have been added to the SQL standard since SQL92 which may make even standard SQL Turing Complete.
- gaius 17y ago
  PL/SQL and T-SQL are certainly Turing complete.
  - samuel 17y ago
    AFAIK PL/SQL is not "Oracle's SQL". It's executed by a different engine and you get context switches when you execute SQL Code (don't know about T-SQL). But Oracle's SQL is by itself Turing complete since the introduction of the "Model" clause (see here: http://www.adellera.it/investigations/nocoug_challenge/index.html http://www.adellera.it/investigations/nocoug_challenge/index... how you can compute the FFT using it). Moreover, I read somewhere that the engines that implement the "recursive with" are also Turing Complete, but can't recall where.
- Confusion 17y ago
  Second, "SQL isn't even Turing-complete" is sort of a folk theorem And it doesn't mean anything, unless you specify its exact limitations. If they are merely theoretical limitations that nobody uses in practice: who cares?
rbranson 17y ago
This seems like an aimless rant. Rails (and Hibernate to my knowledge) uses SQL for exactly what he recommends: filtering, aggregation, and joining. The RDBMS is a very real and concrete concept in ActiveRecord. If you don't know SQL, it's going to make it difficult to get beyond the most basic ActiveRecord functionality.
- mattmcknight 17y ago
  Agreed. It's complete ignorance on the author's part to suggest that Rails and Hibernate serialize objects into a single cell. acts_as_nested_set even provides a decent way of dealing with tree structured data in a relational store. His second part is off base as well, the challenges of serialization in strongly typed languages have little to do with wire protocols. He seems to be suggesting that adding a translation layer would solve the problem. It rather seems that would still fail if deserialization were used, as the translation layer would then become brittle. Rather it would seem to be more wise to step away from a serialization based persistence approach, while it might be a good idea for wire protocols, since the data is more ephemeral.
  - loup-vaillant 17y ago
    About the serialization, I understood he basically said "don't use that". Not that an explicit, manual translation layer would solve any problem. It just makes you aware of it. Translation layers are brittle even if they are automatic. The only solution is to have a protocol (or file format) that change as little as possible. An automatic marshalling facility doesn't encourage you to do so. I see one case where automatic serialization is harmless, though: when you have the guarantee that the program producing the data and the program receiving it are of the same version and don't talk to the outside world. Temporary files used to recover crashed sessions are a good example.
- fauigerzigerk 17y ago
  ActiveRecord and Hibernate can certainly do everything SQL can do. But the modeling and data access patterns they encourage are inconsistent with the strengths of relational databases and the relational model. The point of using a relational database system is to think in sets of tuples because doing that makes life simpler (for some types of tasks). You apply one of a handful of set operations to a set and you get back another set of tuples. Once you start assigning types and custom operations to particular combinations of attributes, you lose this recursive transformability and hence the simplicity of the model. What you get is a horribly complex monster. The whole OO idea is a tangled mess. ActiveRecord and Hibernate do their best to help you view the database as a tangled mess as well.
didroe 17y ago
I liked the OPs reply to this message: >Thanks for your reply. It was quite interesting, though I get the feeling you used my question solely as a trigger to share with us a long-held dissatisfaction with the current state of affairs concerning the use of databases, regardless of whether it actually applies to my particular problem.
Kirby 17y ago
For beginners, this is definitely good advice. Particularly the first point - if you're using a relational database, and don't structure your data around its strengths, you'll take a profound performance hit. And sure, don't pass around data structures if you don't know what you're doing, and MySQL is a pretty crummy place to put them. However, once you're doing real work, sometimes translating to XML and back is extraordinarily expensive. The best approach is a hybrid. Use the database to store things relationally if possible, and using defined APIs for sure. And if your translation stage is expensive, use something like memcached to make sure you do that translation as infrequently as possible. _This_ is the layer that it's appropriate to store serialized data structures at. It's not permanent storage, you can blow it away when you change the internal structures, and nobody external is relying on it. But you end up, in most programs, with even more speed benefits than if you'd stored it in the database like this to begin with - the initial build can be expensive, but then you're reading basically from memory. (Not all data is well suited to this approach, but if your data is read frequently and written to infrequently - there's few things you can do to increase performance more than this.)

96 points by Rexxar 17y ago

aaw 17y ago

A well thought-out post and a good read, but both of the author's points about the limitations of relational databases are a little misleading. The first, "Every single element in a relation (aka table) has to be exactly the same type" is true in many relational databases, but isn't necessary: SQLite, for example, is implemented with a nice form of dynamic typing where individual columns can take on multiple data types. Second, "SQL isn't even Turing-complete" is sort of a folk theorem in computer science that isn't necessarily true anymore, especially with all of the proprietary SQL dialects in existence: the original standard, SQL92, is basically equivalent to relational algebra, which has the expressive power of first-order logic - it's so weak it can't even express concepts like graph reachability. But constructs like recursive queries have been added to the SQL standard since SQL92 which may make even standard SQL Turing Complete.

gaius 17y ago
PL/SQL and T-SQL are certainly Turing complete.
- samuel 17y ago
  AFAIK PL/SQL is not "Oracle's SQL". It's executed by a different engine and you get context switches when you execute SQL Code (don't know about T-SQL). But Oracle's SQL is by itself Turing complete since the introduction of the "Model" clause (see here: http://www.adellera.it/investigations/nocoug_challenge/index.html http://www.adellera.it/investigations/nocoug_challenge/index... how you can compute the FFT using it). Moreover, I read somewhere that the engines that implement the "recursive with" are also Turing Complete, but can't recall where.
Confusion 17y ago
Second, "SQL isn't even Turing-complete" is sort of a folk theorem And it doesn't mean anything, unless you specify its exact limitations. If they are merely theoretical limitations that nobody uses in practice: who cares?

rbranson 17y ago

This seems like an aimless rant. Rails (and Hibernate to my knowledge) uses SQL for exactly what he recommends: filtering, aggregation, and joining. The RDBMS is a very real and concrete concept in ActiveRecord. If you don't know SQL, it's going to make it difficult to get beyond the most basic ActiveRecord functionality.

mattmcknight 17y ago
Agreed. It's complete ignorance on the author's part to suggest that Rails and Hibernate serialize objects into a single cell. acts_as_nested_set even provides a decent way of dealing with tree structured data in a relational store. His second part is off base as well, the challenges of serialization in strongly typed languages have little to do with wire protocols. He seems to be suggesting that adding a translation layer would solve the problem. It rather seems that would still fail if deserialization were used, as the translation layer would then become brittle. Rather it would seem to be more wise to step away from a serialization based persistence approach, while it might be a good idea for wire protocols, since the data is more ephemeral.
- loup-vaillant 17y ago
  About the serialization, I understood he basically said "don't use that". Not that an explicit, manual translation layer would solve any problem. It just makes you aware of it. Translation layers are brittle even if they are automatic. The only solution is to have a protocol (or file format) that change as little as possible. An automatic marshalling facility doesn't encourage you to do so. I see one case where automatic serialization is harmless, though: when you have the guarantee that the program producing the data and the program receiving it are of the same version and don't talk to the outside world. Temporary files used to recover crashed sessions are a good example.
fauigerzigerk 17y ago
ActiveRecord and Hibernate can certainly do everything SQL can do. But the modeling and data access patterns they encourage are inconsistent with the strengths of relational databases and the relational model. The point of using a relational database system is to think in sets of tuples because doing that makes life simpler (for some types of tasks). You apply one of a handful of set operations to a set and you get back another set of tuples. Once you start assigning types and custom operations to particular combinations of attributes, you lose this recursive transformability and hence the simplicity of the model. What you get is a horribly complex monster. The whole OO idea is a tangled mess. ActiveRecord and Hibernate do their best to help you view the database as a tangled mess as well.

didroe 17y ago

I liked the OPs reply to this message: >Thanks for your reply. It was quite interesting, though I get the feeling you used my question solely as a trigger to share with us a long-held dissatisfaction with the current state of affairs concerning the use of databases, regardless of whether it actually applies to my particular problem.

Kirby 17y ago

For beginners, this is definitely good advice. Particularly the first point - if you're using a relational database, and don't structure your data around its strengths, you'll take a profound performance hit. And sure, don't pass around data structures if you don't know what you're doing, and MySQL is a pretty crummy place to put them. However, once you're doing real work, sometimes translating to XML and back is extraordinarily expensive. The best approach is a hybrid. Use the database to store things relationally if possible, and using defined APIs for sure. And if your translation stage is expensive, use something like memcached to make sure you do that translation as infrequently as possible. _This_ is the layer that it's appropriate to store serialized data structures at. It's not permanent storage, you can blow it away when you change the internal structures, and nobody external is relying on it. But you end up, in most programs, with even more speed benefits than if you'd stored it in the database like this to begin with - the initial build can be expensive, but then you're reading basically from memory. (Not all data is well suited to this approach, but if your data is read frequently and written to infrequently - there's few things you can do to increase performance more than this.)