Database Design Basics: Making Data Entry Easier

This article is about making data entry easy and it’s for people who don’t have a computer or maths background. I studied law, literature and communications before taking up computing and it took time to let go of words and to use numbers. Not that I’ve given up on words entirely but I’ve learnt that computers prefer numbers. Learning to use boolean options can improve your database in important ways: ease of input, data integrity,  data storage and data re-use.

When we are performing any sort of data entry on the computer we discover that its easy to make mistakes. Words are comparatively long and hard to spell. On the other hand, 0 (zero) and 1 (one) are easy to type and you know immediately if it is correct.

Ease of Input

Plain language and simple input options make it easy for your users to get the input right. Good design choices can reduce their workload and provide safeguards to prevent simple errors and accidents. When you simplify a question to a Yes/No option the amount of thinking required is reduced. In many cases, you can offer one option so your user only needs to touch the form to answer in the affirmative or the negative. When you are able to do this consider entering the opposite value as the default.

Data Integrity

Ensure that the data being entered is correct. Even the best users make mistakes so you should take it for granted to errors will occur. When you know that there are only two possible values it is very easy to test for mistakes and correct them.

Data Storage

Storing a 1 or a 0 for Yes/True or No/False reduces the amount of storage that is required. The smaller the size of the data set, the less resources are required, the faster all your operations will run.

Database Design Basics: Data Re-use

If you are collecting data that affects numbers then it makes sense that the answers to questions are in numbers too. Even if you are not doing math, once you have data you’ll want to use it in some way. It is at this point, that you start to re-use your data for research, analysis or reporting, that you will discover why well-formed and accurate data is so highly regarded.

The simplest request will be to find a set of records. If you are storing data as a positive/negative statement (see Common Mistakes), you immediately have a problem. While you are searching manually the field format supports the search, however, when you start scripting to run reports you’ll see how the difficulties arise. To create an unambiguous search request it is necessary to search for a unique value. The only unique word is “not” in “Does not have a driver’s licence.” There are no words in “Does have a driver’s licence” that are not in “Does not have a driver’s licence” so it would be necessary to use the whole string to perform the search or you could search for “not” and then flip the found set using the Omit Records step. In comparison, finding a true or false value is simple and unambiguous – search for 1 or 0.

When you start scripting to run reports you’ll have to make decisions based on the data. A number can be evaluated to true/false without requiring any conversion. That allows us to use the field value as the test, so we can say

If ( Applicant::hasDriverLicence ) then ...

That is simple and easy to understand. Reading it makes sense, even to non-programmers.

Evaluating wordy data quickly becomes complicated, here we evaluate the data looking for people with a driver’s licence.

If ( LeftWords ( Applicant::hasDriversLicence ; 2 ) = "Does have" ) ) 

That’s much harder to read and much harder to write.

Re-using Data to Enrich the User Interface

FileMaker offers two powerful features for modifying the user interface: conditional formatting and the “Hide object when” option. Both features are able to determine the outcome using recorded data. Data that is in a boolean state is  perfectly suited to these tests.

Conditional Formatting

Conditional formatting is focussed on text style, background colour,  and icon colour.

Hide Object When

The ability to hide or show an object allows us great flexibility in the layout design. We are able to show or hide objects conditional on the answers given to earlier questions. In the example below, the field for the driver’s licence number is only shown when it is needed. This allows us to build dynamic interfaces which can respond the users inputs to present different workflows.

Using Boolean Data to Control Script Flow

Using booleans to simplify conditional statements to control the user interface we gave examples which used IF. The same logic applies to the CASE function too, and many other built-in functions. You can utilise boolean data to control the flow of scripts too, simply by using the same logic inside the IF and ELSE IF script steps.

Wrapup

Learning to describe your data in boolean form reduces your effort and makes it much easier to provide better solutions.