The Spreadsheet Conundrum
I am in the middle of a three-day training course at work on design patterns. I am thoroughly enjoying the topic. The course is being delivered by Ken Pugh, author of Interface-Oriented Design, who has the kind of riotous Duchampian word choice and delivery that I appreciate.
He brought up an interesting topic yesterday that didn’t click until today. On a flip chart, he draws a spreadsheet and tells us to consider the design of a spreadsheet program.
Simple design question: do you store the spreadsheet as a collection of rows, or do you store it as a collection of columns?
Well…if you store by rows, what happens when you need to add a column? You need to go through every row and add a column.
Well…if you store by columns, what happens when you need to add a row? Same story.
Pugh calls this the Spreadsheet Conundrum. It is really a specific incarnation of “It Depends.”
I didn’t get it. The example was related to a multi-lingual site. Suppose you have two web pages: order.jsp and customer.jsp, and two languages: English and German.
If you stored by rows, then adding a language is easy. If you stored by columns, then adding a page is easy. Unfortunately, the discussion headed towards look-up tables, hash maps, and other implementation-specific details. I lost the real point in these details.
Today, I asked Pugh what he thought about a sticky example back from my most successful post, The Getter Setter Debate:
Rather than writing something like
statement.append(account.getTransactions())instead we would write something more like
His reply: “ahh, this is an instance of The Spreadsheet Conundrum.” In the flash of a vision, it hit me.
account.appendTransactionsTo(statement), aside from avoiding the reviled getter, is perfect if accounts are shown in list boxes in the GUI and sent to the printer as well, like so:
Just add columns. So now you have
account.appendTransactionsTo(document). This is, aside from the potential violation of architectural layers, the right choice here in this environment, i.e. where we have multiple output choices for the account.
But now, let’s say that a statement contains more than just accounts. Statements include information about the customers who hold the account, as well as, perhaps, the mutual fund holdings of the account:
Here, it is easier to add rows, instead of columns. Different environment.
Now code should look like
statement.appendHoldingsFrom(account). Of course, inside these methods, a dreaded getter, or its regulated equivalent, or its friend class equivalent, has to be used.
Here’s an even simpler example in English: should the Phone dial a Phone Number, or should the Phone Number dial itself on the Phone? In geek speak:
The answer: it depends!TM
If you are likely to have many dial-able devices (basically anything with a number pad) and not many types of sequential numbers, then the phone number should dial itself on the phone. From a higher level, the phone number should dial itself on a dial-able device.
On the other hand, if you are likely to have many different sequences of numbers (like phone numbers, PIN numbers, and credit card numbers) and not many dial-able devices, then the phone should dial the phone number. From a higher level, the phone should dial a sequence of numbers.
Now I’m asking — what happens if the answer is: both?
Well, looking at this from strictly what is in common, I can see that both attempts have repeated methods. In the first, both
document have three repeated methods. In the second,
holding all have basically the same method.
I think you can Extract Interface here, but something about that solution doesn’t sit right.
It is exactly this sort of perpetual and contextual design that leads me to declare that software design is an endeavor of masochism. How I wish it were like math, where there is one right answer.
That is what the Method Regulator pattern, the flailing I did in The Coder’s Whiteboard, and my soon-to-be-a-topic post on simulating friend classes in languages that don’t support them, are all about.
You are getting to see me fruitlessly struggle against the fact that it always depends and there is no right answer. I so desperately want to have a general answer, so the next time I sit down and work on my side project, I don’t have this debate with myself yet again.
I waste the same amount of time on it every time I sit down and look at it. Moreover, the debate itself is exhausting. I want a decision and then I can move on.
But that isn’t going to happen. It depends. Try to think of the Spreadsheet Conundrum next time, and ask yourself the hard question: which is more of a pain, rows or columns?