Re: NULL or 0 for a "FLAG_COLUMN"

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 Received on Wed Sep 06 2006 - 10:28:13 CDT