Re: NULL or 0 for a "FLAG_COLUMN"

Martin T. wrote:
> Brian Peasland wrote:
> > Martin T. wrote:
> > > Hey all.
> > >
> > >>From a general ORACLE point of view (lets say 9i2 upwards), what would
> > > be the preferred way of storing a true/false flag column.
> > >
> > > a) col MY_FLAG: NOT NULL, DEFAULT 0, Possible values: 1, 0
> > > or
> > > b) col MY_FLAG: Possible values 1, NULL
> > >
> > > There is some side effect of having NULL vs. 0 when using indexes,
> > > right?
> > > Any other ideas why the one or other might be better (Storage /
> > > Performance / Indexing)?
> > >
> > > thanks!
> > >
> > > best,
> > > Martin
> > >
> >
> > On option allows NULL values the other does not. Instead of focusing on
> > performance/storage/indexing, how about focusing on the business rules?
> > Defining a column as NOT NULL is placing a constraint on the data in
> > that column. Does that constraint enforce your company's rules or not?
> > The business rules drive constraints, not the other things you are
> > looking at.
> >
>
> Brian - are you stating that a) and b) implement different business
> rules??
>
> The business rule is: "This column represents true or false, it must
> not be undefined".
>
> Both a) and b) allow the column to have exactly two distinct values,
> just the representation of the two distinct values is different. (NULL
> and 1 vs. 0 and 1)
>
> best,
> Martin

What value would the user see on a screen? If it is a text display, how do you display the false (NULL) value? If this column represents the response to a question on screen, then How does the user distinquish between TRUE, FALSE, and not yet answered?

We might come up with better responses fi we know what the col column represents in the business system.

 Note that three-valued logic may also affect the choice. --- checking for true versus False
IF ( col = 1 ) THEN --- looking for TRUE condition IF ( col !=1 ) THEN --- looking for FALSE condition (fails for the T/F 1/NULL case!!)

Ed Received on Wed Sep 06 2006 - 10:46:04 CDT