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.

order.jsp customer.jsp

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 account.appendTransactionTo(statement)

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:

statement list box document

Just add columns. So now you have account.appendTransactionsTo(listbox) and 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.appendTransactionsFrom(account), statement.appendCustomersFrom(account), and
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: phone.dial(phoneNum) or phoneNum.dialOn(phone)?

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?

statement document

Now what?

document.appendCustomersFrom(account), and


holding.appendHoldingTo(statement), and

Well, looking at this from strictly what is in common, I can see that both attempts have repeated methods. In the first, both statement and document have three repeated methods. In the second, account, customer, and 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?

13 Responses to “The Spreadsheet Conundrum”

  1. Good explanation of my Prefactoring guideline. Usually there is some factor that suggests that going with either the row version or the column version has better design/maintenance qualities. So the choice “depends” on that factor existing and being able to identify it.

    Refactoring a program from something like the statement.append version to account.append version is not necessarily simple. So spending a little time thinking about the ramifications before proceeding can pay off. You may discover later that the choice is wrong, but at least you thought about it rather than making a flip of the coin decision.

  2. Ladies and gentlemen, we’re honored to welcome the man himself, Ken Pugh, to I Built His Cage!

    You are correct that in practice there is usually an environmental force that will tip the scale to rows or to columns. This was certainly the case for The Project.

    Making them both interfaces, i.e. Dialable.dial(Sequence) and Sequence.dialOn(Dialable), as I mentioned to you Thursday morning, seems to allow you the flexibility to go in either direction, at the risk of going into an infinite loop.

    I think this way, you actually can flip a coin and go either way. I tend to favor the Information Expert idiom whenever it doesn’t clash with DDD; when it does, I tend to favor Single Responsibility / Tell Don’t Ask. Making both concepts interfaces can accommodate a neurotic person like me who is always debating between the two.

    I imagine that the premise of your Prefactoring book, not to mention its title, hasn’t sat well with the You Aren’t Gonna Need It XP crowd. I haven’t really understood their reasoning to start with. If you follow their logic and don’t generalize and abstract concepts until you need to, I imagine development grinding to a halt and being buried underneath constant refactoring.

  3. I wrote a have-it-both-ways comment to Domain-Driven Method-Naming Guidelines here that is pretty interesting.

  4. Philip Schwarz Says:

    hyperlink ‘It Depends’ points to a home page. Is that right?

  5. I’m not sure what a “home page” means in this context. It points to a professor’s university web page.

  6. Philip Schwarz Says:

    Hi moffdub, I have linked to this post from my comment on Two Styles

  7. Raoul Duke Says:

    hooray for languages with multimethods?

  8. i want a leave

  9. пердуперденция роликовые

  10. […] is the Spreadsheet Conundrum: does the phone dial the phone number, or does the phone number dial itself on the […]

  11. gjyalpha Says:

    It’s an interesting topic and enlighten me about what I’m struggling recently.

    And I think we finally have a mix of the two styles:

    phoneNumber.dialOn(phone) {
    for(number in numbers) {

    The statement will be “the phone number dial itself on the phone by asking the phone to dial each number it provides”.

  12. […] you are familiar with Ken Pugh’s work in Prefactoring, this is the Spreadsheet Conundrum; do you add rows or […]

  13. […] we are informed with Ken Pugh's work in Prefactoring, this is a Spreadsheet Conundrum; do we supplement rows or […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: