November 26, 2015
The Importance of Tables (Database Design)
Database tables are like drawers in a kitchen. If well laid out, you will have a place for everything and those drawers will be placed to make cooking and cleaning up a pleasure. Typically, each drawer or cupboard contains a single type of item: silverware, pots and pans, plates, glasses etc. Databases are organized the same way. Each table contains only a single type of info. And great databases, like kitchens, have plenty of tables (‘drawers’) that are carefully designed for efficient workflow and a great user experience.
In business today, there are several main types of software: word processing, spreadsheets, browsers, and databases. Although all of these are typically found on most computers, it is databases that we care about here. This is because behind every successful business there is a database, typically custom built for their industry. Every business. Databases manage inventory, accounts receivable, customer relationships, payables, and every other digital process. Yet most people know very little about these critical tools that have such a huge impact on your success.
This is one article in a series (of four) about how databases really work behind the scenes. The goal is to give you a working knowledge of databases and their design. With this new understanding you will be better able to recognize quality software and even become a more efficient user. That is a big step toward being successful in business! Each post in this occasional series will have “(Database Design)” in their title. Since database software is really composed of four key components, I will give of them a separate post: Tables, Relationships, Layouts, and Scripts. Lets start with ‘Tables’.
A database ‘table’ is a place for storing a single type of information. For table examples, think of Customers, Phone Numbers, Locations, Work Orders, or Payments. The key here is that each table has only one type of data.
Lets go back to the kitchen metaphor. You could have just one giant drawer and dump everything into it. But what a mess! You would have a tough time finding anything you need when setting the table or when cooking! Conversely, you could have one drawer for each type of thing. Hmmmm… that is much better. It is has been said by many a cook, that you can’t have too many drawers or cupboards in a kitchen. Well, the same is true with databases. In general, you can have any number of tables. Up to a point, more is better. PestaRoo, for example has about 110 different tables.
But you can have too few. Indeed, having too few tables is a very common mistake of many software designers. It is true, that the more tables a solution has, the more work and design it takes to build it. But, typically its power and ease of use, goes up with the table count. Too few tables indicates incomplete or poorly developed software, a real limiting factor. One more key point, for the definition of a table… you should be able to say, with certainty, what constitutes a single record in each table. Memorize that statement!
Here is a simple example where you might have too few tables. Any database solution in the pest control industry will have a Customers table. Each record is exactly one customer. That is easy. It will include First and Last Names, Address, and such details. But some Customer tables will also include a field for the phone number, and perhaps an email. But this is poor structural design, since a customer may have multiple phone number and multiple emails. So, should we have fields for Phone1, Phone2, Phone3, Email1 and Email2? No! If you wanted to search for a specific phone number, you would have to look in three or more different places. A better approach would be to have a ‘Contact Details’ table to hold all the phone numbers and email addresses for everyone in the entire database. Then, a window (called a ‘portal’) in Customers would look into ‘Contact Details’, but only display the individual phone numbers and emails that belong to that single customer. This allows a customer to have any number of contact details, yet all show up in one place. Very nice! And this new table, Contact Details, will also be able to support any number of other parallel tables, like Suppliers, Agents, Staff or any other type of record that may have phone numbers and emails. So the variable number of phone numbers and emails can appear wherever they are needed. The Customer table has one record for each customer, while the Contact Details table has one record for each phone number or email for that customer (or supplier, agents, staff, etc.).
The reason each industry has its own databases, is because each industry needs different tables. In the early days of databases, programs like Quickbooks thrived. The thinking was that one database would work for any business. But it quickly became apparent that trying to force a company’s industry-specific needs into a generic database was just not going to work. In almost every industry, new companies often try to use such generic software before realizing the great value of industry-specific software. Lets look at some specific tables for our industry that you should be able to find in quality software, but will never find in Quickbooks:
Locations: All the places you provide treatments. One customer may have multiple locations
Work Orders: One record for each appointment ready to schedule or already on the schedule book.
Pests: Critters that we control.
Treatment Types: How we provide control
Products: Names, EPA numbers, and Application rates of the chemicals, baits, and traps we use.
Most of these critical tables won’t be found in generic “One size fits all” software. And we could list 50 more industry specific areas. This is why each industry has its own carefully tailored software.
For one last time, lets go back to our kitchen metaphor. An a addition to having abundant drawers and cupboards, a great kitchen also has them placed next to where they are needed the most. Placement matters in kitchens. We want the silverware and plates to be close to the dining table or counter. We want the pots and pans close to the stove for cooking. And we want the dishwasher close to the sink for easy loading, and also close to the dishes cupboards for unloading. Unfortunately, in the world of kitchens, each drawer or cupboard can only be in one place. (Wouldn’t it be fun to have a silverware drawer in four different places with the exact same contents? Where putting a spoon in one instantly added the same spoon it to each of them?!) Database tables can do this exact thing. They can appear in an unlimited number of places at the same time! Wow! Changing a record in one place, instantly changes that record everywhere! Magic! So, in essence a table is where the data is actually stored, while having the ability to appear wherever it is needed. A table can literally appear in any number of places. Layouts provides a way to show the data from tables. Layouts can even show clusters of different tables, all in one place. So, the table is where the data actually lives. But layouts allow you to view and edit the contents of various tables wherever they are needed. And those places can be as many as you can imagine. We will talk of ‘Layouts’ in another post, but for now just realize that layouts let us magically show table data anywhere we want.
How do these records get hooked together? That is also for another post! (Watch for ‘Links and Relationships’) But for now, lets take a peek beneath the surface of a single invoice. It will be hooked to a single customer in the customer table, a single location, one or more pests to control, one or more treatments, and one technician. Then when once it is paid, it will be connected to one or more payments, one or more deposits, and in good software, it will also be linked to a series statistical tables providing historical compilations by tech, city, service, months, years, and dates. So, one invoice is really a intersection between at least a dozen (or more) specific records each from different tables! Everything about the invoice that you need is in one place! That is why great databases have great table structures. It make thing so easy for you, the user.
A well-designed database has a specific table for each type of data to be managed, and then displays the appropriate content from those tables in clusters relating to a single process that the user needs to manage. That clustering of associated records of information, would never be possible without tables. But with tables, is a great way to boost productivity and profits. You can literally have access to everything you need at that moment. This is why databases are driving the evolution of the modern office.