Re: nulls in databasae design

Before addressing whether you should or should not...let's digress to the concepts involved.

I see 2 main considerations for nulls allowed.

1. Logical/Conceptual Argument: If something doesn't exist, then why would you store a value to indicate nothing? If you don't allow nulls, then you must store indicators representing nulls
2. Coding Implications: You MUST code differently if you allow nulls vs. store a value to represent a null - mainly, get familiar with outer-join if you allow nulls. Of course then there's the aspect coding for the difference of nulls versus 0 or blank.

Discussion:

Let's face it, hackers love this sort of thing - storing something to mean nothing - keeps consultants employed handling complexities in database design. I side with keeping things simple from a data standpoint.

Really comes down to the age-old argument: Is simplicity in processing or the data structures more important?

It's debatable in lesser mentally capable circles (like with consultants) but if you have any common sense (am I biased? comes from 10 years of database designing), you'll side with data since data definitions and relationships (if correctly analyzed) very very rarely change but processes constantly change. Course if the data analyst is a consultant who doesn't know squat about your business then changing data rules become a way of life...

As for saving space, you're not in the land of mainframe anymore. Who cares about a couple bytes when 8 gig scsi disks cost $1,000.

As for assuming that Oracle in any way shape or form works like Cobol, do yourself a favor and don't.

Horror story (ongoing)...consultants trying to convince me that outer joins are more costly. They are - guess why - because if you benchmark against non-outer joins for null foreign keys, then you get more data - that's why they take longer. Duh. They weren't dumb, just trying to fool me so they could justify putting values in null columns because it seemed the most expedient for coding so they don't have to worry about coding outer joins and knowing exactly what they're supposed to be getting.

• My opinions are not necessarily ...

Neil Sauerwein wrote:
>
> I am researching the question of whether to use nullable columns in an
> Oracle database, and would be grateful if anyone would have the time or
> inclination to read and respond to this.
> As a mainframe/COBOL/DB2 shop, we have traditionally made database
> columns NOT NULL with the exception of optional dates, mainly because
> COBOL makes dealing with nulls cumbersome (defining and manipulating
> null indicators). Now in the process of developing a client/server app
> with an Oracle database, this philosophy is being revisited. Nulls seem
> to make more sense from a logical point of view, although whether they
> save a great deal of disk space is questionable. As for programming,
> nulls are much easier to handle in the VB and Java that will be used,
> but some back-end processes may still be written in MF COBOL, which I
> assume uses null indicators in the same way as mainfram COBOL. Should
> this drive the decision to use nulls or not ? Does anyone have any
> opinions/experience/horror stories?
> Thanks in advance,
> Neil
 

-- 
Gary Melhaff
Senior Database Analyst
Weyerhauser Corporation