On Aug 4, 2009, at 11:58 PM, James K. Lowden wrote:

I'm inclined to agree with this view in general, and so is the
DBMS server -- which is why it tries to transform the NOT IN
or NOT EXISTS into the more efficient OJ plan for you.

The real problem here is that NOT IN is semantically different
from the others in the presence of NULLs. Nullable columns
are in general evil, broken, and wrong.

Karl

On Aug 5, 7:30*am, Karl & Betty Schendel <schen... (AT) kbcomputer (DOT) com>
wrote:

Nullable columns are in general evil, broken, and wrong.
I have a few "database coders" (I certainly would call them
programmers -or- analysts) who don't grasp that concept. one, btw, I
heartliy support.

If I hear one of them whine one more time about "needing it as a place-
holder because I don't *have* a value (yet)", I may have to employ
larger caliber educational tools....

On Aug 6, 4:37*pm, "J. F. Cornwall" <JCornw... (AT) cox (DOT) net> wrote:
That's a reason I understand.... In the case of the individuals I was
alluding to, they also wanted two (or was it 3) tables so they could
store general information about an order. Seems they didn't think
they could fit all the <ahem> "required information, you know, like
the individual address lines. We can join the tables together on the
order number an have enough room, tho..."

Some days I cry, other's I used to drink, and occassionally I'd win
one..... I happened to win that one, at least until we got a new
director. Ah well....

OldSchool wrote:
well, they may be evil (not that I see that as a bad thing,
bwahahahaha), but we certainly need them in *our* database. Not as a
placeholder for future things, but because we have a century or more of
data readings where not every record *had* all the possible bits of
data. And, as the instruments became more capable over the decades,
more and more fields were added and recorded. We do, however, still
have measurements taken in the 1880s with a tape measure and a stick.
Mixed in there with readings made on the most modern automated sensors
and fed directly into the system.

And no, we don't have hte resources to completely redesign everything
from the database to the applications to the workflow processes of
11,000 technicians and hydrographers. ;-)

Jim

James K. Lowden wrote:

Totally agree.

(Yes, I'm back.)

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

J. F. Cornwall wrote:

I wouldn't ordinarily presume to comment on how someone else's business
processes work, but in this case you are offering an example of where it
is desirable to use nullable columns. Without raking over the
whys-and-wherefores yet again, nullable columns are deplorable and to
be avoided. Usually it is easy, sometimes it isn't, but they are never
good. One would have to offer an *extraordinarily* good
counter-example to move me one inch on this.

I don't know if your database is used for research purposes, but if
it is, your example is not a good one. Using nullable columns to permit
different instrument records to be spliced is scientifically very poor
practice. Many peer reviewers would express their disapproval more
forcefully than I have if they were shown a paper that did it without
extremely elaborate accounting that is made harder, not easier, by
consolidating the data in one table.

OK, I take your point; money is tight. But using nulls is not in any
way desirable. They are like a life-jacket; better than nothing but
really unpleasant to have to use for real.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

Roy Hann wrote:
The data are definitely used, not so much the database itself. We
generally provide data dumps of the bits people need when asked.
Historically, the most valuable data has been a daily mean discharge,
but the raw data measurements (hourly stage measurements usually) for
old records were never kept. Nowadays, we have the disk space to keep
most of the raw data as well, so that's becoming more of a desired
product too. It may not be the best practice scientifically, but it's
what we have. We cannot go back and recreate good data from a century
ago, and we certainly can't get away with restricting current data to
what was available back then for techniques, and we can't create new
replica tables every time a new instrument comes along. We do try to
quantify the overall quality of the data as well, whenever it's dumped
out or displayed online. Yes, nulls do make our life harder in many
ways, no argument there, but in our system they work.

In addition to the lack of resources, we also suffer from the fact that
our teams are primarily composed of folks who learned programming and
databases on the job as they progressed from hydrologic technicians into
computer jockeys and joined in the maintenance of our constantly
evolving system. We have one actual database analyst with academic
training in DB design and the theories behind all the designs. The rest
of us do the best we can. We have managed, though we do have issues on
occasion... I understand your points, but sometimes we need that life
jacket.


Jim