Thursday, January 1, 2015

A Classic Example of "Broken As Designed"

This is an extremely annoying "feature" I keep stumbling upon in all kinds of software, be it customer-facing or "internal" business applications. I have been seeing this for years (if not decades) now, and it doesn't seem to be going away.

Imagine a web site that sells tours (just an easy to understand example). On a page, you see two dependent drop-down lists: one with continents and the other with countries (again, this is just an easy to understand example). Say, you select "Europe" from the first drop-down list and "Albania" from the second. The page refreshes and displays something like "Your search returned no results". You keep trying, and -- after 50+ attempts -- it turns out that the site only offers tours that begin in Germany (DE), Spain (ES), and Italy (IT). The other 50 or so European countries on the second drop-down list are there for absolutely no reason (except to annoy users). Sounds familiar, doesn't it?

Let's take a peek under the hood and try to understand how these applications work. For the sake of simplicity, we will assume that all this application is supposed to do is display a list of tours for each country. Nothing else.

Based on my experience, it appears that most applications are designed as follows.

Obviously, there is a table with tours (let's call it tour), an intentionally oversimplified version of which may look kind of like this:

tour_id coountry_id name description duration price
1 DE Crossing the Alps Description of the "Crossing the Alps" tour 6 999.00
2 ES Pyrenean Traverse Description of the "Pyrenean Traverse" tour 7 1099.00
3 IT Cycle Tuscany Description of the "Cycle Tuscany" tour 7 1299.00
    Note: To those familiar with European geography it may have occurred that the itineraries of the first two of the above tours are unlikely to be limited to just one country. That is an excellent observation. We will get back to it later. For now, let's say we are only concerned with the countries where the tours start.

Then, there are two more static tables: continent and country.

continent_id name
AF Africa
AN Antarctica
AS Asia
AU Australia and Oceania
EU Europe
NA North America
SA South America

country_id continent_id name
DZ AF Algeria
AO AF Angola
BJ AF Benin
XB AN all Antarctica
CN AS China
IN AS India
NP AS Nepal
AU AU Australia
NZ AU New Zealand
AL EU Albania
AD EU Andorra
AT EU Austria
DE EU Germany
FR EU France
IT EU Italy
ES EU Spain
CH EU Switzerland
CA NA Canada
MX NA Mexico
AR SA Argentina
BR SA Brazil
CL SA Chile

    The country table is related to the continent table through the continent_id columns. Obviously, the country table above is a much shorter version of what it would be in a real-life application where it most likely would contain about 200 rows.

When a user selects a continent -- say, Europe -- from the drop-down list, a database query runs, retrieves all the rows from the country table where the value in the continent_id column is "EU", and populates the second drop-down list with countries. Then the user selects a country, and another query retrieves all the rows from the tour table where the value in the country_id column corresponds to the country selected. If the latter query returns no rows from the tour table, no tours are displayed. Because the drop-down lists do not "know" if there are any tours on each continent and in each country, users may often experience what I described at the beginning of this post.

There are a few ways to improve user experience in an application like this.

Here is the most elementary:

In the country and continent table, add an extra column. Let's call it tour_count. Set its default value to "0". Create a procedure that will increment the value by one every time a tour in a certain country and on a certain continent is added/activated and decrement it by one when a tour is deleted/deactivated. With the three tours we currently have in the tour table (see above), the two remaining tables should look like this:

continent_id name tour_count
AF Africa 0
AN Antarctica 0
AS Asia 0
AU Australia and Oceania 0
EU Europe 3
NA North America 0
SA South America 0

country_id continent_id name tour_count
DZ AF Algeria 0
AO AF Angola 0
BJ AF Benin 0
XB AN all Antarctica 0
CN AS China 0
IN AS India 0
NP AS Nepal 0
AU AU Australia 0
NZ AU New Zealand 0
AL EU Albania 0
AD EU Andorra 0
AT EU Austria 0
DE EU Germany 1
FR EU France 0
IT EU Italy 1
ES EU Spain 1
CH EU Switzerland 0
CA NA Canada 0
MX NA Mexico 0
AR SA Argentina 0
BR SA Brazil 0
CL SA Chile 0

Now, instead of using the continent table, you can populate the drop-down list with continents from a view like this:
    CREATE VIEW continent_view AS
    SELECT continent_id, name
    FROM continent
    WHERE tour_count > 0

Similarly, instead of using the country table, you can populate the drop-down list with countries from a view like this:
    CREATE VIEW country_view AS
    SELECT country_id, name
    FROM country
    WHERE tour_count > 0

When a user selects a continent from the first drop-down list (in our example, only Europe will be available), the following query will run against the second view:
    SELECT country_id, name
    FROM country_view
    WHERE continent_id = "EU"
and populate the second drop-down list (in our example, only Germany, Italy, and Spain will be available).

Will this approach create additional overhead? Of course, it will; it is up to you to decide what is more important: the overhead or users getting the impression that your application was designed by a third-grader.

More things to consider:

Without any real extra overhead, you can concatenate name and tour_count in the continent_view and country_view views and populate the drop-down lists with those concatenated strings. This will give users an idea of how many results they should expect from their queries.

If the per-country count is low, users may prefer to display all tours on the selected continent instead of just in a single country. You might want to consider giving them such an option (similarly to "all Antarctica"; see the country table above).

As it has been noted above, some tours span multiple countries. For example, the "Crossing the Alps" tour begins in Germany and, most likely, ends in Italy (IT), crossing western Austria (AT) or eastern Switzerland (CH) or both; the "Pyrenean Traverse" tour may start and end in Spain, but its itinerary may (or may not, of course) weave in and out of France (FR) and go through Andorra (AD). You might want to include those countries into tour count as well. I am not going to give detailed explanations how it can be done, but will just say that you will probably need another table (let's call it tour_extracountry) to describe the relationship between each tour that spans more than one country and those extra (in addition to the country where the tour starts) countries:

tour_id country_id
1 AT
1 CH
1 IT
2 FR
2 AD

Bear in mind that for tours spanning more than one country you need to increment/decrement the tour_count value by one for each of the countries, but increment/decrement the tour_count value for the continent only once (unless you are dealing with transcontinental tours and/or countries, e.g., Egypt, Russia, Turkey).

There are probably more things one can come up with to improve the UX here. I just can't think of any more right now, and that is not really the purpose of this post.

Conclusion (sort of):

What is described at the beginning of this post most of the time cannot even be considered a bug because functional requirements are rarely complete enough to address such seemingly minor things. If a system doesn't do something it is not required to do, formally it is not a bug, but it doesn't make it any less ugly and annoying.

No comments: