When you are designing software that updates data based upon business rules, or that creates new rows of data from somewhere else, be sure to pay attention to the creation of a useful audit trail. Yes this is more work than just updating the data or appending new records, but taking the care to do so has several benefits.
Folks in the financial or security industries use the terminology of Audit Trail to identify who accessed or changed which records, and when. A business audit trail however can be a more useful resource than for just tracking down suspicious activity.
Audit trails are useful both at record-level granularity and also at the file-level of detail. At the record level track the date and time that rows got created or updated, along with the name of the process performing the updates. Along with a timestamp, updates to master tables should also include the user ID and their IP address.
When you are making a global change behind the scenes programmatically to a file, create an easily identifiable annotation in a new, otherwise unused field.
If you are executing complicated business logic that determines activity that could have material impact to a customer, store the intermediate values along with the timestamp and version information; be sure to save this information for both affirmative actions and those that, in the end, result in no activity (sometimes the questions are about why things didn't happen).
For file level auditing it is often useful to have a logging table to track the batch processing of files, indicating a timestamp and overall record counts. Files being passed between systems should have a trailer record that contains both a record counter and a version indicator from the program that created it.
Audit trails assist in ferreting out performance problems and flaws in applications. They are also useful documentational research tools when a Support department needs to explain why a particular behavior occurred (or did not occur).
Tuesday, April 18, 2017
Thursday, March 16, 2017
A fairly substantial design issue when planning the logical design of a database is how to "chunk" it. I don't mean vertical or horizontal partitions (for performance optimization) nor segmentation for security constraints. Rather, I use the term chunk here to signify how you associate large swaths of data with business processes such that you can restore a subset of the database.
Here's an operational example. You have several customers for whom you retrieve monthly or weekly datasets of new input, at random intervals for each customer. After you ingest the data you run three large processes to end up with a pick list of what to ship to each one. Say the whole process takes a couple weeks of elapsed time.
One day, after the second big processing step, you are informed that one of your customers sent you the data for the wrong store. What do you do now?
Planning for this type of occurrence takes careful attention to detail. Any manual data updates that occur outside of automated production need an audit trail so you may re-apply them after your data is restored. Large aggregation processes need checkpoints or staging tables so you may recalculate after a backout.
Making contingencies for large contiguous corrections, from the start of logical database design, is the artful way to safely chunk your database.
Wednesday, February 15, 2017
Any company that has been around for more than six or seven years has had to deal with changes in the technology stack. Naturally this has serious implications to the clerical staff: those that actually deal directly with the customers. Of course most successful companies hire employees who are quick to learn anyhow. Yet when you have major system wide changes, it is of tremendous help to have a couple folks on board who are both astute at learning on their own as well as at communicating to and teaching the new technology to others.
It takes a certain type of personality to be comfortable with teaching coworkers: this is not like an elementary school teacher nor even a college professor. To be effective the instructor needs to be comfortable enough in their own learning abilities to stay "ahead of the curve" and hence secure in their position.
They need to possess patience for those who are slower to grasp new concepts, and yet appreciate the effort their fellow employees are making, and have a means to be continually encouraging. Companies that have found the keys to long term survival have also found how to keep their internal "teachers" engaged and on board.