Recently I wrote about the problem of storing data for a single record type in two different tables and using the table the record belongs to in order to calculate an implied status. The headache was pretty clear, as well as the potential for data loss if not everything were properly kept up to date.

However, there are other ways to implement implied statuses that can get you in trouble as well. One is the use of a record table in correlation with an event table to track changes to the record table. While the use of these tables is a good practice where event histories need to be maintained, depending on them for statuses is suspect at best.

The reason why it's a bad idea is that it assumes that the developer, and every developer to follow, is intimately familiar with the business logic of the records being manipulated, and combine that logic into all their database views and procedures. If these records do not get combined properly, disaster is sure to follow. Even worse, the logic is likely to not be clear to a new developer seeing the database for the first time, leading to potential "alternate" solutions.

The other problem with these implied statuses is that they require the processing time to be calculated. They're not readily available to pull from a table, like an explicit status.

The worst approach I've seen was to set up the main record table with a status field, not update it on an event, and then calculate an implied status. What happened then was developers creating new views tied to the table's status field, which didn't contain the correct status. This lead to digging into the system, because the previous development team was no longer with the company, to find why some statuses were not being filtered properly out of a view, and discovering the implied status calculation based on the events. It was very misleading a quite a headache to straighten out.

The lesson is that if you need to use implied statuses, make them clear and well documented. Otherwise, stick with updating the explicit status tied to a given record to store your status information.