Be A Data Integrity Watchdog

Funny thing happens when you start to put data into a database. It becomes important. At one point it might have seemed like a nice idea to save the visitor’s IP address. Slowly, as the system evolves, little branches of code pop up around the fact that the IP address is populated. Suddenly you find yourself in a position where you have to protect that piece of data. You can’t sit by idly and let that improperly formatted IP address bring down the whole system. You have to guard your system against these intrusions.

And the intrusions will happen. I’ve designed a number of large systems, and the only common denominator is that somehow, at one point or another, at least some of the data will get corrupted. Transactions fail, databases crash, bugs show up in the margins, users enter in stupid information, or hackers attack. I had been spending time thinking about this issue at The Sporting News, but it wasn’t until MLB that it really congealed into something useful.

In the fantasy baseball domain, a typical roster transaction leads to the addition or removal of a player from a particular manager’s roster of players. If the player is added to player P’s roster he should not be available for any other manager in that league. If he’s removed from P’s roster he should be available to all other managers (including P). A player can’t be on more than one manager’s roster at the same time.

It turns out that every once in a while something hiccups and one of these rules is violated. Over the years I learned that the single most important step to fixing the problem is to make sure it doesn’t get worse. So, for instance, imagine a manager attempts to drop a player from his roster but something goes wrong. The system shows that the player is still on the roster, but he’s also technically available to others. Now that the data is corrupt it’s crucial that the system not allow the player’s status to be modified any further. It can very quickly become an impossible problem to solve if the player is picked up by another manager, then traded to another team, then dropped, etc.

I’ve spent many, many hours fixing transactions by hand. I worked on fantasy applications for over 7 years continuously, and in that time I can’t remember a single year where I didn’t have to fix at least some transactions by hand. Let me tell you, it suuuuucks. It really suuuuucks. Sucks and blows.

With that in mind, I developed a scheme where instead of waiting to hear that some player is on two teams via the message boards I take matter into my own hands. I designed the system to check each player involved in a transaction for corruption immediately after the transaction is committed. If I detect that one of the fundamental rules were broken (e.g. owned by more than one manager), the player is immediately frozen. No further transactions on that player would be allowed until an administrator can come in and fix the issue.

So for a small incremental cost I’ve bought myself some peace of mind. And I can absolutely tell you that it paid off, time and time again. It just took a different way of thinking about the problem – being proactive versus reactive, protecting the integrity of all that data.

Be Sociable, Share!

Leave a Reply