Re: NULL or 0 for a "FLAG_COLUMN"

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 6 Sep 2006 15:09:02 GMT
Message-ID: <J56FF5.5Iv@igsrsparc2.er.usgs.gov>

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.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Sep 06 2006 - 10:09:02 CDT