I've been ranting about databases lately on my use.perl journal and have been struggling to figure out why SQL just doesn't quite seem fit the way my mind works. Now, by going back to the basics, I have a much better understanding. The problem is that relational theory appears to be fairly solid but SQL largely ignores it. Further, programmers who can't even properly describe 1NF are ranting about database problems when it's the interface to the database, SQL, which is causing the problems. (Note: until recently, I also had pretty serious misconceptions about 1NF).

Maybe my work with logic programming has tainted me (and most Prolog databases seem poorly designed, too), but the frustrations I've had with SQL not really being set-oriented are being explained quite nicely by Date's work. The bugs and limitations I keep hitting are ones that are a direct consequence of SQL ignoring the relational model. So instead of being able to work with a declarative language that just lets us specify what we want, we have to spend an inordinate amount of time worrying about low-level details which the language could protect us from. Consider:

SELECT DISTINCT c.name
FROM   customers c, orders o
WHERE  o.id = c.id
  AND  o.amount > 1000
[download]

If that's buried in a 100 line SQL statement that's going to be pretty easy to miss but it's almost certainly a bug. You probably want something like:

In the relational model that query wouldn't even compile because the order id would be a different type than customer id (even though they both might look like integers) and a comparison between the two would be invalid. However, SQL is it's currently implemented doesn't care. Once again, we have an example of SQL's faulty implementation allowing bugs to occur which, if it respected the relational model, would cause the system to crash and burn pretty quickly rather than return spurious results. While it would still be possible to write bad queries, we'd have a huge subset of bugs go away.

I should also point out that current database implementations require databases to be in a consistent state within a transaction but can be in an inconsistent state within a transaction. Date's theory require that every atomic operation require the database to be in a consistent state at the end of the operation. By creating syntax which would allow this, many transactions would probably be unecessary, thus freeing us from yet another low-leve detail we shouldn't be worrying about.

Of course, you have tons of practical experience here, so feel free to ignore me :)

By the way, if you're going to be at the next PDX.pm meeting, I wouldn't mind getting together to chat about this.