Documents and Databases

The Scale of Content of Windows in Database Applications

What should a window in database application represent? Assuming your user interface is going to have an object-centered structure, you need to next decide what objects a single primary window will show. In document-type software such as word processors, image editing and drawing apps, presentation makers, and spreadsheets, the object is a single document, typically stored as a single file. So, another way to ask the question is, what is the equivalent of a document in a database application?

Herding cats (from EDS's commercial)

As a working example, let’s look at the UI for a billing database for your local cat herding service, which has the following data model: Each client has many cats. A team of professional cat herders (employees of the service) conduct cat drives on some subset of a client’s cats. Cat drives are billed to the client quarterly, monthly, or for each drive. Clients usually pay for the bills with a single payment, but some do it in installments. Let’s say the ER diagram looks like this:

Cat herding service ER diagram

For document applications the document has worked out well as the molar unit of interaction, corresponding to a level of detail that is convenient for the task. For most work, any other level of detail would make for an awkward UI. Imagine if only one paragraph or one line of text were shown in each window of a word processor. Nearly as bad would be if the window showed simultaneously the content of all the documents in a project or other major category. The proper intermediate scale works best.

Document = Database

For a database application, you may be tempted to represent the entire database as a single window. After all, often a database is a single file in small desktop installations, just as a document is a single file. This is also essentially how MS Outlook displays it data, if you want to consider Outlook a database. One way to do this for our cat herder’s billing system is as follows:

Whole database in one window as an ER diagram, with detail pane for content

Such a design makes it pretty handy for complex ad hoc querying (for more on this sort of approach, see Badre, A. N., Catarci, T., Massari, A., & Santucci, G. (1996). Comparative ease of use of a diagrammatic vs. An iconic query language. In J. Kennedy & P. Barclay (Eds) Interfaces to Databases (IDS-3): Proceedings of the 3rd International Workshop on Interfaces to Databases, Napier University, Edinburgh, 8-10 July.). However, generally databases do not lend themselves to easy visualization. Imagine how the above would look for a more complex database featuring dozens of tables. The user would probably need to filter this display, perhaps selecting an entity to be an entry point for the rest of diagram.

Alternatively, you could take your cue from Outlook and hard-code the entry points and force-fit the database structure to the tried-but-true hierarchy, as long as you can count on your users not freaking out over the same leaf (e.g., individual cats) appearing on more than one branch.

Whole database in one window as hierarchy, with detail pane for content.

Again, that’s fine if the user’s task is exploratory querying, but more often the user’s task in database applications is to view or manipulate individual records. With a good chunk of the display dedicated to showing the larger database, the records are not as prominent as they could be. That may not be apparent with the simple records in this example, but often database apps need to display a frighteningly large number of attributes per record.

Furthermore, getting to the records by navigating through the database by direct manipulation can get tedious. Consider what it takes to “query” in either example above. In the ER-type interface, the user must select multiple tables and relations to establish the JOIN for showing the cats for a particular drive. In the hierarchy-type interface, drilling down to view the cats for a particular drive means going five levels into the hierarchy of this simple example. When the task is record manipulation, time spent navigating is time getting ready to do work, not actually working.

Document = Record

So if the record is what the user really wants to work on, then maybe each primary window should represent a single record. This has a direct analogy to the physical world where a single paper form typically corresponds to a single database record.

One record in a window, with Open dialog box to search for and select record to show.

You may want to even apply a WYSIWYG strategy, and make the screen appear like the physical form you are emulating. I’ve seen some such implementations replicate the form right down to static boilerplate at the bottom and “continued on next form” buttons.

Such WYSIWYG record windows can be effective if the user is switching back and forth between the physical form and virtual form (e.g., when entering data off the form). However, there’s rarely a need to replicate everything about the paper form in the UI, including all its limitations. As Alan Cooper points out in About Face 2.0, such an implementation is nearly guaranteed to have worse usability than the paper that the computer is intended to replace. Don’t clutter the screen with boilerplate. Link to it in the on-line documentation if the user needs to refer to it; otherwise, eliminate it. Panes in windows can scroll to any length. There’s no reason for “continued on next form” in a properly normalized database.

There is also little sense in blindly replicating a paper form in a database UI when the paper forms will be eliminated once the database is rolled out for the user. UI designers are often looking for physical metaphors to represent in their UI, but the only advantage of such metaphors is the user can transfer the knowledge from the metaphor to using the UI. With the elimination of the old paper forms by the database system, the capacity for specific knowledge of the paper forms to transfer to the new UI will fade as the institutional memory of the paper forms fades. Emulating paper may provide a short-term benefit but with potentially serious long term costs of an electronic form making usability compromises due to the limitations of dead trees. A paper form, for example, has to work with whatever type of user handles it. An electronic form can be tailored for each type of user or even each task. Don’t mistake paper forms for being business objects. They are only representations of business objects, just as database records are representations. Business objects are the catpokes, the cat drives, the clients, not the records of these things, be they paper or electronic records. Even things that do in fact appear physically as paper are often only representations. A bill may be represented by a physical invoice, but that’s not the same as a bill. The bill will continue to exist even if the invoice is burned. If you want a metaphor for your database UI, don’t look at the paper. Look at the business operation itself.

As for the record being the equivalent to the document in general, this works great if the user only shows one record in the window at a time throughout the entire application. In this case, the user’s tasks involves extensive manipulation of single records, perhaps over hours per record, much like in document-type applications the user extensively manipulates a single file.

But sometimes the task requires the user to handle many records at once. The user may be entering the data of one record after another, cross-checking the previous record with the current to track what has already been entered. The user may be copying common attribute values between records. The user may be searching for a record based on complex or vague criteria, relying on visually appraising each entire record. The user may be comparing records against each other. For these sorts of tasks, multiple records should be displayed to the user at once in the same window, typically as a table.

The alternative of one record per window becomes excessively cumbersome. For example, to make comparisons, the user must open two separate windows (assuming the app even allows two windows of the same type showing different content at the same time), then the user must switch from window to window to make comparisons. Compare this to a tabular display of multiple records in one window in which the user opens one window and simply scans down a column of attribute values. In our cat herding example, you might find users working around the limitations of one record per primary window by them using the Open dialog box in the above illustration to accomplish these multi-record tasks. You might even find yourself adding functionality to that dialog box to better support such tasks, resulting in a dialog box far too complex for its original function while still unsuitable for the added functions (e.g., it remains a modal window).

You could simply allow for windows that display multiple records as a table, while form-like windows adhere to the document = record approach, but this can introduce inconsistencies in the UI. For example, for document = record windows, it is pretty clear that a command for duplicating a record should be under the File menu, just as Save As is under File in a document-type app. You may even choose to rename the File menu “Record” (or whatever term the users use). But what about multiple records displayed in a table? Is duplicating a single record under File to be consistent with single-record windows? Most likely most commands under File will act on all records displayed. For example, you probably want Save to post all changed records to the database, rather than requiring the user to save each record before moving on to the next. So now you have some commands under File acting on all records, and other commands acting only on the current record. Confusing. Will users think your duplicate command will copy all the records shown, or just the current record?

Alternatively, given that the duplicate command acts on a selected record among many, perhaps it should be handled by copying and pasting, sort like how Windows Explorer copies files. But now you got the same command under two different menus depending on the window. File or Edit, there’s no clear answer, which means some users might look under Edit, (perhaps influenced from their experiences with Windows Explorer), while others look under File.

The Middle Way, Grasshopper

To summarize, the database in its entirety is typically too big to be represented in a single window, analogous to representing the entire file system in a window when you want to work on only one spreadsheet. A record is often too small to justify its own window, analogous to representing a single row of a spreadsheet in a window. What you need is something in between a record and a database, something that makes for a convenient molar unit of interaction for a wide range of tasks. A database table? Now you’re getting warmer.

A single user’s tasks with a database can vary over a large range of scales. At one extreme, the user can be focused on a single record, perhaps studying a detailed inspection report which has all the complexity of a document several pages long. At the other extreme, the user may working on, not only multiple records, but multiple classes of records as well. For example, they might be working on a graphic depiction of a communications network, with dozens of amplifiers, multiplexers, interface units, and many other classes of objects. Most work is in between these two extremes. Ideally, our in-between molar unit of interaction should be best suited for the in-between but still compatible with the extremes so our UI can be consistent no matter what it is showing.

What all these scales have in common is that the user selected something to work on from a larger universe of objects. It might be a single inspection report. It might be all cat drives that have not yet been billed. It might be all elements of a network at a particular geographic location. It is the result of the query that users are working on in nearly all cases. That is your molar unit of interaction conveniently scaled to the user.

Document = Query Result

Designers for database UIs tend to include extensive querying abilities to capture whatever criterion the user might need to use. This can be seen in the examples given for Document = Database and Document = Record. While powerful query abilities may be used sometimes by someone, most database work does not involved exploratory complicated ad hoc queries. Work with databases tends to be relatively structured, and the actual selection of records used in practice tends to be much more limited for a given user or job. Most work is after the records have been selected, where the records to work on today are selected by very similar criteria as the records worked on yesterday. The records are those to be added to a known customer. They’re records whose status is “ready for final approval.” They’re records that need attention because they are for clients who have been inactive for over two months. They’re records for financial transactions for the last quarter. In these cases, a well-proportioned UI will be optimized for those limited but routine queries, while providing some sort of less convenient but powerful ad hoc querying tool for exceptions and explorations.

Putting aside such an advanced querying tool, we can now envision a document = query-result UI that is optimized for the most common queries. Like a document = record approach, and unlike a document = database approach, a given database app will generally have multiple primary windows. Each window is designed to display specific types of records and fields in order to support the tasks necessary on the records. In this sense, the SELECT and FROM clauses of a query are determined by the window the user chooses to open. For example, or Cat Herding billing app would have the following primary windows:

  • Client
  • Drives
  • Bills

(For our example, we assume the Catpoke table is updated automatically by a mechanized feed from Personnel’s system.) The Drives window, for example, supports the following tasks:

  • Create/update cat drives
  • Regenerate bills (corrected for details in the associated drives)
  • Report on unbilled services
  • Review cat drives associated with a bill or client

The user selects the window to open from a menu. Each window is capable of showing multiple records; each record is represented as a component of the query result, like a line or shape is a component of a drawing in a vector-based drawing application, the difference being that the records’ association with a query result only lasts until the user closes the window and the same record may be associated with two different query results simultaneously. The ability to display multiple records does not limit you to tabular displays of records. You can still display a record as a form that fills the entire window. You just have to provide appropriate graphic design and prominent controls to allow the user to page forward and backwards to the next and previous records respectively. In my experience, this does not cause confusion.

With records being components of the query result displayed in the window, each record needs to be selectable as a whole to allow record-specific actions such as drill-down, duplicate, and re-associate, the latter two may be accomplished with copy/cut and paste whether the window displays the records as a table or a stack of forms. This again is like the components in a document-type app such as a drawing application.

The records displayed in the window are determined by user-supplied criteria –the WHERE clause to the query. While it may be useful to provide users with the capability to create and save custom-made query criteria (perhaps even as local files), for most cases that is probably not the task to optimize the UI for. Instead, consider specifying the criteria to be equivalent to selecting a file by name for opening in a document-type application. That means our database app has an Open dialog box (perhaps backed up by toolbar controls) where the user may specify the criteria for a window. The criteria to support in the dialog box or toolbar are determined by the tasks the window is supposed to support. For example, for the Drives window shown below, the user has the option to display all the drives for a particular bill in order to correct and regenerate a bill, report on unbilled services (where the Bill Number is blank), and review drives associated with a bill.

One query result in a window, with task-based Open dialog box to select records

For data entry, a button in the Open dialog box or separate menu item on the menu bar displays an empty window, performing no query. With the Open dialog box optimized for the most common queries, this may actually be simpler and faster than opening a file in a document-type application by navigating through a directory hierarchy.

While this works best for multiple types of primary windows that each may show multiple records at a time, the document = query approach also scales nicely to simpler cases:

  • If the task frequently requires displaying all records, make “All” be an option in the Open dialog.
  • A window that only shows one record at a time is merely one in which the user queries by record identifier, making for a simpler Open Dialog that just lists all the identifiers, along with other distinguishing fields, and perhaps also provides some filtering tools –not unlike that seen in document-type apps.
  • For some windows, there may be a need for only one query. These windows can open showing this query result without going through the Open dialog box at all.
  • In some simple apps, there is only one type of primary window (one thinks of the object-centered design in the paperclip inventory example). For these, there’s no need for the user to select the window type from a menu. Instead, there’s only the Open menu item.
  • An app that has both a single window type that only shows single record at a time is essentially like a document-type app.

In the case for more complicated windows, a query may be used to limit the display to certain records, but this need not limit the display of the records associated with these principal records, including those of other classes that may also be shown in the same window. The Drives window above, for example, features detail panes for Cats and Catpokes, allowing all data from multiple drives to be entered in one window. In a communications network app, as another example, the user can pick a fiber hub from the Open dialog, and that hub along with all associated objects are displayed schematically. The same can be applied to the display of the database itself for those cases when complicated ad hoc querying is called for. In opening the database window, the user selects from the Open dialog the entity to serve as the entry point and boundaries on the other entities to show, in a sense querying the database schema. The result is shown as a partial ER diagram in the primary window. For the ultimate complexity of showing the entire database, if that suits your users’ task, that is just another option in the Open dialog.

Okay, But It Seems Hard…

You might be thinking that this makes for a complicated interface. Some of this is inevitable because database apps typically are inherently complex due to the abstract relations among objects. Objects in a document are for the most part simply related by concrete spatial proximity –this paragraph is next to that picture, for example (formulas in spreadsheet cells are an exception). Objects in a database in contrast are related by business rules and processes which the user may or may not know in detail.

Also, I will admit that the document = query-results concept can be tricky for users. You need to communicate to your users that they are working on a collection of records in a window, even if for a given instance, that collection current holds only one record. This is something best address through good graphic design of the window, providing visual cues to the collection aspect.

There’re multiple levels to the UI of such a database app that the user has to keep straight, which may complicate things. Each app has multiple types of windows. Each window can display multiple alternative queries. Each query has multiple records. Compare this to a document-type app, where there’s one type of window that, at least with SDI, shows one object (one file) at a time.

However, this is not a fair comparison because document apps have the support of the operating system UI that is already tailored to work with a document metaphor. The molar objects for a document-type app are part of a larger universe of objects that constitute the file system. A database app has it own universe of objects entirely separate from the file system. When you make a document-type app you can rely on the OS and the platform common dialog boxes to support navigating, managing, and selecting your objects. When you make a database app, you have to create the UI for that from scratch. To assess total complexity, you shouldn’t compare a database app to a single document app. You should compare universe to universe, database app to the entire file system and all document apps that use it. We see then that the levels are actually in parallel:

File System and Document Applications
Database Application
Documents of multiple types
Records from multiple classes/tables
Multiple apps, generally one for each document type.
Multiple primary windows, generally one for each important class
Document
Query Result
Components of a document (paragraphs, lists, images, etc.)
Records and their attributes

As caveat, while a database app is not necessarily inherently more complex than the file-system-document-apps conglomerate, the user is more likely to be familiar with the UI for the file system from earlier experience. When users start to use your database app, that may be your users’ first exposure to that universe. That makes it harder. However, I’m betting that by keeping your database apps UI levels recognizably parallel to file-system-apps UI levels, as shown in the table above, some experience with OS UIs may transfer to your database app.

Summary Checklist

Problem: Making consistent and scaleable primary windows for a database app that present the right molar unit of interaction.

Potential Solution (for object-centered UI structures):

  • Have several primary windows in your app; the user selects the window to display from a menu.
  • Each window is designed to display specific classes and attributes of objects to support specific tasks.
  • Each window contains a query result generally based on criteria provided by the user in an Open dialog box.

More herding cats  (from EDS's commercial)

Comments are closed.