Why schemas and not code?

[Hildo Biersma]

On Mon, Jun 26, 2006 at 02:02:28AM -0700, Ovid wrote:
> This brings me to the question I can't figure out:  why is it that
> otherwise competent programmers who admit that their code is a mess
> fail to see that their databases might be also?  Since they can describe 
> virtually nothing about relational theory, one might assume (incorrectly) 
> that they might realize they have a weakness there.

I don't know - but wearing my DBA hat I must say it drives me crazy.

I come from the B&D school of RDBMS design where I'd like to see as
much of the requirements encoded in the database as possible - nicely
modeled, something close to 3rd normal form (with exceptions for
performance and join avoidance), primary and foreign keys, constraints,
triggers, the lot.

The reason is simple: once you have that in place, new team members can
write crappy code with very little chance of causing structural problems
in the database.  Poorly written code that tries an invalid insert,
update or delete will just fail to commit.

So I see poorly designed databases as a missed opportunity, for which
each and every programmer will pay in terms of extra work in the code,
extra work in code reviews, and extra work in cleaning up outages caused
by said code.  Frequently combined with poor query performance that
cannot be solved by just buying faster database hardware.

I even see this in terms of SQL queries - people will write programs
that generate 30-page (!) SQL queries (just selects with subselects);
when faced with said queries and the resulting poor performance, will
refuse to consider either a table redesign (poor database design that
the queries have to work around) or a code redesign (database design
okay but no idea how to write a query, let alone generate one).

Ah well, I should end my rant - sorry if I let the day job's
frustrations show...

Hildo