Re: On specialization constraints time of application

From: Cimode <>
Date: Sun, 14 Jun 2009 08:45:41 -0700 (PDT)
Message-ID: <>

On 14 juin, 17:04, "Brian Selzer" <> wrote:
> "Cimode" <> wrote in message
> > Snipped
> > > The world of practice is full of people whose professional development
> > > consists almost entirely of OOP, and who are suddenly coming up to speed
> > > on
> > > both SQL and database design. Not only that, but they have a very short
> > > deadline to produce deliverables, and an intentionally narrow view of
> > > the
> > > value of stored data. These people often unconsciously associate
> > > meanings
> > > with nulls that go way beyond anything that you or the experts you cite
> > > would accept as a useful meaning for that symbol. If you ask them, they
> > > will revert to the meaning that they attach to a pointer that doesn't
> > > point
> > > anywhere. In that world, promoting some sort of "best practice" in
> > > restricting the use of null tends to fall on deaf ears.
> > Time constraints are not a good excuse for doing poor design since
> > using NULLS will crunch time over the entire system lifecycle.  For
> > instance the time of maintainance of a system using NULLS is far more
> > important than a system that does not use them, increasing TCO by
> > orders of magnitude.
> > Because they ignore RM definitions about, SQL developpers also ignore
> > that drawbacks of using nulls far outweights any advantages.
> I would have used 'misusing nulls' instead of 'using nulls' because
> judicious use of nulls can actually reduce both development and execution
> time, as well as maintenance, especially if decomposition would introduce
> complicated interrelational constraints.  For example, a schema for married
> employees,
> MarriedEmployees {EMP#, NameOfSpouse}
> where NameOfSpouse can be null is a lot easier to maintian than a pair of
> schemata,
I do not want ot make this an offtopic debate about whether NULLS are a good thing or not. I can only say that it is not my experience. As DBA fixing lots of performance issues on mission critical systems, I observed that designs using NULLS produces the following effects (to quote a few from memory):

1> To maintain data correctness, an additional layer of ISNULL, IS NOT NULL, IS NULL SQL predicates become almost mandatory. You just need to take a look at how business intelligence SQL lookalikes (MDX) languages are designed.
2> Since 1 happens, perfomance is strongly degraded since additional scans that would not happen if they were no NULLS. As a consequence the lifespan of applications scanning a specific table using NULLS is shortenned due to overhead due to NULLS. 3> In general, since the use of NULLS force the use of IS NULL/IS NOT NULL clauses on *each* procedure accessing the table, there is a multiplying factor over the overhead described in 2> which is a performance killer.

As a consequence of 1, 2 and 3 I can not say that I can only deny what you claim.

> MarriedEmployeesWithSpouseName {EMP#, NameOfSpouse},
> MarriedEmployeesWithoutSpouseName {EMP#}
> and the interrelational constraint that prevents an instance of EMP# from
> appearing in both  relations.
> > Promoting best practices can not replace a formal education about RM.
> But what counts as 'formal education' these days is in many cases worse than
> being worthless.
As far as I am concerned as a daily database practitionner, a basic formal education in sound database theory has helped me a lot simplify my job since I understand what to expect as a general trend. Formal education has a power of making things more predictable while learning tricks and hacks has only a single mission lifespan. The fact that formal education would or not count in eyes of uniformed audiences has a limited importance. the same audiences in fact pay me big box to fix errors their incompetent ignorant designers could have easily avoided if they bothered doing some reading.

That is my experience. Received on Sun Jun 14 2009 - 17:45:41 CEST

Original text of this message