Layouts (Database Design)

Recall that a database Table is place where we store one type of data. And that Relationships provide links between records. Lets use our kitchen metaphor again to consider database Layouts. This one is easy! A database Layout is like a separate floor plan drawn around each type of thing you do in the kitchen, such as cooking, serving, or cleaning up, showing the drawers, cupboards, and appliances needed for that task. It shows what you see and what you can do. A Layout gives you access to everything you need in one place. Layouts are where we do our work. And since we do a lot of work, a good database has lots of layouts!

This is one article, in a series (of four), about how databases really work behind the scenes. The goal of this series is to give you a working knowledge of databases and their design. With this new understanding you will be better able to choose quality software and even become a more efficient user. That is a big step toward building profits in your business! Each post in this occasional series will have “(Database Design)” in the title.  Database software, behind the scenes, is really made of four key components: Tables, Relationships, Layouts, and Scripts. This blogpost looks at Layouts.

In its simplest form, layouts show the records of a table, showing the fields from one record on your screen, or perhaps a series of records if you are in a ‘List’ view. But well-crafted layouts use relationships and portals to also show stuff from other tables. The goal is to provide everything you need in one place.

To understand layouts, we need to look at our day-by-day activities as, ‘Process Paths’. Think of them as a list of the individual steps needed to complete each task. Lets make a short list: Add new Customers, Schedule Work Orders, Set up  PreScheds (Repeating Schedules), Post Invoices, Apply Payments, and Make Deposits. We could easily list a dozen more. Each of these activities involves a series of steps along a specific ‘Process Path’ through the software. And this path has you landing on a separate layout for each step of the way. That allows us, now, to say what a layout really is…. A place where we do one type of work toward completing some larger activity. Layouts are like stepping stones. Lets look at the those layouts in PestaRoo. Any well-made software will be similar.

For our first example, lets look at Adding a Customer. In PestaRoo and most good software, we can get to ‘Choose Customer’ via a button in the universal button bar at the top of every screen. The first layout is “Choose Customer’ where we search to ensure that they are not already in the system. (We hate cleaning up duplicates!)  If not pre-existing, a ‘plus’ button creates a new blank record on the Customer Info layout where we can type in their critical information such as names, address, contact details, locations for treatments, and notes. So, for this Process Path we are using two layouts: Choose Customer and Customer Info. Two stepping stones for the process. Each carefully designed to optimize your efforts in accomplishing those tasks. (As an aside, note that these two layouts actually display data from ten or more tables!) Think back, again, to a good kitchen where everything we need is at hand. That is what a layout is.

For another example, lets look at the steps and layouts used to schedule a work order, perhaps from an incoming phone call. We first find their record on ‘Choose Customer’ (sound familiar?) and immediately click into their ‘Customer Info’ layout. And this is the same layout we used when creating a new customer. It shows, on one page, all the pertinent info for a single customer. In this ‘Process Path’, we are interested in the locations for our customer. These will probably be in a portal which is a window on the layout that displays a customer’s locations. In the location portal we click a ‘plus’ sign to create a WO. Now we land on Work Order Info, our third layout. Upon arrival, the Work Order (WO) will already be linked to the Customer and their treatment Location. All you do is select the date, time, service type, and technician. Each of these options are in single-click value lists. Fast, quick, and easy. Fast, quick, and easy. To help preclude scheduling conflicts,  another portal shows the jobs already scheduled for that technician on that date! Layouts are so powerful. You should now see that the creation of a WO involves three layouts as stepping stones. Choose Customer, Customer Info, and Work Order Info. On each layout your task is focused fast and easy, because you have all the information and tools at hand to complete the task. Typically, a WO can be fully scheduled in about 30 seconds, while you are also visiting with the customer about their grand kids or the crazy weather. Wow!

A last example: Making a payment from a check that comes in the mail. As you might guess, no matter where we are in the software, (Home Page? Customer Info? Invoice Info?) one click immediately takes us to Choose Customer, our first layout in the process path. Here we will quickly find the customer by any criteria we want, or even multiple criteria. (First letters of Last or First Names? City? Balance Due? Phone Number?) This is the key task that the Choose Customer layout is designed for: quickly finding the correct customer, in a heartbeat, no matter what data you have at hand. Never use software where they want you to memorize obscure codes or numbers. Choosing by name is much more powerful and fast, because this is the single thing the layout is optimized for. “Choosing” or ‘finding’ one customer out of perhaps 10,000 will taking, perhaps 1-3 seconds! Once you have found the correct customer for the check in your hand, click on the icon to ‘Create Payment’. You will land on your second layout where a new payment will have already been created for your customer. You’ll be able to see, instantly all the open invoices for the customer, and with a couple quick clicks, make and post the payment. After posting, you’ll automatically land back on Choose Customer again, ready to do your next payment. The key point here is that we’ll use two layouts as stepping stones to complete the payment. And both of these layouts are carefully crafted to allow you to quickly and accurately do what needs to be done at that instant.

How many layouts should a solution have? As an illustration, PestaRoo has about 1,000. Yet only about 50 show in the visible listing of layouts. Why? The only layouts you need to see are those used to start some task. As we work our way through the task, then additional needed layouts should appear or become available. Most layouts will be hidden all of the time, appearing only when needed. Software’s depth grows as your skills grow, but good software should hide its design complexity while you are learning.

The design of database layouts, basically involves the breaking down each core complex function into a set of stepping stones (Layouts) where we complete each piece of the larger process. Layouts, in quality software, are carefully designed to allow you to quickly complete a single task. A large part of a database’s value, lies in its layouts, because that is where you do your work. The ability of a database to boost your profits is largely proportional to the number of well-designed layouts provided, and how well they are crafted, as stepping stones, along the myriad process paths that define our industry.