Archive for the ‘Visions’ Category

Masters of the Database

Saturday, November 6th, 2010

A couple of expert user interface elements to increase control and flow over database applications.

Some fantasize about Megan Fox. I fantasize about easy database application user interfaces. Certainly this includes a UI that allows novices to complete their tasks quickly and effortlessly with nary a thought, but it also includes UIs that do more than get users through specific tasks. It includes UIs that transform reasonably experienced users into masters of the database. Not just users in control, but users in supreme control: user as artisans annealing the data with skill and understanding; an elegant user interface that follows a few simple user-interface elements to promote powerful actions and flow.

Object controls are one such element, supporting consistent interactions for both form-like and tabular displays of data (and graphical too), while  providing a means to easily complete actions on  both individual attributes (fields) and the data object (record) as a whole. In this post, I’ve got two other elements to present:

  • Expert Activation.
  • Multi-attribute Selection.

Both of these are intended for expert users, those that work daily on an app and may have some formal training for it. They are tools that are not strictly necessary -the user can accomplish the same things by other means, but they allow the expert user to do things faster and more fluidly than otherwise.

If there is a general lesson to these elements, it’s what makes a good expert or advanced element or function. An expert element should:

  • Not interfere with typical user operations. For example, you don’t want expert actions crowding your menus making it harder to find and use the commands users use all the time. It’s okay for an expert element to have lower discoverability because, well, it’s for experts.
  • Be consistent with non-expert idioms and elements. You want to make it easy to transition from novice to expert by leveraging the knowledge and expectations that users already have.
  • Provide high power. The element should have multiple uses and be applicable to multiple tasks, allowing the user to accomplish much with relatively little learning.
  • Standard. The element should be present in multiple windows and applications, so once the user learns it in one place, they can apply it to other places. The elements should constitute a language understood by many.

In a perfect world, all expert elements would be self-documenting, so users can discover it easily, but that too often conflicts with the first bullet. Visible signs of an expert element , like menu items, toolbars,  text, and graphics, clutter out the non-expert features, making life difficult for the typical user. By definition, expert elements are used rarely, so that is not a good trade-off. The two elements I’ll talk about here are all virtually invisible, things, like double-clicking or window resizing, that leave almost clue that they exist. This is another reason for them to be standardized -users can’t tell if the element is present or not by looking at the UI, and you don’t want to make them guess. If you do, they are likely to assume the element is not present and not bother to try. Thus, if you’re going to have them on one window, then they need to be on all windows. Ideally, they should be on all database UIs.

Like I said, I fantasize.

Extending Selection For Databases

It’s hard to imagine physical labor easier than clicking a mouse. If I lived hundreds of years ago, I’d be breaking my back digging up potatoes or something from the hard cold ground. Today, all day, I’m in a comfortable office, leaning back in a padded chair, and just click, click, click, cl–Ack! My wrist! I can’t make the fourth one!  As easy as clicking is, all that clicking is often what limits the speed and ease of using modern desktop applications. A true master of the database would be able to do more than select a link or button.

There are a couple standard additions to simple selection that increase user power and decrease effort. There’s double-clicking, a form of expert activation, that allows a user to select data and perform an action in a single motion. And there’s multi-selection, where the user use the shift key or control key or dragging to select many objects for a single action, eliminating the need to complete that action for each object one at a time.

Multi-selection and double-clicking are two expert shortcuts with all four of characteristics above -non-interfering, consistent, powerful, and standard. Gestures on a touchscreen or touchpad provide opportunities for more. But for an office environment, the mouse has sufficient advantages over the touchscreen and touchpad that it’s not going away. I’ve long felt OS’s app should support a single computer having two mice, which would allow gestures, but what else could we do with just the traditional mouse and keyboard? Specifically, for the UIs for database applications, how can we extend expert activation and multi-selection.

Expert Activation

Power Activation

“Power activation” is combining selection with an action. It’s what double-clicking typically does. For example, when a user double-clicks an icon on the desktop, the user is both selecting the icon and executing the default action for it (typically Open or Run). Similarly, when a user double-clicks a parameter control in certain dialog boxes (e.g., the Current Page radio button in MS Word’s Print dialog), the parameter value is selected and the dialog executes its command and closes.

Let’s have more of that kind of thing. Power activation can be used:

  • To apply default parameter values for a selected command.
  • To apply the default (e.g., most common) commands for selected parameter values.
  • As a short-cut for a sequence of actions commonly done together involving the command.
  • To indicate more global (broad-acting or extreme) parameters than normal (this is consistent with using double-click to select a whole word in a document or text box).
  • To apply  unusual parameters or commands frequently needed by expert users.

The problem is double-clicking can’t be done on every control. Menu items and buttons activate on a single click. You can’t consistently use double-click on them because often their activation means they disappear (e.g., the commit and cancel buttons in a dialog box, or a menu item in a context or pulldown menu). You can’t have these controls lingering around waiting to see if the user is going to double-click or else it’ll be confusing. They have to act immediately. For buttons and checkboxes, there’s another problem: I would not want to try to distinguish between double-clicking and two single clicks (e.g., of a user changing then re-setting a checkbox value). It just doesn’t work.

If they had asked me, I wouldn’t have gone with double-clicking for anything. It takes too long and it’s too hard for novices to master the timing. I would’ve one with chording -power activation would be by the user mashing down both mouse buttons at the same time -much easier and faster than double-clicking. It wouldn’t matter whether the left or right mouse button goes down first. Most actions, including those for activating menu items, links, and buttons, and those for setting check boxes and radio buttons setting, trigger when a mouse button is released, not pressed. For other actions, such as selecting an icon or opening a dropdown list, the system can interpret normally whichever button is pressed first (e.g., selecting data or opening a context menu), then follow it up with power activation when the second button comes down. Chording instead of double-clicking is more consistent with context menus since the right mouse button is already associated with the context menu. Chord clicking suggest “menu-select” as in select the default from the context menu. Double-click in contrast only suggests “select harder.” Chording can be used on pretty much any control, a final advantage over double-clicking.

But unfortunately, the first successful consumer GUI computer had mice with single-button mice, so we ended up with double-click. Too late to change it now. Or is it? We could start supporting chord-clicking in addition to double-clicking. It would do the same things that double click does, only faster and easier, and it would work when double-clicking can’t. It’s “double clicking” of a somewhat different sort, so it shouldn’t be too hard for users to learn that they are equivalent.

The alternative is to use a metakey with a single click for power activation where double-clicking won’t work. I’d recommend the control key for this (or command key for Apples). Menu items, command buttons, and checkboxes would all respond to Ctrl-click the way list boxes, radio buttons, and icons respond to double-click. This introduces an inconsistency, but its only an irregularity, and Ctrl is normally associated with more powerful, broad-acting, or extreme actions (e.g., Ctrl-arrow keys to move word by word in a document) or at least expert alternatives (e.g., Ctrl-select for multi-selection and accelerators), so at least it is consistent with that. I suppose we could have all three: double-clicking, Ctrl-clicking, and chord-clicking all doing the same thing.

Here’s what I’d expand power activation to do:

  • Menu items, toolbar controls, and command buttons: Executes associated command with the current or default parameters.
  • Commit Buttons in Dialogs: Executes the associated command and closes the dialog box.
  • Scrollbars arrow buttons: Jumps the user to the extreme end of the pane.
  • Scrollbar slider box: Jumps the scroll position to center on wherever the current cursor or selection is.
  • Spinner buttons: Increments contents by large round amounts (e.g., factors of 10).
  • Slider control (either end): Set control to the extreme values.

For menu items, toolbar controls, and buttons that open a dialog, power activation would generally be equivalent to selecting the commit button with current settings in the dialog. For example, power activation of a Find menu item would essentially be Find Again.

In addition to Find, this form of power activation would be useful for Replace, Sort (resorts the data after new data objects are created or copied), Print, New/Insert (creates a new data object with the same parameters as the last one created), and Query (re-executes the query populating a window, that is, refreshes it with the latest data). Such an expansion of power activation can take the place of various menu items, toolbar controls, and buttons allowing you to simplify the UI. Using power activation to trim down toolbars is particularly well-suited since we’re just replacing one expert element with another, and toolbars of many applications frankly could lose a little weight.

For the commit buttons of dialog boxes, power activation is only different from what the commit button ordinarily does for “multi-use” dialog boxes (like Find in many apps), where the commit button does not ordinarily close the dialog. This can save users the step of explicitly closing the dialog when the users know they only need to execute the command once (and it they’re wrong, they can always recover by power activating the command that opened the dialog box).

Having such power activation for commit buttons would also eliminate the need for a separate Apply button, which tends to cause confusion. Users unfamiliar with the standard for Apply buttons won’t be left wondering what Apply applies or how it is different from the commit button. Windows standards require the Apply button be placed to the right of the Cancel button, but arguably it makes more sense for it to be next to the commit button, since they are more similar. Power activation eliminates the Apply button, rendering the issue moot. Finally, there’s the problem of the Cancel button. Being labeled “Cancel” may make some users think it will revert whatever they Apply. Being labeled “Close” may make other users think they have a multi-use dialog box -that the commit button doesn’t close the dialog. With power activation, the dialog box is unambiguously a multi-use dialog with a Close button and a commit button that does not close the dialog (without power activation). With power activation, there is little reason all modeless dialogs can’t be multi-use, which allows the Close button to be a visual cue to distinguish modal from modeless dialogs, something lacking in current GUIs.

Power activation of the scrollbar buttons is much handier than dragging the slider box to either end. Power activation of the slider is helpful when the current selection or cursor position has scrolled out of view. After users scroll away to check something else, they can return to their original position quickly.

Alternative Activation

While power activation performs the default or more broadly-acting version of an action, alternative activation performs the opposite or complimentary version of an action. This would apply to menu items, toolbar controls, and command buttons. For example, if Find is set to Find Forward, alternative activation of the Find button does Find Backwards, saving the user from changing the Direction radio button or dropdown list. If Delete places focus on the next item in a list after deleting, then Alternative activation of Delete places focus on the previous item after deleting. That kind of thing.

Okay, I haven’t convinced myself how useful this really is. If it is useful, then I’d suggest Shift-clicking for alternative activation, to compliment Ctrl-clicking for power activation. “Shift” implies some alternative to the usual, much like using the shift keys to type alternative characters, while “Ctrl” usually implies greater scope or power, with the exception of their use with multi-selection; if I had set the standard, Shift-click would perform discrete selection while Ctrl-click would perform aggregate “range” selection, rather than the opposite behavior we have now. Eh, whatever.

Repeat Activation

This could mean a couple of things. One is to expand the application of auto-repeat selection, where pressing and holding the select mouse button for longer than about 600 ms causes the command to start repeating at about a rate of twice a second, analogous to pressing and holding down a keyboard key.

Auto-repeat already exists for scrollbar controls. I wonder if auto-repeat should also be supported for other controls such as spinners and commit buttons, toolbar controls, or menu items for certain commands where a user may need to repeat it multiple times (e.g., Find, Zoom In, Zoom Out, Delete, and Undo) The risk, of course, is that this will result in some users accidentally executing a command twice because they selected “too hard.”

Alternatively or additionally, repeat activation could mean a multi-activation capability. In this case, the user presses and holds down the mouse button, then types a number at the keyboard. A tooltip appears providing feedback on the number entered. The user can backspace to correct the number or type Esc to cancel. When the user releases the mouse button, the command is executed the number of times typed. The user can use this capability to do such things as Find the nth occurrence of an object or string, or Zoom in n steps, or Undo the last nth actions, or Paste an object n times, or Go To the nth object or page.

Historic Activation

With historic activation, the user can re-execute a command with previously used parameter values. For example, after Querying with one set of criteria, the user can easily re-Query with a previously used set of criteria. Now, I know what you’re thinking. You’re thinking, “That’s positively the most amazing expert shortcut I’ve ever heard of. But what incredibly clever, intuitive, yet non-cluttering means will users use to activate it?”

How the hell should I know?

Okay, I can think of a couple possibilities. For command dialog boxes, I’d like to see Back and Forward buttons in the title bar which allow users to page through previous parameter settings. However, I would also like to support historic activation from the menu bar and toolbar. Here are some possibilities, neither which are fully satisfactory:

  • Right-clicking the menu item or toolbar control opens a context menu listing the most recent and previous parameter values used (e.g., strings for Find) that the user can select. This would be nicely consistent with chord-clicking being used to execute the command using the current settings. However, I’m worried that some users have gotten use to selecting context menu items with right-click -will they be confused or frustrated by this now opening a context menu for the context menu item listing previous parameters?
  • Clicking a small symbol in the corner of the menu item or toolbar control opens a dialog or menu that lists previous parameter settings for selection. The symbol should be consistent with those used in the dialog title bar. This avoids confusion with right-clicking but adds visual clutter to menu items and toolbars, which isn’t in keeping with my ideal characteristics of expert elements.

Maybe something like this can’t be feasibly done from activation of a menu item or toolbar control. The problem is in communicating what historic parameter sets are available for selection. I suppose a user can simply remember the set they used one or two commands ago, but beyond that, you have to provide some sort of verification so the user knows what she or he is selecting. That’s bound to be text-intensive for complex commands -something that needs to be shown in a dialog box.

I suppose you could provide this capability with a dedicated feature, rather than through activation. You could have a Repeater menu item that opens a dialog box listing descriptions of all previous commands in the session from most recent to earliest. The user can select one or multiple commands to redo. While you’re at it, you can provide the user the ability to save, name, retrieve, and even edit selected commands, essentially making Repeater a macro feature, but without the user having to enter and exit Record  (avoiding a mode, consistent basic design principles, and also eliminating the need for users to plan ahead so they can concentrate on the task more). Of course Repeater would support Repeat Activation of any of the commands it lists.

This will take some research to figure out.

Multi-attribute Selection

So you and your sweetie are sitting at the computer, looking at your spreadsheet How Do I Love Thee (in 348 ways, if you’re counting). “Dearest,” says your sweetie, “I notice that the Depth, Breadth, and Height for Ways 32 through 78 should be the same as for Way 16. Do update it for me, then we shall suck face mightily.”

“Certainly, oh applet of my eye,” you reply, “Allow me to perform a simple copy and paste, then we shall snog with the most comprehensive scope.”

One problem: it’s not a spreadsheet. It’s a web-based front-end to a database, a page comprising a tabular layout of individual text boxes. You’ve 141 individual Pastes to perform. By the time you’re done, the smooching mood has passed.

I find it astonishing that in today’s database apps you can only select one attribute value at a time. Some attributes cannot be selected at all, if they’re represented by controls such as a checkbox, radio button, or graphic representation. Like a spreadsheet, database UI should support multi-attribute selection so that multiple attributes may be moved or copied between data objects, primary windows, and dialogs. We can support selecting and copying entire data objects by providing object controls, but sometimes users need only certain specific attributes.

Masters of the database deserve better.


I suggest that clicking just beneath the bottom edge of any representation of an attribute value constitute selecting the entire attribute. Typically for text attributes, like those represented in a text box or drop down list, users can today select the entire value by simply clicking in the control (by default all characters are selected), and I would want that convention to continue. Furthermore, attributes represented by controls like check boxes and radio buttons should be effectively in a selected state whenever they have focus. However, selecting by clicking below the bottom edge would be the one consistent method that works for all attribute representations, including many graphic depictions (e.g., clicking the bottom of a bar in a bar chart selects whatever attribute it represents). Maybe even clicking underneath an object control will select all attributes represented by the object control’s icon, as opposed to selecting the whole object, but that has the potential to cause confusion. Making the bottom edge area sensitive for attribute selection also works in all layouts: page, table, and graphical (an alternative like clicking on the attribute label wouldn’t work for table layouts).

Feedback on the hot spot can be provided by a sprite change, maybe something like this:

Upward arrow and horizontal bracket

Upward arrow and horizontal bracket

The idea is to convey that the entire attribute is involved. Consistent with my recommended characteristics for expert elements, it’s pretty subtle, not the kind of thing a user is likely to discover the first time they use an app, but unlikely to interfere with typical use. However, if a sprite change is too subtle, then you can always try highlighting the attribute in some way, but I seeing a lot of overuse of mouseover highlighting effects, causing users confuse selection with merely hovering.

The user can multi-select attributes by shift-clicking and Ctrl-clicking, leveraging knowledge from existing GUI conventions. She or he can also select one or more attributes by dragging a box around the controls for the attributes, not unlike multi-selecting objects by dragging around the object controls. The user can select attributes from multiple data objects, such as those shown in a table or graphic layout. The user can take the Height from one object and the Width from another, or even the Heights from two different objects. All selected attributes should appear highlighted, including those represented by controls like radio buttons and check boxes.


Once selected, you can support whatever actions you want on the attributes, such as clearing the attributes or setting all to default values. However, the primary advantage that comes with attribute multi-select is the capacity to copy the attributes. It may be useful to be able to move the attribute values too, setting the source values to Null after the move, but I expect users will usually want to copy.

Copying should be effected by the usual copy-and-paste menu items, toolbar controls, and accelerators, thereby staying consistent with existing conventions and user knowledge. Copying should also be effected by drag and drop of the selected attributes. If you also support moving, that would be through cut-and-paste and shift-dragging, or whatever conventions your particular operating system follows.

Like any copying using the clipboard, the destination should make its best attempt to accept in some form whatever is being copied to it. Simply refusing to copy should only happen as a last resort. If copying a date to a date attribute, then it should be accepted as a date data type. If copying a date to a numeric attribute, then the date may need to be converted to a number. If copying to a string attribute, a date is converted into a string using some reasonable format. Generally the conversion should reflect the underlying attribute value, not the representation, so that the meaning is preserved for the user. For example, copying a check box to a text field should paste “True” or “False,” not “Checked” or “Unchecked”; copying a red High Priority icon to a text field should paste “High,” not “Red.”

Copying in this manner should copy the attribute identity as well as the attribute value. When copying one or more attributes to a data object (e.g., by the user selecting the object control then Paste), the app should attempt to match up the source and destination attribute identities. If the user is copying a Start Date, then it goes in the Start Date field, not the End Date field. If there is no Start Date field, then maybe it should go in the first date field of any kind. It should be possible to copy to any attribute irrespective of the control used to represent it. Assuming the data type is compatible, the data is valid, and the user has permission to modify the destination attribute, it shouldn’t matter if the value is shown by a text box, drop down list, radio buttons, or check box.

When copying multiple attribute values of the same attribute to a single data object, the first source attribute is copied. If a single attribute value is copied to multiple objects, then each object gets that value for the corresponding attribute. If multiple attribute values are copied to multiple objects, then the values cycle over the objects.

For pasting and dragging-and-dropping outside the app, such as too another database or document, the copied attributes should include enough information to support pasting using standard and common data formats (e.g., as XML, common spreadsheet formats, rich text table cells, HTML table, CSV, and tab-delimited text). Similarly, the user should be able to copy multiple attributes from other apps to objects in the database if the attributes are represented in such standard or common data formats. Possibly copied graphic attributes should included information to support graphic pasting. For example, when copying an icon for an attribute to a image manipulation program perhaps it should paste the icon.  Pasting or drag-and-drop to a file manager should create a file that preserves all the information necessary to paste again in any of these formats, including pasting back into your app with no loss of information.

Naturally, copying multiple attributes should support repeat activation.


Multi-attribute selection and copying add substantial flexibility for experts, potentially eliminating the need for various separate functions, and thus simplifying the user interface. For example, as in the case for the How Do I Love Thee database, it provides a fast means to set multiple attributes of multiple objects to the same value. One drag-select and Ctrl-C to copy the values for Depth, Breadth, and Height from Way 16, then another drag select of the object controls for Ways 32 through 78 and Ctrl-V to paste these values. Now your user can go on to do better things than futz with your app, like smooching.

This eliminates the need for a separate multi-object multi-attribute Modify feature, which would likely require the user to learn a new dialog box. If there are no attributes with the right values to copy, then the user sets one object in the primary window to the correct values, using a UI that’s already familiar, then copy and pastes those values to any other objects. This would be useful for document-style apps as well as database apps. For example, a user could copy the format for some text by using multi-selection of the settings in the Format dialog box or detail pane, then paste that format to any other selected text. You now no longer need a Format Painter or Paste Special function.

There are also uses for Pasting multi-selected attributes into a dialog. Pasting into a Query dialog would allow the user to to display data objects that are the same as a particular one on various attributes. Pasting into a Find dialog would allow the user to select all objects that match a given one. Once these objects are selected, the user can perform any number of actions such as showing them in a separate window, essentially performing a sub-query, or removing them, essentially performing a DELETE query, or copying and pasting other values of the same or different attributes, essentially performing an UPDATE query.

Copying and pasting multiple attributes to other apps is essentially an Export function, so you may not need a separate UI for that. Users can paste attributes for analysis in a spreadsheet or display in a table for a report. Being able to copy and paste multiple attributes to and from files in the OS would allow users to complete complex updates involving changing multiple objects to multiple different attribute values. Users can save certain patterns of attribute values as a milestone to allow easy reversion if what they’re trying doesn’t work. Users would be able to email attribute values to each other, making it easy for one to help another (e.g., to properly configure records, system settings, or options).

These are the sorts of things that users have been able to do with the content in their document apps since the dawn of GUIs. It’s time we provide the same capabilities to users of database apps.

Summary Checklist

Problem: Providing elements for expert users of database applications.

Potential Solutions:

  • Provide expert activations
    • Use some combination of double-clicking, chord-clicking, or Ctrl-clicking.
    • Apply to command controls, such as menu items and command buttons, as well as attribute controls, and object controls (icons).
    • Include a means for power activation, repeat activation, historic activation, and possible alternative activation.
  • Provide multi-attribute selection
    • Allow user to select multiple attribute values by Ctrl-clicking, Shift-clicking, and drag-selection.
    • Support copying multiple attributes through the standard drag-and-drop Copy and Paste controls.
    • Include attribute identity with the attribute value so the values “know where to go” when copying to objects.
    • Support copying the attributes values to other applications and to create OS files.
  • In general, make your expert elements:
    • Not interfere with typical user operations.
    • Be consistent with non-expert idioms and elements.
    • Provide high power, where a single idiom has multiple uses.
    • Be standard through the application, product line, and beyond