best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.