Why is database integrity so impopular ?

wrote:
> Hello,
>
> When time comes to build transactional databases (as opposed to data
> wharehouses), I belong to the school that STRONGLY believe in
> normalizing data with high integrity mechanisms. I know all the
> performance cons but IMHO, pros largely overwhelme.
>
> It amazes me, though, how many systems rely on the application to
> manage data integrity. I work as IT director for a large-size
> manufacturer and *none* of our applications use integrity. And I am
> talking here of ERP and other mission-critical systems.
>
> In fact, I had rarely open a database properly normalized and
> inforced ... and I have been working with databases for over 10 years,
> mostly in sectors where lack of integrity can result in dramatic
> consequences.
>
> What is wrong with modern DB design approaches? And what's the point
> of using a big relational DB without the benefits of integrity and
> normalization?
>
> Thank you,
> EBL

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.
 >> Stay informed about: Why is database integrity so impopular ? 

wrote:

>What is wrong with modern DB design approaches? And what's the point
>of using a big relational DB without the benefits of integrity and
>normalization?

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


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 >> Stay informed about: Why is database integrity so impopular ? 

Hello Tony,

On 6 oct, 04:43, "Tony Toews [MVP]" wrote:

> 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.

I know cases like this, and also cases where the systems were
originally developed using DBase or Access and ported to a DBMS using
an automatic database importer without any redesign.


Regards
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 5, 6:56 pm, Alfredo Novoa wrote:
> Hello Eric,
>
> On 5 oct, 20:30, wrote:
>
> > It amazes me, though, how many systems rely on the application to
> > manage data integrity.  I work as IT director for a large-size
> > manufacturer and *none* of our applications use integrity.
>
> I supose you are new in the position

Yes but I have been working as a developper for many years before.

And as a developper, even when doing team OO programming, it was clear
that integrity was part of the database. This did not mean that
applications could not use their own integrity rules, it only meant
that the database was ultimately setting the rules. Additionaly, it
is now comon to see databases shared among many applications ... it's
therefore common sens to concentrate integrity at the data layer.

When I moved from a developper to a manager position, I had to work
with commercial applications I had not developped. Eventually - and I
guess it's everywhere the case - we had to share data from those
databases to new internally built applications. That's when I first
noticed poor data normalizations and complete absence of integrity.
Later on, errors at some ERP applications also put in evidence the
absence of integrity.

The strangest thing about this is the fact programmer use complex
RDBMS as a simple persistent data environemtn. If I had to developp
an application with all the integrity concentrated in the
application, I would use simple text files to store data. The result
would be the same in terms of security but I would obtain more
performance.
 >> Stay informed about: Why is database integrity so impopular ? 

wrote:
> Hello,
>
> When time comes to build transactional databases (as opposed to data
> wharehouses), I belong to the school that STRONGLY believe in
> normalizing data with high integrity mechanisms. I know all the
> performance cons but IMHO, pros largely overwhelme.
>
> It amazes me, though, how many systems rely on the application to
> manage data integrity. I work as IT director for a large-size
> manufacturer and *none* of our applications use integrity. And I am
> talking here of ERP and other mission-critical systems.
>
> In fact, I had rarely open a database properly normalized and
> inforced ... and I have been working with databases for over 10 years,
> mostly in sectors where lack of integrity can result in dramatic
> consequences.
>
> What is wrong with modern DB design approaches? And what's the point
> of using a big relational DB without the benefits of integrity and
> normalization?
>
> Thank you,
> EBL
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.

--
Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
 >> Stay informed about: Why is database integrity so impopular ? 

Alfredo Novoa wrote:

>I know cases like this, and also cases where the systems were
>originally developed using DBase or Access and ported to a DBMS using
>an automatic database importer without any redesign.

Let me add that my databases built in Access are good designs and have relational
integrity. Although I've seen lots that aren't. <sigh>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 7, 1:51 am, Daniel Pitts
wrote:

> 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.

I assume you mean it's tedious when done in application code. It's
easy when the integrity constraints are defined declaratively and
enforced by the DBMS.


> 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.

So is notepad.exe a "system" because it allows for persisting text
files?

The need for a formal data model has to do with the need to encode
complex values, irrespective of whether persistence is needed. One
can have persistent or transient data, just as one can have persistent
or transient finite state machines. Of course mostly we want data to
persist and state machines to be transient, and there are reasons for
that - some quite subtle, such as the difficulty of defining
consistent cuts on distributed state machines, but it is hardly a
definition of the distinction between data and state machine.

A common modern view of a large distributed system, is of interacting
applications that communicate either synchronously or asynchronously
such as by using web services. The various databases in the system
take a back seat to this application-centric view point.

However I suspect most of the data theorists in this ng prefer the
idea that state machines and data are kept apart at ever increasing
scales in the "system" - so even at an "enterprise" level there is a
clean separation between applications and the enterprise wide data.

I like to define "data-centric architecture" according to the 4th item
in the following article:

http://en.wikipedia.org/wiki/Database-centric_architecture

Another crime perpetrated by ignorant application programmers is to
heavily use application defined messaging protocols to build
distributed systems. It is usually much easier for applications to
communicate indirectly via a shared database.
 >> Stay informed about: Why is database integrity so impopular ? 

wrote in message

> Hello,
>
> When time comes to build transactional databases (as opposed to data
> wharehouses), I belong to the school that STRONGLY believe in
> normalizing data with high integrity mechanisms. I know all the
> performance cons but IMHO, pros largely overwhelme.
>
> It amazes me, though, how many systems rely on the application to
> manage data integrity. I work as IT director for a large-size
> manufacturer and *none* of our applications use integrity. And I am
> talking here of ERP and other mission-critical systems.
>
> In fact, I had rarely open a database properly normalized and
> inforced ... and I have been working with databases for over 10 years,
> mostly in sectors where lack of integrity can result in dramatic
> consequences.
>
> What is wrong with modern DB design approaches? And what's the point
> of using a big relational DB without the benefits of integrity and
> normalization?
>
> Thank you,
> EBL

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.
 >> Stay informed about: Why is database integrity so impopular ? 

wrote:

> Hello,
>
> When time comes to build transactional databases (as opposed to data
> wharehouses), I belong to the school that STRONGLY believe in
> normalizing data with high integrity mechanisms. I know all the
> performance cons but IMHO, pros largely overwhelme.
>
> It amazes me, though, how many systems rely on the application to
> manage data integrity. I work as IT director for a large-size
> manufacturer and *none* of our applications use integrity. And I am
> talking here of ERP and other mission-critical systems.
>
> In fact, I had rarely open a database properly normalized and
> inforced ... and I have been working with databases for over 10 years,
> mostly in sectors where lack of integrity can result in dramatic
> consequences.
>
> What is wrong with modern DB design approaches? And what's the point
> of using a big relational DB without the benefits of integrity and
> normalization?

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.)

--
Roy
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 9, 8:14 am, "Walter Mitty" wrote:
> "Roy Hann" wrote in message
>
>
>
> > 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.
>
> I can echo that.  Right down to the IT director who later said that my
> approach was the right one, much later.
>
> That suggests a new discussion about the "sadder but wiser client".  There
> must be a lot of stories out there about that.

Well, that point bring the question of IT Directors competences.

I start from the assertion that when a company is ready to buy a big
software solution (like an ERP system), IT Managers are part of the
decision. It is then their reponsability to make database integrity a
requirement, to explain to the financial managers the purpose of such
a requirement and to be able to argue with vendors or integrators that
will try to minimize the importance of integrity.

The case here is classic : 5 years ago (I was not working here) the
company decided to buy an ERP solution. At that time, the IT director
had a financial profile with non-technical computers knowledge. IT
team was basically made of clerks playing with Excel pivot reports.

The decision to choose the ERP was therefore exclusively based on the
trust they had with some outside consultants. Consultants, most of
the time, specialize in one system and stick to it even if it has
major problems, like lack or absence of database integrity.

Conclusion : it's always a question of competence at the IT
departement level. If IT managers were all the time chose among
technical personel, systems would improve because IT managers would
force vendors to have a technological thinking.

But most of the time, IT managers are former business managers (or
people holing "information systems management" diploma) thinking their
working knowledge of computers gives them aptitudes to take IT mission-
critical decisions.
 >> Stay informed about: Why is database integrity so impopular ? 

"Roy Hann" wrote in message

> 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.

I can echo that. Right down to the IT director who later said that my
approach was the right one, much later.

That suggests a new discussion about the "sadder but wiser client". There
must be a lot of stories out there about that.
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 5, 11:30 am, wrote:

> What is wrong with modern DB design approaches?  And what's the point
> of using a big relational DB without the benefits of integrity and
> normalization?

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.
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 13, 5:24 am, Jon Heggland wrote:
> DBMS_Plumber wrote:
> >    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.
>
> It is considered a deviation not to have a default? Why is that? I would
> have thought that having defaults for every column would encourage
> sloppiness.
> --
> Jon

If you mandate that a column cannot contain a NULL, setting a DEFAULT
means that when a programmer legitimately doesn't have a value for the
column they aren't obliged to put in there the first thing that
springs to mind.

I'm sure there would be cases where you could say NOT NULL but not
provide a DEFAULT. The point to me was that this shop is serious about
their integrity and this seriousness is manifested in their
engineering policies and procedures.
 >> Stay informed about: Why is database integrity so impopular ?