::scr Data integrity and why databases suck

[Simon Kinahan]

> Ah, that's a different issue. Those are semantic bugs, I'm talking about
> consistency checking to prevent data corruption. SQL databases do this,
but
> with an annoyingly crude data model, and at too low a level... they tell
you
> when something's gone awry by complaining, but it's usually much too late
at
> the stage when the data tries to hit the database; the original bug can
still
> be really hard to find.

This is something that has been preying on my mind recently. My current job
involves writing middleware tools that plug into database-backed systems
either just above or just below an object-relational mapping layer. It seems
to me that the currently fashionable model for server-side applications,
where there's some kind of application server, written in an OO language
(usually Java), which stores all its data in a relational database, is
wrong.

Data intergrity is a big part of the problem: each check gets done three
times. Once as constraints on the database; once as "business logic", and
once as verification checks on the client.

On top of that, most of the vast and expensive apparatus built into
relational databases in never used. Object-oriented tools  that talk to
databases make almost no use of the kind of complex, set based operations
databases specialise in implementing efficiently. Instead, they spend their
time inserting, updating and deleting single rows, and occasionally doing a
multi-row "select". Your expensive BEA application server never even uses
your even more expensive Oracle database to do a join.

Simon