Software

General discussion

Gravatar
0 Votes
Locked

Choosing an efficient design for Boolean column values

By MaryWeilage Editor ·
This week's Oracle e-newsletter focuses on choosing an efficient design for Boolean column values. When you design a database table structure, what strategy do you choose for storing a logical Boolean? Do you think you'll follow the tips described in this e-newsletter?

If you aren't subscribed to the free Oracle e-newsletter, you can automatically subscribe to it by pasting the following URL into your browser:
http://nl.com.com/MiniFormHandler?brand=builder &subs_channel=bldr_front_door&list_id=e050&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

Efficient - for whom?

by rob_fergusson In reply to Choosing an efficient des ...

The article implies that minor technical issues ought to drive database design decisions with enterprise implications. Specifically:
- 2 bytes vs. 1 byte. This was a good argument 20 years ago. If you really believe this, then trade grief for storage by using BITAND() to pack 8 bits per byte.
- optimizing JDBC coding

Ad hoc access by the barely trained hordes is often a more important consideration. A byte-optimized, JDBC-optimized database design isn't always an enterprise-optimized design.

gravatar
Collapse -

some more things to consider...

by swstephe In reply to Efficient - for whom?

I know it sounds nitpicky to even mention using something other than 'Y' and 'N', but the difference can add up for people who are heavily into JDBC and J2EE projects or want to squeeze each record into a small number of blocks.

Using BITAND on the database is really hairy, you need to get numeric values, (which can only have 99 possible values in one byte -- it is binary-coded-decimal internally), or if you want to convert from RAW to number on every value.

One thing I forgot to mention is that 'Y' and 'N' is based on English. I guess Spanish should be 'S' and 'N', French 'O' and 'N', etc. Same with 'T' and 'F'. You could move your database a step closer to being NLS-friendly by just using something somewhat international. That is very important to projects in Europe and Asia.

It may also be a bit friendlier on users to type 1 or 0 rather than having to type 'Y' and 'N' with the danger of mistyping quotes.

Also, there are some queries that can get cleaned up with math to generate nice IF-THEN-ELSE logic:

select is_manager*bonus*5-on_commission*bonus*2 ...

means "if employee is manager, then bonus * 5, if on commission, then subtract bonus * 2" ... some people may prefer that to ...

select decode(is_manager||on_commission,'YY',bonus*3,'YN',bonus*5,'NY',-bonus*3,0) ...

Back to Software Forum

Start or search

Related Discussions

Related Forums