[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