Database Design: A Brief Rant About Quality

You've perhaps seen this item in the news, which discusses the woes which have befallen the Tokyo Stock Exchange and one of Japan’s largest brokerages after a typographical error in a stock trade resulted in a loss of at least $225 million for the brokerage firm involved and also contributed to an almost 2% decline in the Nikkei 225 average on the day of the event.

The details of the story are fairly shocking, especially if you’ve done systems work: the trader’s intention was to sell one share of a particular company’s stock for 610,000 yen (US$5,041). However, the figures were transposed by the person entering the order, so what ended up in the system was an order to sell 610,000 shares for one yen (less than one US penny).

If you’ve ever written data entry validation (and if you’re reading this blog and you’re not related to me, I’ve got to assume you’ve at least considered it), I’m sure you can see all sorts of opportunities for error checking here. For instance, if the price for a sell order is markedly below the stock’s previous closing price, a warning prompt with a confirmation could be popped.

It gets better, though (or worse, depending on your outlook): the 610,000 shares offered in the order comprise forty-one times the number of the company’s total outstanding shares, but the automated trading systems at the Tokyo Stock Exchange processed the order anyway! How easy would it be to write a check to catch that condition?

$225 million (and counting) lost, all for want of a data validation. We could probably write the code to fix both of these issues in ten minutes.

The media reports that the Japanese government has rebuked both the stock exchange and the brokerage firm involved. If the government can find the people who designed, built, and tested the trading system, though, it should rebuke them too. This kind of negligence does nothing to build confidence in our industry or its practices.

The moral of the story is perhaps best summarized in Pond’s Second Law of System Design (the full list of Pond’s Laws will be published sometime soon): All user input is inherently evil and never to be trusted. This may seem obvious, but in light of current events it bears repeating: data validation is a key part of guaranteeing data integrity, and there should be no edge case too bizarre to merit consideration in this regard.

We in this business ignore this rule regularly, and at our own peril, as this episode demonstrates. Somewhere in the world, there’s a development team whose negligence cost their employer/client over $200 million. I sure wouldn’t want such an event on my resumé, and I doubt you would either.