Re: Why is database integrity so impopular ?

<eric.bouchardlefebvre_at_gmail.com> wrote in message news:2ecca0cf-6917-47a3-b95c-8e59e122bbd8_at_i76g2000hsf.googlegroups.com...
> 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. Received on Tue Oct 07 2008 - 16:27:16 CEST