Software Journal

Venn Diagram queries in Oracle have saved the day for me many many times. Consider this:

You have a table “analyzethis”, and say mv (modelvalue) is in 0/1 and tv (TruthValue) is in 0/1. Then, the following query does something nice.

select sum (val), match from
(select count(*) val, ‘M’ as match from analyzethis where mv=1 and tv=1 union
select count(*), ‘M’ from analyzethis where mv=0 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=1 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=0 and tv=1)
group by match

This query returns:

Sum(val), Match
1450, M
2300,N

Of course a similar result can also be obtained by decode, sign and square functions.

Some things go without saying. Such as, a telephone number is a number. Like 703 123 4567. But if I go back to Dave Roberts, who taught me CSCI 242 an infinite number years ago, he would say, can you give me your telephone number, and add it to Peter’s and subtract Anna’s? Well, that really doesn’t make much sense.

In database modeling terms, telephone number is a String, a varchar2, a text. Not a number. This pretty much goes without saying as well, for anyone who has been data modeling for more than a few days. Here, are a couple of reasons telephone number is considered a String, not a number:

1. It can have symbols. People enter their telephone numbers themselves, using hyphens, parenthesis, plus sign, alphabetical (1-800-CALL ATT), etc. The database mostly needs to save the data that the user entered, and show it later. It would be unacceptable to modify the user’s data and show it in a different format. Would be cute and dumb at the same time.
2. You don’t do any Math on the telephone number. You don’t take average, or sum of all telephone numbers, like you do with invoice_total and avg _time_in_queue.

Now, there really are systems that store a telephone number as a number, not a varchar2. That is primarily done as a short cut for validation. These systems have those annoying (but perhaps highly functional) websites where you get those messages (“Please use numbers from 1-10 only.”) when trying to enter a white space or parenthesis in a telephone number. The good aspect of that is, that they are very very good at catching the 10 digit US/Canada telephone numbers. Works absolutely great for utility companies and gas electric cooperatives with US as their only market, but if international market space is your thing, then not so much.

I take this as my gentle remember, that all rules are meant to be learnt, and then we can break some. The good news is that you get to pick which ones you break! The bad news of course is that you need to learn them first, and then break them.