[DUG] Using Boolean (Char(1)) in Firebird

[russell]

Hi Steve

 

Firebird Fb does not have a native boolean type. 

I use a smallint for my Boolean types. 1=True, 0=F. 

There are many ways to define and use Booleans in Fb.

Eg. CREATE TABLE BCD_CONNECTIONS (

       CONNECTION_ID ID /*Integer NOT NULL*/ NOT NULL,

       FIRST_TS TIMESTAMP NOT NULL,

       CURRENT_TS TIMESTAMP NOT NULL,

       MACHINE TEXT24 /*Varchar(24) */ DEFAULT 'Unknown' NOT NULL,

       SECURITY_CODE TEXT48 /*Varchar(48) */ DEFAULT 'illegal' NOT NULL,

       MACHINE_ID Integer,

       USER_ID Integer,

       USER_NAME TEXT64 /*Varchar(64) */,

       B_IN_WINSLEEP Smallint DEFAULT 0,

       CONSTRAINT BCD_CONNECTIONS_PK PRIMARY KEY (CONNECTION_ID)

);

 

So these Booleans can be indexed. Tables can have many Booleans.

ID, TEXTNN are my defined Domains in Fb.

 

Russell

 

From: delphi-bounces at listserver.123.net.nz
[mailto:delphi-bounces at listserver.123.net.nz] On Behalf Of Steve Peacocke
Sent: Sunday, 30 March 2014 3:19 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: [DUG] Using Boolean (Char(1)) in Firebird

 

Hi all,

 

I'm playing around with a Firebird database and wanted to know from you DB
experts out there how you handle booleans in a table.

 

These could be as simple as

  ActiveRecord (Y/N)

  AccountTransactionType (I/E) - (Income or Expense)

 

That last I would normally think would be "Income (Y/N)" so that would be a
boolean too.

 

My understanding is that this will never be indexed, even if you
specifically add an index to it. So how do you handle it. There may be
several boolean fields in a table definition.

 

As these tables c an contain several hundred thousand records, this could
potentially slow down any query to say total all records last 3 years where
Active and Income - as the only index would then be on the date field, there
is a possibility that this could potentially be a very slow query.

 

I've heard of others creating another table to create, say, non-Avtive
record ID's, but this one table could have several booleans, therefore
creating several new tables (combining then into a single table with the
field name would cause the same problem).

 

Any thoughts?



Steve Peacocke
Mobile: +64 220 612-611

Linkedin Professional Profile
<http://nz.linkedin.com/pub/steve-peacocke/1/a06/489> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20140330/1ce6f8cc/attachment-0001.html