Re: Using bits to store information in the database.

>From a database maintenance and code maintenance perspective,
this is a bad idea.

If comes under the heading of 'clever code' that a developer can look at and say "Ain't I clever". I've done it, we've probably all done it at some time.

It was a bad idea when I did it, and it remains a bad idea. Do it and you will regret it as long as you have to work with the system.

Do yourself a favor and properly model this, then build a database that gives you flexibility and no limitations.

Jared

On Wednesday 07 May 2003 09:32, laura pena wrote:
> The reason to use bit flags suggested by developers ... are that we can
> represent multiple check options.In my example verified and confirmed
> could be check.So if verified was 1 and confirmed was 01 then the following
> bits would be set 11. I see both advantages and disadvantages to this. The
> alternative approach would be to use varchar2(1) and have columns represent
> each.I heard 32 entries would be max with this approach. If we used a long
> 64 bits but that seems like a lot of wasted space. Fom the developers view
> point they can reference a database column once and perfom the bit
> translation on the client side. I am wondering if alternatively I could
> create a stored procedure to do the samething. Seems like an interesting
> design decision. -Lizz
> Jonathan Gennick <jonathan_at_gennick.com> wrote:Wednesday, May 7, 2003,
> 10:22:06 AM, you wrote: lp> Looking for advise on storing bits in the
> Oracle
> lp> database column.The scenario goes like this: define
> lp> column statusFlag NUMBER(x)statusFlag can represent the
> lp> following: verified set to 1nonverified set to
> lp> 10confirmed set to 100non confirmed set to 1000audited
> lp> set to 10000 I believe this gets my point accross.... So
> lp> here are my questions: What is the largest amount of bit
> lp> I can set for a NUMBER(x)?
>
> When you go from 1 to 10 to 100 to 1000, you aren't working
> your way up in terms of bits, but rather in terms of decimal
> digits. NUMBER columns are stored in some sort of decimal
> format. I believe the largest possible NUMBER is NUMBER(31),
> but check the manuals to be sure. That would give you 31
> positions: 1, 10, 100, 1000, etc.
>
> What is the reason for going down this path? Have you
> considered alternatives? You might find it easier to us a
> CHAR(31) column, in which case you could use SUBSTR to
> address each, individual flag. For example:
>
> SELECT *
> FROM my_table
> WHERE SUBSTR(x,23)='T';
>
> I suppose you could even create function-based indexes to
> help such queries.
>
> If you really want to work in terms of bits, then you need
> to think in terms of 1, 2, 4, 8, etc.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to
> Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
>
> How can one set and retrive the information in Oracle based on bits being
> set? Are there any draw backs from using too large of a lp> NUMBER? I can
> guess one.. wasted space. Thanks in advanced,-Lizz
>
> lp> ---------------------------------
> lp> Do you Yahoo!?
> lp> The New Yahoo! Search - Faster. Easier. Bingo.

Content-Type: text/html; charset="us-ascii"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).