Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
Searching Higher ASCII If you look at the storage options for a field in Manage Database, and you see that it is set to a particular language, you may not think much about what it can do for you. Try changing the language to Unicode. This will make FileMaker index include upper ASCII characters, so you can search for values without enclosing them in quotes. For example, you will be able to search for an email address by including the at sign (@).
Level: Intermediate Version: FileMaker 15 Category: General Tuesday, August 8, 2017
I've seen a lot of databases in my twenty plus year FileMaker career. I like to call them the good, the bad and the ugly. Some are so beautiful, I am instantly inspired by their brilliant interface and subtle programming. Others are ugly inside and out. Some trick you by looking good on the outside but are so badly programmed that I want to chastise the developer. These are the databases I don't like to work on but often adopt. I make the best of it, grumble under my breath (and, sometimes out loud) and fix them as best I can.
Throughout this article, we'll use the classic invoicing solution as our example.
Hobbyists posing as professional FileMaker developers are the very reason certification was created by FileMaker, Inc. While a rigorous certification test identifies the difference between amateur and professional, it won’t stamp out the problem of poorly designed FileMaker solutions. Experience, not memorization, is key to good database design. I've adopted many poorly written databases from certified developers... unfortunately.
Surely, you don’t want to be one of these database designers. That’s why you need to plan your solution thoroughly and read this entire article. When you get a new client or your boss asks you to design a new solution, don’t pick up the mouse. Shut your computer off and start the process of planning your solution. If you plan well, the development phase of your solution will flow more easily.
I like to compare the process of creating a database solution to the construction of a house. If you start by hiring a contractor, you probably won’t get the house of your dreams. The first step is to hire an architect. You tell the architect you want five bedrooms, three bathrooms, a man cave and a swimming pool and he designs a house based on your specifications. The architect then shares the plans with you so you can validate his design. Maybe you decide to change a few things. The architect takes your input and create new plans. The process can go back and forth until the plans are satisfactory.
Changes will be made as the house is being built. It’s a natural part of the process. However, you don’t want any major changes that will affect the structure. For example, you can easily change the type of cabinets in the kitchen but you will be hard pressed to change the foundation of the house once it has been poured. It’s exactly the same for a database. Get the structure correct in the planning stage. Nobody wants to discover a relational design issue halfway through a project that requires major surgery. Structural changes have a way of rippling through the entire design, forcing changes to calculations, layouts, scripts and just about every feature in FileMaker. That’s how important proper relational design is for the health of a database solution.
Requirements Document Building a house is the same process as designing a database solution. You don’t start building the solution off the top of your head. Your first step is to create a requirements document. This document will vary depending on the person and the project. Some people will create a simple one page outline either because the database is easy to design or the developer is very experienced. Others will spend weeks perfecting their requirements document. The key is that some type of document needs to be created to specify what the database will do.
A requirements document helps you to organize your thoughts. You don’t want to forget any crucial aspects of the solution because you thought you had it all figured out in your head. Even worse, you might have it figured it in your head and then forget halfway through the project some of the features you wanted to implement. I always have my clients create an outline of their project. Not only does it help me but it helps my client organize their thoughts. Even if your client is your boss at work, you need to organize your thoughts into a single document. Have meetings about the database with management. Draw pictures of the interface you are planning. Involve people in the process and then write down everything in a requirements document.
One thing developers often forget to do is interview employees who know the processes better than anyone else. Don’t just rely on management to know everything. Sometimes things work differently on the warehouse floor. Management is certainly driving the process of the database development but they are probably doing it for a different reason than staff. Management wants reports and statistics. Staff has to record data. These are two parts of a single equation you need to blend together. Every requirement document looks different. Here are some ideas of what to include.
The more complete your requirements document, the better chance for success. Imagine forgetting to tell your architect that you need a bathroom in your kitchen. Crazy I know but what if that is your need and you don’t design it into the architectural plans, it’s going to be tough adding it at the end. Careful and thorough planning is crucial to the successful outcome of your project.
While planning is very important to the success of your project, beware of over planning. I am a great believer in finishing anything I start but I avoid procrastination. At some point you just have to start a project. FileMaker is very forgiving. If you forget a field in your requirements document, FileMaker will let you add it at any point during the development cycle. If you forget an entire report, you may have to start the development cycle over again because the structure doesn’t support the report. In fact, reports are one of the main factors that shape the relational design of your solution. In short, over plan the table structure and ease back on the rest of the areas. Over planning costs your client money. Some portion of the process should remain organic because FileMaker is so easy to change. Take advantage of the forgiving nature of FileMaker but don’t rely on it.
There’s no real template for a requirements document. Every document is as unique as each person. You can’t really mess it up unless you forget an entire feature. Otherwise, the design of your document is mostly unimportant. Just make sure you include everything your database needs to accomplish which essentially boils down to the workflow of your business. And, don’t write it all in one sitting. Produce the requirements over a matter of days or weeks depending on the complexity. Get management or your client to sign off on the document when you are done and you are ready to go.
Entity-Relationship Diagram Part of the requirements document process is creating an ERD or Entity-Relationship diagram. An ERD is a representation of a business process in database terms. Think of it as the architectural drawings. It represents the structure or foundation of your database solution. The ERD is so important, I can’t imagine designing even the smallest project without one.
Relationships are the easiest and hardest thing to understand. They are based on a simple mathematical equation but so many people, including myself, struggle with them. What helped me understand them better was an insight from Rick Kalman, the Product Manager for FileMaker. He said relationships are just questions you are asking your data. The question is expressed in terms of a mathematical equation but is still just a simple question like, show me the records that equal this value. It’s really that simple.
An ERD is comprised of entities or tables and relationships between those entities. Some developers like to make lists of possible tables and then vet them. Think of it as a brain storming session where you write down everything you think could be a table. This process is the beginning of data modeling or translating a real-life scenario into a database. I usually skip right to creating an ERD because of my experience but let’s try it out with the example we’ll be using throughout this book, an invoicing solution.
I have purposely chosen a simple approach to diagramming an ERD. Just entities, relationships and cardinality (crow’s feet) are represented. More complex ERD models may express attributes, associations and other unnecessary elements, IMHO. The point of an ERD is to design the structure of your solution. Use the requirements document to list fields if you like but I find it an inconsequential exercise since FileMaker is so forgiving.
Before we start, let’s define an entity. An entity or table is a person, place, thing or event. What may help in understanding the definition of an entity is the definition of an attribute or field. Attributes describe a person, place, thing or event. For example, a collection of people would be a table with each record in that table holding the information describing a unique person. A field in the people table might be hair color, height or anything describing a person for which there is one choice. If a single record parent table can have more than one of the attributes then a child table is usually warranted.
It’s better to add more stuff to this list than less. Get everything down based on your requirements document, even if it might not be an entity. You might make some proposed entities into fields. The idea is to use the list when you create your ERD. The process of drawing the relationships and scrubbing the ERD diagram will help you determine which items in the list really are entities.
So which of these possible entities are actual tables and which are attributes, destined to be fields? The obvious winners are invoices, products and customers. These three tables clearly define records. You will need records for products, customers and invoices. Each is a person, place, thing or event. On the other hand, prices describe a product so price becomes a field in the product table.
Some of the possible entities depend on your requirements document. If you are tracking vendors then you will need a table for vendors, otherwise it is unnecessary. Phone numbers, email addresses and mailing addresses depend on how whether you want unlimited entries or just a couple for each. It really depends on how sophisticated your customer tracking section is going to be. More tables equals more complexity. If you don’t need unlimited phone numbers, just add one field for work phone and another for mobile phone. Don’t just blindly follow relational design rules.
Notes fall into the same category of sometimes you want a table and sometimes you don’t. The simplicity of a large scrollable field often works perfectly. The only problem with notes is they can get messy real quick with many users and many methods of entering data. You can help clean a notes field by offering a button that inserts a standard time stamp followed by a couple of returns but people have to click it. Sometimes it’s better to organize notes in a table with one field for date and time and another for the actual note. This also allows you to sort the portal by date or even a category field that determines importance. Creating a notes table also allows for a report by category.
To create a company table or not to create a company table, that is the question. I have done it both ways. According to relational design rules, you should always create a table for companies in order to organize your contacts by company. But it really depends on your data. If you are going to be dealing with a lot of corporate sales and different people at each company, a company table makes sense. If most of your sales are to individuals, a company table just gets in the way and makes it more difficult to enter orders.
The rest of the possible entities are for you to determine for your unique solution. The examples covered so far are exhaustive enough to help you understand the process of turning entities into tables. You can see why I like to skip this step in the process and go right to the ERD. I have been creating database solutions for over two decades and I naturally think in terms of relational design. It’s a waste of time for me to write down my proposed entities but it might not be a waste of time for you.
Before creating an ERD for the invoicing solution example in this article, let’s take a look at some simple examples to get your juices flowing. The first example is a solution tracking movies and actors. Ask yourself from the perspective of a single record in a table, whether the associated table can relate to just one one or more than one record. Then reverse the perspective and ask the same question. In this case, one actor can be in many movies and one movie can have many actors. The answers to these two questions tell you that you have a many-to-many relationship.
A many-to-many relationship is indicated by crows feet on either side of the relationship line. A one-to-many or many-to-one relationship is indicated by crows feet on only one side of the relationship line. A one-to-one relationship is rarely used in FileMaker but can be indicated with a straight line and no crows feet.
Let’s try another ERD example of artists and paintings. One artist can have many paintings and one painting can have one and only one artist. Is this always true?
What if you have a mural then the ERD would look like the following.
Not every artist database is the same. You need to question the structure even if you think it’s obvious. I have seen amateur developers assume so many times that I expect it is going to happen. One time at MacWorld when I was giving a presentation, someone commented that they wished their developer had been more inquisitive. If the developer had only asked, he would have realized the school he was designing a database for was a grade school and not a high school. Remember, this is not an isolated case. Ask even if you think you know the answer. I am often surprised at the answers I get and often prevent structural issues and a lot less headaches.
SIDE TIP: One-to-One relationships are rarely utilized in FileMaker Pro. They are so uncommon, it is difficult to even come up with a good example. While one-to-one relationships are common in other database products, you are better off just creating fields in FileMaker. FileMaker design choices are often different than other databases. In this case, blank fields do not cause any overhead to the system as with other database applications. In fact, just the opposite occurs in FileMaker where one-to-one relationships can make a solution overly complex.
There are endless numbers of simple ERD examples. The more examples you see, the better you will become at recognizing the necessary relationship type. Scroll through the examples below and verify the relationship type. The most common relationship type has been drawn. As practice, ask yourself if there are any exceptions to the common relationship type assigned.
The ERD for our invoicing solution can be expressed as a simple or complex diagram. For purposes of training, I am going to include only the most important tables at this point. During a normal development cycle, all or almost all of the tables would be identified to ensure a healthy structure. Instead, we will add tables onto the project as needed to keep the process as straightforward as possible. A complete ERD at this point would just be overwhelming and leave too many questions unanswered. Adding every single table at this point will just muddy the waters and complicate the learning process. The following ERD diagram shows the most important pieces of the equation.
The first relationship you need to notice is the many-to-many. While it is possible to create a many-to-many relationship with two tables using a multi-key, it doesn’t lend itself well to reporting. Multi-keys will be covered in other articles on this site in order to stay on task. For a structurally sound relationship that supports ad hoc reporting, a join table is required. A join table is just as it sounds. It joins the two outer tables with two one-to-many relationships. We’ll call our join table Line Items.
Each time a product is sold, a record is created in the line items table along with a unique identifier from the invoice and the product records. The identifier allows the invoices and products table to both see the same line item record, thus, joining the two tables together. Now that’s going to create a lot of records but not to worry. Databases are designed to handle lots of records. While you don’t want to make unnecessary records, records are the building blocks of ad hoc reporting since a database can easily rearrange records via finds and sorts.
Key Fields While key fields are not really part of the requirements document, I wish to prepare you for their inevitable use. Since tables and relationships are being discussed, it seems natural to discuss them now. Each table should have a primary key. A primary key that uniquely identifies each record in a table. Think of it as a social security or a driver’s license number. Primary keys are used in many ways but in terms of relationships, it is the value that keeps records from two different tables connected in a relationship. In the example of customers and invoices, the primary key identifying a customer is stored in a foreign key in the invoices table.
Think of key fields in the same way you would think of keys to a household. Only one key can gain entry to the front door but duplicates can be made and handed out to anyone who needs to gain access. The key hole is analogous to the primary key and the duplicates of the keys are the foreign keys. The next door neighbor’s house is like another record in the table, with it’s own primary key.
A record is created in the invoicing table each time a customer purchases products. Each invoice is associated with a customer by recording the primary key value from customers in the foreign key field in the invoices table. It’s not important to understand how the primary key is shared at this point, just that it will be easy to do with a popup menu. In fact, there are many ways to populate a foreign key as will be discovered in other articles on this site.
Primary key fields are typically assigned as auto-enter serial number values. Don’t be tempted to link tables based on data that is typed by a user. By divorcing the connections between tables from data entry and using a value generated by FileMaker, relationships are safe from any changes to data. In other words, don’t ever use a social security number as a primary key field. It certainly does uniquely identify an individual but what if the data entry person types it incorrectly. What happens if child records are related and six months later the data entry error is corrected. What happens to the related records? They are orphaned since the foreign key values are not automatically updated.
Serial numbers are not the only types of unique identifiers in FileMaker, just the most common. There is an internal identifier called a Record ID. This internal number is created automatically without any interaction from the developer. It was first revealed using the Get(RecordID) calculation function for use in web deployment. The only issue with this unique identifier is it can’t be changed. What happens if you need to import data from one copy of your solution to another? The Record ID might not be the same and you have no way of changing it. Serial numbers can be manipulated in many ways, giving them flexibility.
Another type of primary key is accessed through the Get(UUID) calculation function. UUID stands for Universally Unique Identifier. While serial numbers avoid duplication by producing sequential numbers, UUID is a random value that is guaranteed to be unique. UUID was introduced in FileMaker to make it easier to create distributed systems where users have no internet access. Distributed systems synchronize remote users data with a centralized server. Without UUID, unique identifiers would be be a nightmare to produce.
SIDE TIP: Unless you have remote users that cannot get cellular or WIFI access consistently, save yourself the hassle of synchronization. It is far easier to use serial numbers and live connections to a database solution. For example, UUID cannot be identified outside the context of the table while serial numbers can easily be preceded with letters to help identify the table from which it was spawned.
Scrubbing Once you have completed your ERD, you need to test it against your requirements document. This process is called scrubbing. The goal of scrubbing is to guarantee your ERD structure will support all the items outlined in your requirements document. This process is so fundamental, I can’t emphasize it enough. It is common to change your ERD dozens of times during this process. The goal is a structural design that will support the entire feature set including interface, reporting and future development.
So, break out your number two pencil and a lot of paper, this process is going to take a long time. It will be time well spent, especially for complex solutions of ten tables or more. The process is fairly simple. All you have to do is validate whether the items in your requirements document will work within the structure of your ERD. This can be done by creating a sample database and actually testing the feature or by simply running it through your head. In the beginning of your career, you may need to build more sample databases. As you get more experience, you won’t skip the scrubbing process, just streamline it by working it out mostly in your head.
How many times you scrub your ERD depends on the complexity of the project. A rule of thumb to know when you are done scrubbing is when your ERD stops changing. This doesn’t guarantee a flawless relational structure but it is a pretty good indicator. It’s the same process used in application development. Software Quality Assurance (SQA) employees keep track of bugs. When bugs continually decline over a period of time, the project is close to complete. Any flaws at this point will most likely be easy to correct in an update. It’s the major relational mistakes you want to avoid via the process of scrubbing.
Water Cooler Talk If you are talking to an SQL or Oracle developer around the cliché water cooler, they may toss around terms like normalization or normal form. These are fancy words that describe sound relational database design. FileMaker naturally prevents some relational mistakes and others must be learned. It is not vital to memorize these terms unless you want to impress your friends. Rather, I offer them here for you to imbibe their meaning, not memorize their definition. I can never remember which form is which off the top of my head but I can design a relationally sound solution. In other words, don’t expect a pop quiz from me asking you to define first normal form. Just understand the purpose of these forms and why they are important to relational design.
Normalization is the process of efficiently organizing data in a database. The idea is to divide large tables into smaller tables in order to reduce redundancy and duplicate data. This reduces the size of the database, increases efficiency and allows for easier updating of data across multiple record or tables. First normal form governs the shape of a record and warns against storing duplicative data in the same record. Some designers like to describe it as atomic values that cannot be divided. For example, you might be tempted to enter all the phone numbers for a single contact into a single field separated by returns or into multiple fields. Instead, you could create a child table to store phone numbers. Reducing records down to their most granular level is the key to good relational design. First normal form also excludes repeating fields which are essentially a legacy feature in FileMaker from the days when it wasn’t relational.
The reason for these precautions is to allow for ad hoc reporting. If you store the extracurricular activities for contacts using a field formatted as a check box, which stores the data as a return-separated list, it will be difficult to report on how many people like cooking or watching movies. Reporting in any database relies on organizing records. It is difficult to split up a record into different parts of a report. However, there are always exceptions to these rules. For instance, it is unlikely you will ever produce a report counting the number of phone numbers so you could consider making separate work phone, home phone and mobile phone fields in the customers table. Exceptions to relational rules are fine as long as you know the consequences.
Second normal form reduces redundant data by transferring it to another table. The classic example is city, state and postal codes in a contact manager table. By placing the data in a separate table, it is only stored once in the postal codes table but referred to many times in the contact table. It also makes it easier to enter the data. Just select the primary key associated with the postal code you want and the city, state and postal code automatically appear. In addition, if the name of a city changes, just change the entry in the postal codes table and it will be updated in every contact record. Of course, sometimes you don’t want live updates and will opt for duplicative data. FileMaker offers lookups and auto-enter calculations to take snapshots of relational data but let’s save this discussion for another time. In addition, you may not want to require users to select a primary key and opt to break the rules by using the postal code as the primary key so users can simply type in the postal code and the city and state are linked. Relational design is not just about following the rules, it is about making educated choices.
Third normal form dictates there be no transitive dependencies. It is in violation when one non-key field is a fact about another non-key field. For example, if you have a table storing employees along with their department and department location, this violates normal form because the location is a fact about the department in addition to being a fact about the employee. Therefore, the department data should be stored in a child table.
Fourth and fifth normal form do exist but are not practical for standard database design and will not be considered in this book. You can research these forms on the internet or the various book published if you are so inclined. As you can see, normalization is about avoiding flat databases. Break down your data to the atomic level and prevent data redundancy and duplication. Records are good. Fields are generally bad. To many amateur developers take the easy road out and just create a bunch of fields. I see it all the time: Field1, Field2, Field3, etc. This is considered denormalized or flat. Then again, sometimes you break the rules. Relational design is mathematical but is also an art form.
While you may not completely understand all the definitions offered here, you will become accustomed to the meaning through practice. The more databases you create, the better you will get at recognizing good relational design simply because you found you couldn’t create a report you needed. Don’t worry so much about the definitions and the water cooler talk. Aim to understand what defines an entity or table and the relational theory will fall in place naturally.
SIDE TIP: Normalization is the process of dividing larger tables into smaller tables in order to eliminate redundant or duplicate data. Terms like first normal form and second normal form are defined in books like “Database Design for Mere Mortals” by Michael Hernandez.
Be Good to your Client or Boss I've met so many amateur developers who don't create a requirements document or even an ERD. They just wing it. Don't skip the planning process. If you are already planning, make sure you scrub your ERD so you can perfect your structure. And, by all means, know how FileMaker works so you actually design your solution within it's abilities and strengths. So many developers design from the point of view of SQL or Access. It's FileMaker. It's a little quirky so get to know it.