Relational Design: Great on the Hard Drive, but...

Relational Design: Great on the Hard Drive, but…

If you work with reports, you probably work with data. And if you work with data, you probably work with relational design. Relational design, of course, is the way in which you relate your data by breaking it up into normal form. I don’t want to spend hours giving a lesson on normal form, but suffice it to say that the basic concept is that instead of entering a name, address, phone number, or e-mail address into every field of every sales record (resulting in duplicate data inside a table if a single customer makes multiple purchases), you keep that information in a separate Customer table. With each customer you include a customer number. Then inside the Sales table, you have a customer number, relating each sale to a customer. This keeps your database much smaller. Why have Bill Gates’ phone number and address over and over in your computer if you’re selling computers to Microsoft? Once is good enough, thank you.

The problem is that just because you design your data in relational form doesn’t mean you want to design your reports in relational form. Yes, if you and a couple of coworkers are the only ones reading a report, then fine; you can make them look however you want. But if you’re sending reports out to millions of, oh, I don’t know, cell phone customers, then you probably want them to be as readable as possible. Why? Many reasons. First, you won’t get two hundred thousand angry and confused customers filing a class action suit against you, accusing you of trying to deceive them. And second, you don’t want all those two hundred thousand customers plus a half million more flooding your support center needing help understanding their bills. And third, you want to keep your customers and not lose them to the competition.

Here’s a case in point: A database for a phone usage system is likely to have a table containing usage information (that is, information on each phone call), which will in turn contain a code describing the type of call, such as whether it’s a free weekend call, or an evening call, or whatever. The code can, in turn, be matched up to another table that describes each call.

Suppose the usage data has this kind of information:

From	To	Time	Duration	Type of Call
661-555-1212	405-123-4567	8:35	13	A
661-555-1212	212-999-1111	12:52	17	B
407-111-3535	661-555-1212	22:20	5	C

The type of call is a code with a table that might look like this (of course, the table would have more entries, but this is enough for the discussion):

Code	Call Type
A	Daytime rate
B	Daytime cell-to-cell rate
C	Evening rate

And now the fun begins. You want to print this information in a bill that you send to the customer. What do you do? One way is to follow the same structure as the tables, by printing exactly the preceding two lists. That’s pretty simple to program. If the customer wants to find out the type of call, he can simply look at the Type of Call code and then look up the description in the second list. And that’s the way many phone bills are printed.

Here are two possible SQL statements that would do this:

 select * from calls;  select * from calltypes; 

Easy enough; just dump the data out from the tables. But this has major problems. First, as much as we computer programmers are perfectly fine dividing out information into normal form, the same isn’t true for people outside the computer world.

RULE

Normal people don’t understand normal form.

(I know, we’re the normal ones, but I just thought that was a clever way to express it.) As much as this might seem hard to believe, it’s true. Ask people what they think of the little “code” part of their phone bills, and you’ll quickly discover that they all hate it. Nobody wants to scan through a list and see some strange letter or number and then have to go look it up to find out what it means. (Once again, they think it’s dishonest.) And by the way, the list of codes is often printed on the back of the bill, out of the way, making decoding the bill even harder for people.

So why do the bills look like this? Because the programmers are usually the ones who came up with the layout, and the programmers printed them in the way that made sense to them. But:

RULE

The way a programmer thinks isn’t necessarily the way everybody else thinks.

To a programmer, the data lives in normal form in the database, and therefore it makes perfect sense to print the data in normal form on the report. But that’s simply not the best solution. The best solution is an easy one: Instead of printing the codes, simply print the meaning behind them, like so:

From	To	Time	Duration	Type of Call
661-555-1212	405-123-4567	8:35	13	Daytime rate
661-555-1212	212-999-1111	12:52	17	Daytime cell-to-cell rate
407-111-3535	661-555-1212	22:20	5	Evening rate

Of course, you might run into the problem of this data not fitting on the report. What do you do now? Throw in the towel and go back to the previous, bad solution? Of course not. You barrel forward and fix the minor layout problem.

RULE

Never choose a bad solution simply because the bad solution is easier and takes less time to implement.

Go forward and find a better solution, even if it takes a bit more time. The space problem has a couple of easy solutions. First, you can consider a smaller font, although be careful here because you realistically have to consider customers who might not have perfect 20/20 vision.

But if a smaller font is not an option, then what do you do? Here’s a good solution: Abbreviate. Look at this:

From	To	Time	Duration	Type of Call
661-555-1212	405-123-4567	8:35	13	Day
661-555-1212	212-999-1111	12:52	17	Day cell2cell
407-111-3535	661-555-1212	22:20	5	Evening

While I’m not big on cell2cell, at least it is a pretty good abbreviation. But if people don’t like that, then just use cell to cell, still dropping the word rate and shortening daytime to day. But notice I could have fit the whole word daytime in the first row. Why did I abbreviate it day? Because I used day on the next row, and I don’t want to be inconsistent. That’s all.

But if you want to see some seriously bizarre codes, look at the printout from a car repair shop. I was once involved on such a project (for an oil change center, actually), and I dare say that the people writing the program gave little or no thought to what the customers would want to see in their bills. Car repair statements have codes for the type of car, the work done, whether items are parts versus labor, and on and on. In the interest of space, I won’t reproduce a statement here, but take a look at one and you’ll see what I mean.

Now a quick point about all this: I can hear some of you saying, “You crazy usage person, you don’t know my work, you don’t know the problems that I encountered, and you don’t know that this was the best solution.”

Well, to you I say, “I don’t care. Don’t tell me it’s not possible because I know it is possible to do better.” Remember the story of how I tried to get my boss to accept the printouts in giant paper because a smaller font wasn’t possible. He told me to go fix it anyway, and eventually I did. Often problems like this are really just a matter of spending a few extra hours or a few extra days on them.

RULE

Never say a better solution isn’t possible.

Data versus Information

One thing that can help you create reports that are easy for laypeople to read is to recognize the difference between data and information:

• Data refers to what’s stored on the hard drive.

• Information refers to how the data is interpreted by the brain. In other words, information is data with meaning added to it.

Warning

Be forewarned, however: Not everybody agrees on these definitions. But it’s the concept here that I’m talking about, so you can use whatever words you want when I use data and information.

For example, as I look at this word processor I’m using, I’m seeing words and sentences and headers and other information. But the program is storing this information as a bunch of meaningless bytes. By itself, a byte of data has no meaning. Here’s an example of a byte of data: 01000001.

By itself, that’s just a byte, which is eight bits, with the lowest bit 1, the second-highest bit 1, and the rest 0. But when you add meaning to this byte, you get information. That’s a number 65, which is the ASCII code for a letter—small letter a. Or, it might be a student’s score on a high school remedial math test (not yours or mine, of course!). Or it might be one piece of an entire string of 64,000 bytes of data in a file, and that file contains an encrypted version of a spreadsheet listing employee salaries.

Thus:

• 01000001 is data, which has no meaning.

• The letter a is information (on some levels, that is, although by itself it’s not particularly meaningful).

• A student’s score of 65 on a remedial math class is information (and bad information for the student’s parents, on top of all that).

• An encrypted spreadsheet is data, since in its encrypted form it’s not directly of much use to us humans.

Remember, then, data by itself has no meaning. Once you interpret the data, place it in a context, and add meaning to it, you have information. That means, then, that data is what lives inside the computer. Information exists in our brains. Think about this: I’m staring at a computer screen filled with text and menus and toolbars and so on. But that’s just a bunch of bits and bytes inside the computer. My brain interprets this as a document containing the chapter of a book. The computer doesn’t know that this is a chapter. The computer doesn’t really know anything at all, at least anything with meaning. (I won’t get into all the philosophical nonsense about whether computers have existentialism. Yuck.)

So what does all this have to do with reports? Lots. When you create a report for laypeople, remember that the laypeople have no concept of the data behind the information, and they don’t want to. That means they don’t want to have to deal with mysterious codes and nonsensical data. While such data might have meaning to you, it doesn’t have meaning to them.

What is that c thing? That’s the code for cell-to-cell phone calls. But does it have meaning? Is it information? No. It’s a code, or just data to the laypeople. That doesn’t belong on a report.

RULE

Think about whether you are providing data or information. If you’re providing data, get rid of it and replace it with information.

Of course, don’t simply provide a legend to your data, as I talked about in “Relational Design: Great on the Hard Drive, but…” in this chapter. By providing a code for the phone call and then providing a separate list, you’re simply providing data and not information. Skip the data and get right to the information.

Read It the Way a Customer Would

One of the reasons I’m filling this book with entertaining anecdotes is to remind you that while you might be a programmer developing software, when it comes to other products you’re not a designer but a consumer or customer. In this book I complain about all sorts of products that we use on a daily basis. And as you use these products, think about how you, as a consumer, are particularly bothered by the product and what you would do differently if you had designed it.

Then, when you’re building your software, do the reverse: Remember what it’s like to be the consumer using these non-software products, and then think what life is like for the consumer using your software. Remember, as much as you might not like to think about this, to the consumer you are the evil engineer who designed the product. If you build a bad product, the consumers will be talking about you when they say, “Why can’t they make their software do this and that?”

Now, of course, my goal isn’t to cause you to snap and end up on some heavy psychotic medications because you know all these people out there are talking about you and angry at you. My goal is to help you be more conscious of the needs of the consumers and, in turn, create software that they will actually like. And how do the consumers judge the software? If they use the software directly, they judge it through the user interface. If they receive reports and printouts, they judge it through the reports and printouts—but only to a point.

The truth is most consumers who receive reports and printouts (such as phone bills) are not using these sheets of paper to judge the software. They are using the papers to judge the company that sent the papers. If I see a mistake on my bill, I don’t blame the computer program; I blame the company. (And if I call and the person on the phone blames the computer, I know better. It’s the company’s responsibility to send me correct bills, and it’s the company’s responsibility to make sure the programmers are doing the very best job possible. Garbage in, garbage out, as they used to say.)

When you’re designing a report, then, you need to look at the report through the eyes of the people who will ultimately be using the report. Here are some questions to ask:

• Is the information easily accessible and quick to find?

• Will the users spot the most important information first?

• Is the information complete?

• Is the information clear?

• Does the information provide the users with the answers to any questions they might have and help them accomplish their goals?

These are things the customers will look for. Remember, if a customer receives a phone bill, she isn’t going to say, “Wow, what a nice sans serif font they used on this bill, and look at how they made good use of an eight-character tab.” Instead, she is going to first look at the amount due and then, usually, the date that the bill is due. She could care less about the font or if the report includes the officially trademarked corporate logo.

She’s also going to want to easily find a way to contact the company if she disagrees with the bill. Is the 800 number easy to find? Is the website available? (And please, have a website.) Can she pay the bill online? Is the account number clear? (Sometimes I’ve seen bills with an account number as well as other numbers, and the bill isn’t clear to me as to which number I need when I go online to pay!)

Then you should show the additional information, such as a breakdown of the amounts. But remember, for amount breakdowns, customers don’t want to see the kind of abomination I started this chapter with. For example, customers don’t want to see this:

Total carried over from pg 2:	$35.25
Federal tax	$2.25
State tax	$1.05
City tax	$.75
Total tax	$4.05
Additional fees	$2.52
Total taxes and fees	$6.57
Interest	$8.97
Total due	$50.79

Do you see what’s wrong with this? The second, third, and fourth lines are all added up, and the total is on the fifth line. The next line is then added on, for a new total shown on the following line, as Total taxes and fees. Then an Interest amount is shown, followed by the sum of Total taxes and fees, Interest, and the Total carried over from pg 2. That’s too darn confusing! And worse, some bills with this kind of a format will have some items in bold and maybe a few underscores to try to make it clearer. Try as they might, they’re not helping. The bill is a mess and impossible to read. Consumers hate this kind of bill (and again accuse the company sending the bill of trying to deceive them).

One slightly better way is to indent, like so:

Total carried over from pg 2:			$35.25
Federal tax	$2.25
State tax	$1.05
City tax	$.75
Total tax		$4.05
Additional fees		$2.52
Total taxes and fees			$6.57
Interest			$8.97
Total due			$50.79

But I would argue that this still isn’t great. It’s better, but not great. While indentations such as this are easier for somebody with a bookkeeping background to understand, laypeople still aren’t accustomed to multiple levels of indentation. I’d limit it to just a couple levels, like this:

Total carried over from pg 2:		$35.25
Taxes and fees		$6.57
(Taxes and fees are broken up into the following)
Federal tax	$2.25
State tax	$1.05
City tax	$.75
Additional fees	$2.52
Interest		$8.97
Total due		$50.79

This is looking better. But I still don’t like some parts. For one, I don’t like the “Total carried over from pg 2” item. Here’s why:

RULE

Don’t have several rows labeled Total.

I would argue from my own experience that one reason phone bills are so hard to read is they have a million items with the word Total, when, in fact, the bill can only have one total, the amount due. Further, the phrasing of “Total carried over form pg 2” is stiff. Instead, I would propose the front page have a list like this:

The amounts on page 2 add up to	$35.25
The amounts on page 3 add up to	$15.54
For a grand total of	$50.79

Then, page 3 (which is the page I was working on before) would have something like this:

Federal tax	$2.25
State tax	$1.05
City tax	$.75
Additional fees	$2.52
Interest	$8.97

And don’t include a sum on this page . The reason is that the person questioning the bill who sits down with a calculator may accidentally add up all the numbers and then add in the total as well, doubling the amount for that page. Then the value won’t match up with the amount due, and he will have to go back and do it all over again. That causes a frustrated and confused customer. If she wants to see the total for page 3, she can add it up with a calculator and then compare the number to the $15.54 shown on page 1 after the words “The amounts on page 3 add up to….”

In my opinion, these final two lists are the best for dealing with itemized details. The reader can clearly see how they add up, and she won’t accidentally add in an extra total or two, and the question of where the total came from is clear.

But before I close this section, let me include one final note and some thoughts:

RULE

If a number is supposed to be subtracted from a total, show it with a minus sign.

Don’t have this kind of thing:

Previous amount due	$97.35
Payments	$80.00
Current amount due	$57.36
Total amount due	$74.71

Instead, fix the wording problems (…amount due, …amount due, …amount due, each with a different number!), consider reordering with a bit more chronology, and get a minus sign in there, like so:

Amount of this month’s bill	$57.36
Amount you owed last month	$97.35
Amount you paid last month	-$80.00
Total amount due	$74.71

Earlier generations might have frowned on words such as owe and might have liked to see the words Thank you after the amount you paid last month. Skip it. People these days know very well they owe the money and don’t see it as any more negative than the word due. In fact, I’d argue that people today are less intimidated by the word owe, since due has the connotations of “You will pay me NOW!” And further, people aren’t impressed with the words Thank you showing up on their bill. Nobody today believes some sweet elderly secretary with photos of her beautiful grandkids on her desk sat and carefully typed up the bill and was putting a personalized “Thank you” note on it. People know that a giant printer in an operations center spit out the bill and that no human hands even touched it. The words Thank you are as much of an insult as a mail-merge program printing out reports in a handwriting font to make us think somebody wrote something by hand.

But not to harp too much on phone bills, let me talk about some other reports people receive. Almost everybody shops at a grocery store. And our cash register receipts are getting longer and longer. And to make matters incredibly complicated, in some states, food is not taxed while other merchandise is.

The other day I went to the grocery store and spent $132.92. My receipt was really long. And it’s broken up into several sections. Here are some highlights (I won’t list the whole thing here):

General Merchandise
Map		4.59		CT
Snowbrush		5.99		CT
Gen Mdse Sub (before tax)		10.58
Grocery
Tomato Sauce	.39		F
BBR CKN BRST	1.49		F
1 @ 2 / 3.00
Carrots	1.50		F
Toothpaste	2.94		T
Spaghetti	1.39		F
1 @ 2 / 3.00
Soup	1.50		F
Total
Total Ad/In-store savings	14.34
Total Tax	2.66
Total	132.92

This one isn’t too bad; I’ve seen worse. But the part I want to draw your attention to is the lines that say 1 @ 2 / 3.00. That’s saying I bought one item that’s priced at two for three dollars. (And so now I know, looking at this receipt, I didn’t have to buy two to get the deal!) The first 1 @ 2 / 3.00 line is between the breaded chicken breast and the carrots. Which does it apply to? One is 1.49 and the other is 1.50, both conceivably two-for-three-dollars (give or take a penny). Now at first I thought the line applied to the item just before it. But that’s not right, because look at the second 1 @ 2 / 3.00 line. The item before it is 1.39 and the one after it is 1.50. Apparently the 1 @ 2 / 3.00 refers to the item that follows.

But notice also that some items have a T by them, and some have an F. In this state, food is not taxed, apparently. And so the F probably stands for Food. Since the toothpaste is not food, it’s taxed and gets a T by it. But these are more secret codes! As a programmer, I was able to easily figure out what T and F mean. But what do those two letters mean to most people? True and False, of course!

And why do some taxed items appear under General Merchandise? Probably because the store divides the items into two distinct accounting units. But why should that matter to me? All I know is that I bought a bunch of things. I don’t care about the accounting. They’re just confusing people.

Maybe instead they should have two sections for the receipts, non-taxed items and taxed items. Then they wouldn’t need to have a little T or F beside each item.

Then, of course, we have monthly credit card statements. These in general aren’t all bad; however, the part I want to talk about is where they figure the interest. I won’t reproduce one here, but take a look at one of your own statements and see what I mean. How clear is it? Did they really think of you when they printed it? Are you a bit suspicious of them? Now go back to your engineering hat. Do you want your customers to be suspicious of you? Probably not.

Wasted Paper in Your Mailbox

The days of dumping piles upon piles of paper that was touched once and viewed maybe twice are quickly going away. People are tired of seeing the trees getting destroyed, and they want to save paper. Think of all the bills and bank statements you’ve received in your life. (And I’m not talking about junk mail, either.) The landfills are getting deeper and deeper, and companies are also spending millions of dollars on the paper that’s ultimately ending up in the landfills.

And so today, often phone bills don’t come with a detailed list of each and every call. Instead:

SUGGESTION

If you want your customers to have access to the details, include an option to provide it online. (Don’t automatically provide it online, however, as not everybody has Internet access!)

The Web is a good thing, although I shouldn’t have to tell you that. Put the information online. Don’t give all the details in the report if they’re not absolutely necessary in printed form.