Design: bit on or off?

2 Dec 2005 1:17 PM

Lisa Pearlson

I'm asking lots of design questions here.. but they are little ones,
sometimes matter of taste, sometimes more than that.

Imagine I have a table of "Keys". Some of these keys will be "active" and
some will be "blocked".

I'm trying to decide whether I should use a BIT column and call this
"active" or "blocked".

So should bit 1 mean "active" or mean "blocked" ? There will almost
certainly be more active than blocked keys. Which one is more intuitive or
likely convenient in practice?

Of course I could use a set "yes/no" or "active/disabled" but for only 2
possibilities, a bit seems more efficient and convenient in front en back
end. What are your recommendations/tastes?

Lisa

2 Dec 2005 1:25 PM

Razvan Socol

Hi, Lisa

If you use a bit column, 1 should represent true and 0 should represent
false. So, if the column name is "active", 1 means that the key is
active, 0 means that it's blocked.

However, you should consider using a char(1) column with a constraint
like "Status IN ('A','B')", because it is possible that sometime in the
future you may want another status value, for example "pending". If you
use a codification on a char(1), make sure that it's meaning is well
documented (for example in the Description of the column, if you use
Enterprise Manager).

Razvan

2 Dec 2005 1:29 PM

JT

If "active" basically means "enabled" or "on" or "true", then use 1 and 0
for "blocked".

Show quote

"Lisa Pearlson" <no@spam.plz> wrote in message
news:u5kVzK09FHA.4004@TK2MSFTNGP14.phx.gbl...
> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
>
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
>
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
>
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
>
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
>
> Lisa
>

2 Dec 2005 1:35 PM

David Portas

Lisa Pearlson wrote:
Show quote

> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
>
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
>
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
>
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
>
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
>
> Lisa

I'd prefer to use a CHAR or maybe an INT status code. That way, you can
add more statuses if you need to, you can use a meaningful readable
code that everyone can understand and you avoid some of the peculiar
quirks of the BIT type (for example some numeric operators are valid
for BIT and others aren't).

--
David Portas
SQL Server MVP
--