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
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.
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.