Bit Value

[Jack]

Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.

Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.

Question: Is this a good idea? Or could I somehow tally up all these
bit values and store it in one field in the customer table?

The application is a website and it is built using ASP.NET (VB.NET)

Any opinions would be great.

Cheers,
Jack

[Hugo Kornelis]

Re: Bit Value

On 18 Jan 2005 17:06:28 -0800, Jack wrote:
[color=blue]
>Hi there, I'm not sure if this the appropriate group so apologies if
>it lies outside the boundary.
>
>Senario: I have a customer table with contains a bunch of different
>bit values that represent true/false values pertaining to the
>customer. I decided for the purpose of clarity I would move these
>values into another table. This way I would keep the customer details
>(name, age, etc) separate from these values.
>
>Question: Is this a good idea? Or could I somehow tally up all these
>bit values and store it in one field in the customer table?
>
>The application is a website and it is built using ASP.NET (VB.NET)
>
>Any opinions would be great.[/color]

Hi Jack,

I'd say: replace the bit columns with somewhat more descriptive columns,
like
ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))

I don't see any advantage in moving these columns to a seperate table,
unless you are approaching the max number of columns per table (quite
unlikely) or unless you need to store these yes/no values only for a
limited subset of your customers.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Rawheiser]

Re: Bit Value


"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:r85su0peh2 op7lhf1i7eocfdm 2shms59vb@4ax.c om...[color=blue]
> On 18 Jan 2005 17:06:28 -0800, Jack wrote:
>[color=green]
>>Hi there, I'm not sure if this the appropriate group so apologies if
>>it lies outside the boundary.
>>
>>Senario: I have a customer table with contains a bunch of different
>>bit values that represent true/false values pertaining to the
>>customer. I decided for the purpose of clarity I would move these
>>values into another table. This way I would keep the customer details
>>(name, age, etc) separate from these values.
>>
>>Question: Is this a good idea? Or could I somehow tally up all these
>>bit values and store it in one field in the customer table?
>>
>>The application is a website and it is built using ASP.NET (VB.NET)
>>
>>Any opinions would be great.[/color]
>
> Hi Jack,
>
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
>
> I don't see any advantage in moving these columns to a seperate table,
> unless you are approaching the max number of columns per table (quite
> unlikely) or unless you need to store these yes/no values only for a
> limited subset of your customers.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)[/color]

Another possible beneficial thing you can do with a 'side-car' table like
this.
Save only one row per distinct combonation of flags and store the integer
key to that bitmap pattern in the main table.
With an indexed column you would be able to more quickly select on those
values.

FamilyType Table
---------------
FamilyType Married BothWork HaveKids Descripiotn
----------------------------------------------------------------
1 Y Y N DINK
2 Y N Y That Type
3 N Y N Whatever
etc...

customer Table
----------

CustomerKey Name FamilyType
-------------------------------
1 joe and linda 1
2 bob and rob 3
3 karl and cindy 2

You can use a view, a proc, or an instead of trigger to make sure that
instead of updating the flags, you simply select the correct family type
code.

[Erland Sommarskog]

Re: Bit Value

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))[/color]

What can be more descriptive that 0 and 1?

Using character values when there are perfectly usable numeric values
as there are for binary value is asking for trouble. Let's see was
Y/N, T/F, J/N or something else?

We once had a lot of such columns in our database, but almost all are
bit columns these days. And, no, while the name of the type is ab_yesno,
the values are not Y/N, but J/N.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Bit Value

Jack (jack-b@humlog.com) writes:[color=blue]
> Hi there, I'm not sure if this the appropriate group so apologies if
> it lies outside the boundary.
>
> Senario: I have a customer table with contains a bunch of different
> bit values that represent true/false values pertaining to the
> customer. I decided for the purpose of clarity I would move these
> values into another table. This way I would keep the customer details
> (name, age, etc) separate from these values.
>
> Question: Is this a good idea? Or could I somehow tally up all these
> bit values and store it in one field in the customer table?[/color]

The last thing would be a bad idea. That's more cumbersome to use, and
more prone to errors.

As for shuffling the bit columns to another table, well, it depends. We
actually did this with our accounts table, since we found that we were
costantly adding bit columns to it, of which some were for tiny marginal
features that were only referred to in one or two places.

Therefore we move these less used bit columns to this table:

CREATE TABLE accountflags (accountno int NOT NULL,
flag varchar(15) NOT NULL,
CONSTRAINT pk_flags PRIMARY KEY (accountno, flag))

That is, the bit column became rows. And a flag is set for an account
if there is a row in the table, else not. The flag themselves are
defined in another table, one that is loaded with data from scripts
when the database is created.

But if all you want to do is move bit columns to a side table, but
keep them as columns, I'm not sure that it's worth the effort.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Bit Value

machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.

SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.

Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)

What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!

There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.

In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?

Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:

1) Ignore the problem. This is actually what <i>most<i> newbies do.

2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.

Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?

You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!

[Erland Sommarskog]

Re: Bit Value

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue]
> machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.[/color]

Completely wrong. In some language you can say "String" and the language
will allocate for you. In SQL Server you have to specify the max length
of any character column you want to use, and you cannot have more than
8000 bytes on a page. (There is varchar(MAX) in SQL 2005, but practice
will remain to specify an upper bound.)

As a further example, consider the whole range of tinyint, smallint,
int and bigint.

Since you store data on disk, the actual storage format matters a whole
lot to database programmers. Too much storage has bad effect on performance.

And of course, as datatype, BIT has nothing machine-level at all. It is
just a name for something that can have two values. While it could have
been called Boolean, Celko, Pamela or Urban, the name bit is quite good,
because most people have an understanding of how much information you
can cram into a bit.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Bit Value

> Surely, no good programmer would ever write non-portable[color=blue]
> code by getting to such a low level as bit fiddling!![/color]
There's nothing wrong with fiddling with your bits is there ? :-)