“>> btw - Billable is a bit field [sic] and just hold true/false. Would it be better practice to use a char(1) Y/N field [sic]. <<”

— Recent question, posted on a listserve

My Response

Foundations: Fields and columns are totally different concepts. One reason you came up with a magnetic tape file solution is that you think of SQL as a file system and are mixing physical and logical levels. I keep pounding people on this point, but it really makes a difference.

Bits (in lowercase with the usual meaning, as opposed to some proprietary BITS datatype, in uppercase) are too low level and physical for a data model. They are not handled the same way in different host languages and hardware, so they do not port. And nobody agrees how {+0, -0, +1, -1} to map into TRUE and FALSE in languages that have a BOOLEAN datatype.

The use of flags like the one you cited in the post is actually more of a punch card processing trick. Having one column that could be used by a card sorter to pull out a subset was useful.

Instead of a flag, sit down and develop an encoding for all the types of charges, and make it extensible. So far, you have “billable” and “non-billable,” and you cannot extend these. Are there “billable, taxable,” “non-billable, taxable,” “non-billable, non-taxable,” and “billable, taxable” charges?

One of my favorites, because it gets posted here a few times a year, is a survey with “yes/no” questions. You also need “Not applicable” and “not answered” codes. The ISO sex code has four values, so something like “IsMale” does not work!

Failure to have the extensible code typically leads to ALTER TABLE statements to add each new flag — “IsTaxable.” This creates problems for the front-end boys: They have an extra column to read into the host program and display properly; their queries have to be changed to add more predicates. With an extensible encoding scheme, the changes are easier to do and to understand.

Compare reading and maintaining a predicate like this:

((IsBillable = 1 AND IsTaxable = 0) OR (IsBillable = 1 AND IsTaxable = 1)) 
OR (IsBillable = 0 AND IsTaxable = 1)

for which you have to add more flags, against (charge_status = 12) being modified to something like (charge_status IN (10, 11, 12)) instead.

In the meantime, the DB programmer needs to add CHECK() constraints to avoid illegal combinations; imagine that (“non-billable, taxable”) is not possible. Let’s be honest, most database programmers forget to create a decision table for all the flags in the schema and assume the front end will weed out bad data. Sure, that works :)

Let me repeat: “all the flags in the SCHEMA,” not just this table. Tables are not files; they are interrelated. If one table has “IsMale” and another table has “IsFemale” for flags, you can get into trouble fast. Did every table that had a “IsBillable” get the “IsTaxable” flag, too?

Standard codes are much easier to propagate over the schema since they usually have a lookup table of their own. I simply add the new code to the lookup table. That is also how I guarantee that I do not have illegal situations.

--

Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 750 magazine columns and seven books, the best known of which is SQL for Smarties, now in its third edition. For more articles, puzzles, resources, and more, go to www.celko.com.