Boolean Values

[TC]

OK, I've got what may be a completely trivial problem. I'm
going to be working with a moderate number of records -
perhaps 3,000 max. I have a datum that could be described
as boolean - it's either yes or no.

The nature of the data dictates a vast majority of the
values for this field are going to be either one or the
other, depending on how I define the table. For instance,
if most of the glasses were full, then I could call the
field IsFull and most of the values would be True.
Conversely, I could call the field IsEmpty, in which case
most of the fields would be False.

My question is this - from a design standpoint, would
there be any benefit going one way or another? Would
there be any sort of notable query efficiency, storage
benefit from having these data False vs. True?

No. In the jet file structure, boolean columns are truly stored as single
bits. From a storage viewpoint, it makes no difference whether they are
true, false, or any mixture of the two.

HTH,
TC

 

[MDW]

OK, I've got what may be a completely trivial problem. I'm
going to be working with a moderate number of records -
perhaps 3,000 max. I have a datum that could be described
as boolean - it's either yes or no.

The nature of the data dictates a vast majority of the
values for this field are going to be either one or the
other, depending on how I define the table. For instance,
if most of the glasses were full, then I could call the
field IsFull and most of the values would be True.
Conversely, I could call the field IsEmpty, in which case
most of the fields would be False.

My question is this - from a design standpoint, would
there be any benefit going one way or another? Would
there be any sort of notable query efficiency, storage
benefit from having these data False vs. True?

 

[John Vinson]

My question is this - from a design standpoint, would
there be any benefit going one way or another? Would
there be any sort of notable query efficiency, storage
benefit from having these data False vs. True?

The only (subtle) benefit to one way or the other is that *if* you
will usually be searching the table for the rare value, an Index on
the field will be helpful. If you'll just be reporting the value, or
searching for either one or the other indifferently, then I don't
think it matters for efficiency; you can just define it whatever makes
the most logical sense.

 

[MDW]

In 99% of the cases, any queries I perform are going to
exclude records with the rare value, so I will certainly
index that field. I wasn't sure if there was any big deal
either way, beyond that. Thanks.

 

[Tim Ferguson]

My question is this - from a design standpoint, would
there be any benefit going one way or another? Would
there be any sort of notable query efficiency, storage
benefit from having these data False vs. True?

Just to put a small spanner in the works, what about a completely different
solution? If there really is a small number of records that are different
from the majority, then you could remove the field altogether and use a
second table with one column linked to the original one:

IsFull (GlassID Primary Key, Foreign Key references Glasses)

This would then contain just the GlassID values for glasses that were full.
When you need to get at the empty glasses, you just join the tables and add
the filter

WHERE IsFull.GlassID IS NULL

and so on. I'm not sure that there is a real advantage to this, unless
there are dependent fields like FilledByWhom, DateLastFilled and so on: but
it is an alternative approach.

Best wishes


Tim F