Database Application Design

1. Database Application Design “From principles is derived probability, but truth or certainty is obtained only from facts.” Nathaniel Hawthorne

2. Class Outline • What are the initial steps necessary before designing a database? • How can these steps be completed? • What are the common errors that are made in the design of a database? • How is the quality of data affected by each type of error? • Develop experience in critically evaluating application designs with examples

3. Functions of a Database Application 1. Process views of data: create, read, update, delete • forms for maintaining data on a record by record basis • query to append, update, delete groups of records 2. Format or materialize views • create view with query or SQL statement • format view with report or form 3. Enforce constraints • DBMS provides some options: e.g., MS Access provides referential integrity and table properties such as validation rules, input mask, lookup lists, default values • other constraints must be programmed with macros or VBA 4. Security and Control (Lecture 10) 5. Execute business logic (Macros in Lecture 9, VBA)

4. forms reports queries help mouse cursors icons images text Components of Windows Interface • toolbars • command buttons • accelerator keys • shortcut keys • scroll bars • menus • context-sensitive help • general help

5. Interface Design Guidelines • Design of user interface a critical success factor • consistency • user-centred • compliant with established platform standards • Create short-cut (accelerator) keys • Define tab order • Supply screen tips • Create custom menus • Create a startup form • Set startup properties See O’Brien’s Microsoft Access Developer’s Handbook ( Chapter 5 - User Interface and Application Design) for a detailed description of components, associated properties, and usage.

6. General Appearance of User Interface • consistency- user interface easier to understand and use; interface elements should look as if they belong together • arrangement and flow • alignment • grouping • emphasis - draw attention to interface elements to be seen first, using focus, location, grouping, hierarchy, enabling/disabling, size, color, or font attributes; visually indicate what user should do next • visual clues - use like sizing and spacing to indicate user interface elements are similar and different sizing and spacing to indicate user interface elements are different • white space • use company or product names and logos sparingly Adapted from: McKay, Everett N. (1999). Developing User Interfaces for Microsoft Windows. Redmond, Washington: Microsoft Press, Chapter 4.

7. Form Layout Acceptable: Better:

8. Visual Affordance • Windows users come to expect various behaviour from an application (e.g., items with a white background and a flashing vertical bar can be edited) • Design your application consistently with expected conventions

9. Combo Box Format Properties

10. User’s Interaction with the Application • keyboard access to all features • mouse access to all features • actions with significant consequences require explicit selection from the user (user needs to be fully aware they are about to do something dangerous or destructive) • feedback for all time-consuming operations • visually indicate modes • single-clicking and double-clicking are consistent • right mouse button only for context menus • do not use the middle mouse button (user assigned behaviour) • assign shortcut keys consistently - according to convention • make shortcut keys redundant • avoid horizontal scroll bars

11. The examples that follow were taken from the Hall of Shame. The examples are all from ‘real’ applications, some designed by leading software companies.

12. Checkboxes are for Multiple Selections Option buttons are used to allow the user to make a mutually exclusive selection. Checkboxes are used to allow the user to make multiple selections. The very appearance of checkboxes tells the user that he or she can select several items. Never use checkboxes when you want the user to make a mutually exclusive choice.

13. More Checkboxes In the Font dialog box for Word 97 the user can set text attributes using an array of checkboxes; no problem there. However, there a 4 pairs of mutually exclusive options listed: strikethrough/double strikethrough, subscript/superscript, emboss/engrave, and all caps/small caps. the controls look like checkboxes but behave like option buttons. Obviously, using option buttons would have spoiled the aesthetics of the control group.

14. Radio buttons are for Mutually Exclusive Options The dialog is intended to allow the user to specify the fields on which to sort the data in a report. As can be inferred from the image (and not without considerable difficulty), the user can specify sorting on three fields. Actually, the user must specify the sorting on three fields, since there is no way to indicate that you want to sort on any less. As indicated in the image, sorting the results by Part ID, then by Part ID, and then by Part ID would be just hunky-dory in the mind of the developer.

15. Too many options for radio buttons The programmer of this corporate application had apparently not yet read the chapter on the use of drop-down controls. Makes you wonder how the user is asked to specify his or her country. Designs like this often result when the programmer not only lacks knowledge of proper interface design, but also lacks experience with the Windows environment. Such designs are inefficient for the user, and result in larger, more sluggish applications that waste system memory and resources.

16. Menus vs. Toolbar Standards Use drop-down options in menu bars. Icons on tool bars are best used to carry out an immediate action. Limit surprises for the user who is accustomed to design standards.

17. Drop-down boxes Displaying only 3 items in a listbox is just plain stupid. This dialog box is provided by the Associate function in Microsoft's File Manager, as is displayed when the user wishes to associate a particular file type with an application. In this example, there are 133 entries in the list; clicking the page portion of the scrollbar will display the files 2 at a time. We are at a loss to explain why the developer didn't use more of the available space on the form to make the list larger, or why he or she didn't just make the form larger. The end result of the developer's lack of thought is a control that is essentially useless for the task at hand.

18. Limit Drop-down box entries This type of control is seen all too often in corporate applica-tions: drop-down controls and lists that contain thousands of entries. Programmers who mistakenly use it generally get a hint that it might be inappropriate when they find out that it takes an extraordinarily long time to load the form The excuse for such unwieldy controls is often a misguided interpretation of the almighty call to arms, "We must ensure data integrity." The programmers want to make sure that the user specifies a valid entry; in their view, the best way to do this is to force the user to select from a list. That would be fine if you had, say 20, 60, or maybe even 100 items in the list. Beyond that number, the fact that the user can only scroll a handful of items at a time causes the control to become unwieldy. All data can be organized in some meaningful way that will allow the user to more rapidly access the specific information he or she is interested in. Files are organized into folders or directories for example. Employees are often categorized by department, job title, or salary grade. Designing the interface to exploit the appropriate organization will allow the user to more rapidly locate the desired information, while at the same time, "ensuring data integrity."

19. Is this really necessary? Based on concern over the typing ability of some of the 400,000 corporate users, the project manager of this particular application insisted that drop-down controls be used to collect information wherever possible. Visual Labels requires right-clicks to perform necessary functions. It is the only way to access necessary functions. Right-click context menus are a useful addition to interfaces. They are intended to be an alternative means of accessing functions; as shortcuts, primarily for advanced users. They should never be required, and should always be used in conjunction with conventional menus. Otherwise, new users might never realize the functions are available, and they will have no keyboard access to the functions once they find them. Whenever a new user is observed using a program that requires right-mouse clicks, they will inevitably ask, "How do I ...", and when instructed, will invariably respond with "Gee ... that's odd."

20. Creativity? Unlike life in high school, in graphical user interfaces, looking like everyone else is a good thing. When applications look and act alike, users learn them much more quickly, since they can transfer their knowledge of one application to the others. When your application looks and behaves unlike other applications, your users will require more time to learn how to use your application. Novelty for novelty's sake is never a good thing. One only need to recall the experience of sitting in a rental car, trying to find where the controls are located in this particular car. (The auto industry has gotten much better at this; in the seventies you never knew where a control might be located). The use of diamond-shaped rather than round option buttons only increases the likelihood that some users will be confused. Even more serious, the option buttons act like command buttons; click on one and the program will jump to another dialog. Just to add to the confusion, the application intermixes the standard and custom option buttons, both in terms of appearance and behavior. Don't strive to be different. Strive to be good.

21. Too ‘busy’ We shouldn't have to say this, but when you have to devote more than, say, 25% of your window's real estate to command buttons, you probably have too many buttons. You should never have to duplicate a command button more than once on the same window, never mind 9 times, and certainly not 15 times.

22. Scrollbars This application that shows such complete disregard for established design principles and industry standards that you have to wonder if the developers have ever used a graphical user interface. This image illustrates a central design idiom used in PeopleSoft applications. A proper critique of PeopleSoft applications would require megabytes of storage, so for the time being we will simply focus on their novel use of scrollbars. Scrollbars in PeopleSoft applications are used as database navigation controls. In the illustration, clicking on the outer scrollbar would display the next category of awards, and clicking on the inner scroll bar would display the next award in the current category. Keep in mind, the frames do not scroll up and down; the information in each is simply replaced. PeopleSoft often nests scrollbars 3 or 4 deep, such that the user becomes absolutely dumbfounded not only as to how the information is arranged, but also as to how to navigate within a window.

23. Make it fit! Scrollable forms are a sure sign of inexperience in graphical user interface design. These often result from the conversion of legacy applications to the Windows platform, or in the development of applications based on paper forms. Scrolling forms hide important information from the user, hide navigational aids and controls, and require more user inputs to get to the desired information.

24. Summary • The look and feel of the application can be the single most important determinant of its value to the user. Strive to design highly usable interfaces that are easily learned, minimize user frustration and maximize user productivity.We begin with the system requirements and a few basic rules: • Software must assist the user perform a task, not become a task in itself • Software must not make the user feel stupid • Software must not make the computer appear to be stupid

25. Demonstrations in Access 97 • combo box in forms and associated properties • option group in forms and associated properties • concatenating fields in a query (or form or report) • control source and record source properties (most important properties - in my opinion) • embedding SQL statements in the record source and control source • switchboard and startup options • creating toolbars and connecting to specific forms • quick look at other uses for forms such as for collecting query criteria