[thelist] database structure for speedier queries

Joshua Olson joshua at waetech.com
Sat Dec 1 14:29:17 CST 2007


> -----Original Message-----
> From: Joel D Canfield
> Sent: Saturday, December 01, 2007 2:12 PM
> 
> > sounds like adding the archived field and setting it to null 
> > or not null is the best solution. 
> 
> question to the other folks more db-savvy than I - what's the 
> difference
> in current use or future-proofing between using NULL or not, versus
> using 0 or 1?
> 
> eddicate me

Joel,

I typically use zero vs non-zero for boolean flags for three reasons:

1.  Some databases don't query as quickly for NULL vs non-NULL values
because of their indexing mechanisms. [0]

2.  The format for comparing against the NULL value is different than for
other types of fields (IS vs. =, IS NOT vs <>)  I prefer a bit of
consistency when at all possible.

3.  In my mind, 0/1 is synonymous with false/true, whereas NULL/non-NULL
isn't really flase/true, but rather false/some-sort-of-true.  I think this
is just how my brain works.

And, when choosing bit vs integers for storing these flags, I prefer integer
types over bits because of the ability to use integer types with certain
aggregation functions that may be impossible with bit types.  I can't recall
a specific example off the top of my head, but I occassionally encounter a
"whew, glad I chose a tinyint" moment when trying to build a query.

(FWIW, I use a "date_deleted" field in many of my tables.  Non-NULL is
equivalent to deleted, NULL means non-Deleted.  Sooooo, while I understand
the problems associated with this, I'm not dogmatic about it)

[0]
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/th
read/78e125af767e3b98/3f70a4ece9061814: (towards the bottom) "Oracle b*tree
indexes do not index null values. "

Joshua

<><><><><><><><><><>
Joshua L. Olson
WAE Technologies, Inc.
http://www.waetech.com/
Phone: 706.210.0168
Fax: 413.812.4864
Private Enterprise Number: 28752

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/ 



More information about the thelist mailing list