About planning a database
A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.
Follow these general steps to plan a database:
1.
Determine the purpose for your database, or the problem you want to solve. For example, “to keep a list of my customers,” “to manage my inventory,” or “to grade my students.”
If other people will use the database, be sure to talk with them about the data they will need.
2.
Consider the information you will store in your database. Typically, information falls into broad categories. Accurately identifying these categories is critical to designing an efficient database, because you will store different types and amounts of data in each category. For example, a database intended to track sales has categories such as “customers,” “products,” and “invoices.” A database that records student grades has categories such as “students,” “classes,” and “assignments.”
3.
After you’ve determined the broad categories, consider how these categories are related. This can be done by writing simple sentences that describe how the categories interact, such as, “customers order products” and “invoices record customers’ orders.” Each of these pairs suggests a relationship between the data in one category and the data in the other category.
4.
After you’ve identified your categories of information, you are ready to organize your database.
In database terminology, these categories of information are referred to as tables. Tables are used to group data containing a common element or purpose. For example, you might use one table to store names and addresses, while you use another table to store transaction details, such as date of sale, item number, unit price, and so on.
Typically, databases are organized in one of three ways:
 •
A single table in a single file. Use a single table if you need to track data in one category only, such as names and addresses.
 •
Multiple tables in a single file. Use multiple tables if your data is more complex, such as customers, products, and invoices.
 •
Multiple tables in multiple files. Use multiple files if you need to share the same data among several different database solutions. For example, you can store your tax rates or shipping information in a separate file if you plan to use that information in more than one solution.
Use relationships to share data between tables in the same file or with tables in external files. Other database elements, such as scripts and access privileges, are stored at the file level; therefore, some complex solutions will benefit from using multiple files.
Note  FileMaker Pro is very flexible, so the decision to store data in a single file or in multiple files is often one of packaging and convenience. Data stored in tables is very easily shared between tables in the same file and tables in external files using relationships, as explained in Working with related tables and files. Other elements, such as scripts and access privileges, are stored at the file level, and because of this some complex solutions will benefit from using multiple files.
5.
Determine the database tables and the data they will include, and, in turn, which fields you will need.
Tip  To make it easy to search and sort records, create separate fields for first and last name, titles (like Mr. or Dr.) and items in addresses (city, state or province, country, and postal code). Separating your data into multiple fields at the time of data entry can make it easier to generate future reports. For example, using separate fields to capture transaction details such as the date, item number, quantity, and unit price of each transaction makes it easier to compile summary and subsummary reports at the end of a week, month, or year.
6.
Decide which fields will contain common data among the tables.
For example, a database for a bakery business might include these tables: a Customers table, which stores customer information; a Products table, which stores product information; a Line Items table, which stores information about products sold, and an Invoices table, which stores order information.
Each table has only one subject, and all fields in a table describe only that subject. For example, the fields in one record of the Customers table together store all the information about one customer. For the same reason, you might assign each customer a unique, identifying number. You wouldn’t enter a customer identification number into the table unless you had a new customer to add, so the existence of a customer number determines the existence of a record. A Customers table might also have fields for the customer’s name, address and phone number.
A Products table might have fields for a product identification number, the unit price for each product, and the quantity in stock. A Line Items table might have fields for product and invoice identification numbers, the name, unit price, quantity, and total price of each product sold. An Invoices table might have fields for an invoice identification number, order date, and salesperson.
Fields in the Customers, Invoices, Line Items, and Products tables
7.
Determine the match fields for each table, and circle each one in your plan.
See About match fields for relationships.
Match fields in the Customers, Invoices, Line Items, and Products tables
8.
For each table, decide which fields will store data, and which ones will be used from other (related) tables.
Based on a table’s subject, you can see where it makes sense to store the data and where to use data from a related table. Other than match fields, all fields should appear only once in your database. Cross out occurrences of fields that don’t pertain to the table’s subject.
Unnecessary fields crossed out in the Customers, Invoices, and Line Items tables
9.
Determine the relationships between the tables. In your plan, draw a line from each match field in a table to the corresponding match field in the related table.
What establishes a relationship between tables is that their match fields contain matching data.
Relationships between the Customers, Invoices, Line Items, and Products tables
Relationships also make it possible to group your data to resolve complex queries. For example, relationships can be used to determine current inventory levels, sales projections, and other tasks where it is necessary to query data across multiple tables. See Working with related tables and files.
10.
Determine whether you need to share your database with other users, and how they will access the file.
See Sharing databases on a network.
11.
If you’re designing the database for other people to use, show them your paper plan and ask them to review it and suggest any changes.
12.
Consider who will use the database and whether you want to restrict access to it. When you create the database, assign access privileges as needed.
See Protecting databases.
13.
Decide what layouts you need, and plan a separate layout for each task.
For example, create separate layouts for printing labels or envelopes.
See Creating and managing layouts and reports.
14.
Create a form that lists all the files and tables you need and the fields for each table. Also list the forms and reports you will generate from each table.
15.
Create your database.
16.
If you’ve designed the database for others to use, ask a few people to test it. Then, fix any problems they found before you make the database available for everyone to use.
Related topics 
Creating a FileMaker Pro file
Defining database tables
About defining database fields
Creating a layout
About protecting databases
About relationships
Was this topic helpful? Send feedback.