Wednesday, May 17, 2017

Artistic Options

On your browser (under the Tools menu) you will find an item that says "options." And when you are developing code for your employer using the latest IDE, say Visual Studio, you can naturally set the look-and-feel "options." So how is it that within the software you create for your coworkers you left out the Options settings?

Maybe personalization is of less importance when you have two hundred clients rather than two million, but I also believe that a large part of the failure to personalize is deliberate scope restrictions to begin with. In other words if only twenty percent of the users need to see "purchase history" then we segregate it off to a separate screen. It's as if we pull the oysters off the regular menu since only five patrons regularly order them and they know how to ask for them.

Occasionally during design you will discover however that several groups of people work on essentially the same subset of data, although you will hear preferences expressed in terms of "I'd rather see it this way." I'd rather have a pulldown list. I'd rather have all the ship-to data on the side.

Heck, even restaurants that have a tightly limited menu allow you the flexibility to build other victuals given their ingredients. Go to In-n-Out and you can get a 4 by 4 or Animal Style without the carhop even blinking an eye.

Sometimes screens can be cleaner (and easier to code) if you design from the start with the idea of user-selected optional components or views. You will please more folks if you allow for personalization: expand their options!

Tuesday, April 18, 2017

Artistic Auditability

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

Thursday, March 16, 2017

An Artful Chunk

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.