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

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

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

country

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
US	NA	USA
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

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

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
US	NA	USA	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_extracountry

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.