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.
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.
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:
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Choosing an efficient design for Boolean column values
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.