4 ms·

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL;

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.

Ericson2314 9y ago
Some ideas: 1. Rename the constraints to mimick the classification of keys described here. E.g. require UUIDs for artificial keys. 2. Make joins with foreign artificial keys much lighter syntactically than free form joins on natural keys. Make joins involving non-ubique columns in the WITH (today's syntax) harder still. People should be able think of foreign keys as references to be dereferenced separate from full joins. 2. Going further, break apart "tables" to represent the are used for different things people use them for, e.g. key-value maps vs "join tables" (a term I had never heard before but immediately could define from experience) vs cannonical data. In math terms, distinguish sets of aggregate data from relations. Having full algebraic data types for the sets of agreggate data but not relations could help. (maps are a special case of relations.) Another final related distinction is tables-as-types vs tables-as- collections. I suppose that relates (no pun :)) back to what joins are allowed. 4. Keep the references pointing in the right direction. For example, à la Rust and capability theory, the owner should point to it's uniquely owned children. But, in SQL today, we instead invert the back reference, having the children refer to their unique parent. Supporting lists (of children) and unaliased foreign keys directly would make the cannonical back-reference an implementation detail.
hug 9y ago
Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks, the way it writes, or the way it handles. We're not using it because we've tried to use ORMs and those ORMs have proved conclusively better across use cases than SQL. The reason we use it is because we don't yet have a better solution. If you can write a better solution than SQL I would love to hear about it: I absolutely loathe SQL, but I use it because out of everything I've seen, it's the simplest and most elegant solution to the problem that I've ever come across. So yeah, while we could possibly and should definitely make SQL better if we can, I don't think that suggesting that we just re-do it from scratch is the way to go. That way lies madness, and possibly javascript frameworks.
- Terr_ 9y ago
  I think you're discounting network-effects and historical inertia. Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."
  - hug 9y ago
    I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been? And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. I'm not saying SQL is the definitive end of database query languages, of course, because that would be silly. There is, almost certainly, a more objective truth. What I'm saying is good luck finding it; It's only semi-cynically that I say I wish you luck finding a better language than SQL. I honestly, with all my heart, wish that SQL could be replaced with something better, because I hate SQL. When some guy comes along and is like "we should do a better SQL!" the answer is yes, we should, but I'm not exactly going to hold my breath,
    - tome 9y ago
      > And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable conclusion of your claim.
      - marcosdumay 9y ago
        Again, can you name any study that found a benefit with a non-laughable p-value? If nobody can, then one just has to conclude they are indistinguishable.

Ericson2314 9y ago

Some ideas: 1. Rename the constraints to mimick the classification of keys described here. E.g. require UUIDs for artificial keys. 2. Make joins with foreign artificial keys much lighter syntactically than free form joins on natural keys. Make joins involving non-ubique columns in the WITH (today's syntax) harder still. People should be able think of foreign keys as references to be dereferenced separate from full joins. 2. Going further, break apart "tables" to represent the are used for different things people use them for, e.g. key-value maps vs "join tables" (a term I had never heard before but immediately could define from experience) vs cannonical data. In math terms, distinguish sets of aggregate data from relations. Having full algebraic data types for the sets of agreggate data but not relations could help. (maps are a special case of relations.) Another final related distinction is tables-as-types vs tables-as- collections. I suppose that relates (no pun :)) back to what joins are allowed. 4. Keep the references pointing in the right direction. For example, à la Rust and capability theory, the owner should point to it's uniquely owned children. But, in SQL today, we instead invert the back reference, having the children refer to their unique parent. Supporting lists (of children) and unaliased foreign keys directly would make the cannonical back-reference an implementation detail.

hug 9y ago

Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks, the way it writes, or the way it handles. We're not using it because we've tried to use ORMs and those ORMs have proved conclusively better across use cases than SQL. The reason we use it is because we don't yet have a better solution. If you can write a better solution than SQL I would love to hear about it: I absolutely loathe SQL, but I use it because out of everything I've seen, it's the simplest and most elegant solution to the problem that I've ever come across. So yeah, while we could possibly and should definitely make SQL better if we can, I don't think that suggesting that we just re-do it from scratch is the way to go. That way lies madness, and possibly javascript frameworks.

Terr_ 9y ago
I think you're discounting network-effects and historical inertia. Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."
- hug 9y ago
  I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been? And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. I'm not saying SQL is the definitive end of database query languages, of course, because that would be silly. There is, almost certainly, a more objective truth. What I'm saying is good luck finding it; It's only semi-cynically that I say I wish you luck finding a better language than SQL. I honestly, with all my heart, wish that SQL could be replaced with something better, because I hate SQL. When some guy comes along and is like "we should do a better SQL!" the answer is yes, we should, but I'm not exactly going to hold my breath,
  - tome 9y ago
    > And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable conclusion of your claim.
    - marcosdumay 9y ago
      Again, can you name any study that found a benefit with a non-laughable p-value? If nobody can, then one just has to conclude they are indistinguishable.