Recall that a database Table is place where we store one type of data. To revisit the metaphor of a kitchen, it is a single drawer or cupboard. But it is a special ‘drawer’ that only shows the data we want to see at that time. This is almost magic. How can this work? It works because of Relationships. In this post, we will show how we can have the right data appear at the right time and place, but nowhere else. This is the world of Relationships.
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 Relationships.
Since each table contains only a single type of data, we next need some way to show just records out of that table, in just the place we choose. For that, we use Layouts. A layout shows just the fields from one record on your screen, or a series of records if you are in a ‘List’ view. We’ll talk more about Layouts and their abilities in another post. For now lets just note that a layout allows us to look at the fields from one record from a table.
But the power of databases comes from the ability to show both native data AND related data on that same ‘Info’ layout, at one time. Best of all, we never need to re-enter the data in the new place to make it show up. To say this in another fashion, data typed in only one place can flow onto a gazillon other records without retyping it! We will label each record to indicate ‘who’ it belongs to and where it should additionally appear. To let data flow, from place to place, we use special fields called ‘keys’. Lets look closer at these.
Every single table will always have one special field, called the ‘Primary Key’. It automatically assigns a serial ID number to each new record added to the table. Since these values must always be unique, they are assigned by the software, not the user. They can never be changed. This term, ‘Primary Key’, is database-speak. Most database developers rename these special fields to be more readable. For example, in the Customers table this field is usually called ‘Customer ID’, in Locations it is ‘Location ID’, and in the Invoices it is called ‘Invoice ID’. Regardless of what name these special fields are given, they are still ‘Primary Keys’ telling the software where that record belongs, and what serial number it has.
Now, here is where the magic begins. We also have ‘Foreign Keys’. They are found everywhere in the database where we might want to be able see the data from some primary key record. These Foreign Keys look like Primary Keys, but are not auto-entered with serial numbers and can be changed at will. Foreign keys are filled with the value of some other record’s Primary Key that we want them to be related to. Cool! This ‘filling-in’ of the foreign key occurs (invisibly) by some action of the user. They point back to one primary key record as if to say, “I belong to that record whose Primary Key matches my Foreign Key.”
Lets look at a simple example. Take a single Location that belongs to John Doe. Perhaps this is a rental property that you treat. On the Location record you will see all of the location specific fields, like address, city, directions, type of property, and such things. You will also see the name, ‘John Doe’, since he owns the property. Beside the name, you will also usually be be able to see the foreign key that points back to John Doe’s record. In this case, lets say its value is ‘1234’. In PestaRoo, we display the customer name in blue to indicate that the data is flowing in from somewhere else. And, by having the foreign key right on the layout, you can change it if the property is sold to someone else.
Here is another example. Imagine we are on a customers record for ‘John Doe’, whose Customer ID is ‘1234’. Now, we want to type in a new phone number for John. On that layout, there will be a Contact Details portal. This portal, like a window, will allow you to see, in a scrollable list, all the phone numbers and emails for John. Above or below that portal will be a “+” sign to allow you to create a new record. Or, in many cases, a blank line will already be waiting for you, in which to enter a new phone number. Whether it is a waiting blank line, or a “+” button that makes a blank line, they operate the same way. As soon as you start typing in the phone number, a foreign key field in Contact Details is immediately set to John Doe’s ‘1234’. Instantly. So if you look below the surface in the database you will find that Contact Details has a field called Customer ID. This is that foreign key. Its value determines to whom the record belongs. Lets say John Doe has four phone numbers or emails. So, again looking below the surface, you would find exactly four ‘Customer ID’ fields populated with a value of ‘1234’. And that is how the portal ‘knows’ what records to display! That is to say, the portal looks through a relationship linking the foreign keys to the matching primary key. Relationships in databases and people can be magic and powerful.
So what is a relationship? Merely a set of links between tables. A relationship may actually have multiple linking fields that must match. Although most relationships have only one or two links, sometimes you could have a relationship with five or even six linking fields that must match. Lets look at a more normal example with two linking fields. Perhaps you want to email a statement to a customer. And, you have a button that says ‘Email Statement’. But some customers might not have an email address. So, in that situation, we would like the button text to turn to a very light gray, indicating that for that customer, the function is not available. How could that work? You would have a relationship with two links: ‘Customer ID’ = ‘Customer ID’, and ‘Type’ = ’email’. (As an aside, ‘Type’ could be a field the user had manually set to ’email’ or a calculation that looks for the ‘@’ symbol.) Instead of showing ALL phone and email Contact Detail records, this relationship would only show those for one customer and that are specifically emails! So the calculation that controls the text color on the button, would merely look thru this new relationship and check to see if any records exist that match both of the two criteria. If ‘yes’, the button text is black. If ‘no’, the button text is gray. Very neat! If you explore quality software you will find lots of buttons that have built-in intelligence.
Now another example, re-imagine an invoice. Of course, it has its own primary key, called ‘Invoice ID’. But just as important, each record also has a series of foreign keys that it is associated with: Customer ID, Location ID, Technician ID, and others. In each case these foreign IDs allow the invoice to display info from the customer, location, and technician. Taking a peek beneath the surface, we see an invoice ‘hooked’ to a single customer, a single location, one or more pests to control, one or more treatments, and one technician. Then, when it is paid, it will be connected to one or more payments, one or more deposits, and in good software, to a series of statistical tables providing historical compilations by tech, city, service, months, years, and dates. Wow! So one invoice is really a displayed intersection between at least a dozen (or more) records, each in different tables, looking ‘thru’ relationships. In illustration, for fun, PestaRoo has over a thousand relationships. And each one allows us to see related information from elsewhere on the current record. Relationship are really amazing in their power.
Relationships between multiple tables, properly built, suggest a potential ideal for a database: That each record in each table would literally only exist in one place in the entire database. So, the eight characters of, “John Doe”, would actually be typed only once in the entire database, even if he had hundreds of invoices and payments. Or, that the name name of each of the field technicians would literally be typed exactly and only once in the database, even though he or she may have a thousand of work orders! How can this be? Because of Relationships his name data would flow to the thousand related records.
The job of a database designer is to create this beautiful structure of tables, where data entered once can literally be used and re-used everywhere it is needed! You should never have to type anything twice. If he/she succeeds, you will never have to type any customers name more than once…ever!
Database that have abundant and carefully designed Relationships, built with this principal in mind, are the fastest, easiest to use, and simply the most powerful. They reduce your work and build your profits.