Hello Eric,

I supose you are new in the position ;-)

I have the same experience, and I never have found an IT director who
thinks like you.

The problem is not in modern database theory, the problem is that most
developers don't know the foundations of their profession and common
sense is very uncommon.

In my country most people who develop business systems never read a
database theory book. The few ones who studied a database course in
the university never understood database theory very well at all, and
they forgot almost everything just on the end of the final
examination.

The textbook we used didn't have any chapter devoted to database
integrity, only a few pages about the poor SQL declarative integrity
support, and not covered with exercices. The whole Relational Model
was dispatched in five hours or so, and taught with many mistakes and
misconceptions.

The vendor's training materials are usually even worse.

And we also have all that abject oriented programming stuff saying
that RDBMS's are nothing but silly and cumbersome register buckets.

It is not only integrity and normalization. Most developers I know are
not able to write non trivial queries and they load the data in the
applications using simple queries, make several iterations on the
registers, and send the data back to the DBMS.

In the business software industry, technical incompetence is the norm,
and the develpment tools we have are awful.

Regards

Integrity is rare in general.  When it comes to db's, even rarer, partly
because of pressures on naif's to deliver faster on machines that would
be faster than needed if only people  took the time to determine the
essential requirements of systems.

Probably because the talented people at the software vendors are the sales droids.
The system was originally designed by someone who didn't have a clue and they managed
to sell a few copies and kept on going.   Any competent database or developers or
systems analyst types likely didn't stay very long either.   And management is
cracking the whips on generally mediocre people to get the product out as quickly as
they can.  "To heck with bugs.  It's features that count."

So how's that for generalizing?  <smile>

I really enjoy reading the WTF and other similar blogs for such stories.  And then
there's comp.risks where mistakes have killed people.

Tony

I think that part of the problem is DB design and Application design are
really different types of abstraction.  For application programmers,
dealing with DB constraints is tedious.

The truth is that whenever your "Application" calls for persistence, it
is no longer just an "Application"; it has become a "System".  System
design is a higher level abstraction.

Moving from Application design to System design is /almost/ a natural
progression, and many engineers traverse the barrier without ever
realizing and without learning the other aspects of System design. This
includes learning proper DB design.

I admit that I fell into that category for some time. My background has
been Application design, but I've started to appreciate the concept of
constraints at ever level of the System. I even sometimes wish that the
DB could do more validation than it does, even if it makes things a
little more "tedious".  In this case, tedious just means the hard
problem is already solved.

Let's separate out the issues of data normalization and data integrity
enforcement, even though they are clearly interrelated.

For a period of about 10 years, I was a database consultant, called in to
fix databases which were broken beyond the ability of local talent to
repair.  This could mean "broken" at a very mechanical level, where the DBMS
was refusing to allow connections to the database due to DBMS detected
corruption.  It could also mean "broken" at a very high level, where two
copies of the same database had diverged in their model of the subject
matter domain,  and the data couldn't be used together. Of course, the
nature of my work was such that I had a bias towards observing databases
that had been badly designed or badly managed.

But, nevertheless,  here's the conclusions I draw.  An enormous percentage
of the mission critical databases are intially designed by programmers who
are highly competent at programming, but have no formal training in database
design and no practical experience in database management.  Highly competent
programmers often have the misconception that database design and
administration involve a fairly trivial subset of skills they already have.
By the time they find out differently, it's already too late.

Programmers tend to want to enforce data integrity at the application level,
because that's where their strength lies.  Certainly well written
application programs will not be needlessly careless about data integrity.
There are certain cases where it's wise for the application program to
enforce some constraints,  even if the database will later enforce the same
constraints all over again.  The NOT NULL constraint comes to mind as an
example.

But the failure to enforce data integrity at the database level, together
with the failure to enforce standards of data integrity across all the
application programs, often produces a situation where data integrity falls
by the wayside.  The people who have had a chance to learn from such a
bitter experience often never get another chance to design a database from
scratch.

I am afraid I have to confess (not for the first time) that I am a
prostitute.  I work for the kind of company that builds the kind of
systems you use.

If we ever submitted a bid that included a plan to do a lot of
up-front analysis and up-front database definition (including all the
constraints we could discover, but minus the ones SQL can't even
describe), before we ever delivered a line of executable application
code, we would lose out to the company that didn't bother with all that
fancy nonsense.  And that would be true even if our overall bid was
lower.

This is not speculation.  It happened repeatedly until we were starved
into submission.  The only very slight satisfaction I have is that one
of our present customers once rejected one of our early proposals, for
exactly the reasons I listed, and the IT director was kind enough to
tell me recently that if he had to do that project over again he'd do
it the way we had offered to do it.

But in general, you can't sell pearls to pigs.  (And if you're not a
pig don't expect to buy pearls from a pig-swill merchant.)

On Oct 5, 11:30 am, eric.bouchardlefeb...@gmail.com wrote:

For what it's worth .....

The situation, I think, is actually getting better. We can and
probably always will find examples where the folk in charge of the IT
were clueless and the result was a complete debacle.

But - I recently spent two days in the company of two very senior IT
people from a very successful Web Company. At one point in the
meetings -- it was a conference about the challenges posed by the
kinds of very, very large databases generated by sensor data -- an
unrepentant OO-DBMS type proclaimed that the only way to make this lot
work was to tie the data management very closely to the programming
language in order to overcome the dreaded impedance mismatch and you
know how this story goes. . .

 Our 'senior IT guy' stood up and basically called the OO-DBMS guy a
moron. He then gave a 5 minute rant that might have come out of the
mouths of any of our more virulent relational bigots. Programmers --
he asserted -- were very, very, VERY bad for the business. He wanted
naked access to structured data by the company's analysts. Central to
that? Get the data integrity right.

  Later, over a cocktail or two, we chatted. In his shop EVERY table
has a declared primary key, every column without NOT NULL and DEFAULT
must have a documented reason for the deviation. Their code review
check list includes questions like "If your code has nested cursor
declarations and looping, please explain why in the comments." Good
Java people are cheap and plentiful. Good SQL people are rare, and he
pays them a lot. His set of interview questions includes "What can you
tell me about relational algebra?"

  An exception? Mebbe. But he's really, really successful. And an
aggressive (abrasive) advocate of relational thinking.

On Oct 5, 11:30 am, eric.bouchardlefeb...@gmail.com wrote:

Just to be difficult.....

This is now a long thread, with numerous well-crafted arguments
exhaustively
defending good database design.  But (largely  for the sake of
argument)
there are times when bad design is called for.

For large, well-funded projects,  performance can always
be purchased, usually  for far less than the maintenance costs
associated with
bad design.

But smaller, not-so-well funded systems are part of the bell curve
too.
I wrote a web-based administration system for an "outfitters"
organization once.
There were about 1000 licensed outfitters that needed to be tracked
by a single-person administrator. So there was a private interface
plus a
public one. The public interface was supposed to
include a keyword search that combined outfitter location with
arbitrary
combinations of attribute keywords like "elk hunting, fly fishing,
walleye,  pheasant" etc.

This organization had a limited budget and insisted on
a cheap shared host server.  Mysql queries with mulitiple named
cursors
ran like lightning on my desktop linux box, but they ran like half-
frozen molasses
on the overloaded shared host server.

But application code that did "string-like" over a long string of
comma-separated
keywords worked, and it ran an order of magnitude faster (on the
chintzy shared
host).