Re: Boolean datatype in Oracle?

Alan,

you're quite right, howver I meant it in the light of the boolean question of the original poster. As a boolean has two values (TRUE/FALSE), and Oracle has three, as you illustrated, a technique I use quite often in Designer is to have Yes/null, Yes/No and No/Null domains - depending on defaults and mandatoriness

Alan wrote:

> Three cases are preferable, IMO:
>
> NULL
> YES
> NO
>
> They each have a different meaning. Null means that a choice has not yet
> been made, which is different than YES or NO, and is also meaningful data.
> Note that a CHECK constraint will ignore Nulls, which is desireable in this
> case.
>
> Note also, that in user interface design:
>
> an unchecked check box is not necessarily the same as a check box that has
> been checked and then unchecked. Usually
>
> 1. Never been checked = Null
> 2. Checked = Yes
> 3. Checked then Unchecked = No
>
> Unfortunately, 1 and 3 appear the same to the user, which is why radio
> buttons (mutually exclusive, mandatory once clicked controls) are preferable
> for Boolean fields:
>
> neither button clicked = Null
> Yes button = Yes
> No button = No
>
> This eliminates any ambiguity as well as uncerainty on the users' part.
>
> "Frank van Bortel" <fbortel_at_home.nl> wrote in message
> news:3A240618.2D21C13C_at_home.nl...
> > Or, if you happen to have a non-mandatory column:
> > Y and NULL (or N and NULL - whichever your default
> > value)
> >
> > Alan wrote:
> >
> > > Just use a CHAR(1) and use a CHECK constraint to limit the entries to Y
 and
> > > N or 1 and 2, or whatever.
> > >
> > > "Robert Martin" <rwmartin_at_sisconet.com> wrote in message
> > > news:8vc26606o7_at_enews3.newsguy.com...
> > > > Well, I am converting an SQL Server 7.0 database to an Oracle 8i
 database
> > > > and just want to convert things as best as I can.
> > > >
> > > > -------------------------------------------
> > > > Robert Martin (rwmartin_at_sisconet.com)
> > > > Design Engineer
> > > > SISCO Inc,
> > > > 6605 19 1/2 Mile Road
> > > > Sterling Heights, MI 48314-1408
> > > > Phone : 810-254-0020 (ext 125)
> > > > Fax : 810-254-0053
> > > > "Frank van Bortel" <fbortel_at_home.nl> wrote in message
> > > > news:3A22C203.91062EB1_at_home.nl...
> > > > > IMHO you don't need one - just compare a column value with a
 literal;
> > > > > the outcome is boolean:
> > > > > WHERE <column_name> = <literal>
> > > > >
> > > > > Robert Martin wrote:
> > > > >
> > > > > > What is the best way in an Oracle database to represent a Boolean
 value
 for
> > > > > > a column in a table? I see there is no simple Boolean datatype
 (which
 I
 find
> > > > > > to be pretty lame IMO). Thanks in advance.
> > > > > >
> > > > > > -------------------------------------------
> > > > > > Robert Martin (rwmartin_at_sisconet.com)
> > > > > > Design Engineer
> > > > > > SISCO Inc,
> > > > > > 6605 19 1/2 Mile Road
> > > > > > Sterling Heights, MI 48314-1408
> > > > > > Phone : 810-254-0020 (ext 125)
> > > > > > Fax : 810-254-0053
> > > > >
> > > > > --
> > > > > Gtrz,
> > > > >
> > > > > Frank van Bortel
> > > > >
> > > > >
> > > >
> > > >
> >
> > --
> > Gtrz,
> >
> > Frank van Bortel
> >
> >

--
Gtrz,

Frank van Bortel