Relational Database Design 101:
Relating Multiple Databases to Multiple Databases

The following article is reprinted from the pages of FileMaker TechInfo Knowledgebase.
The article is reprinted in its entirety, and Revelation Enterprises makes no warranties,
whether express or implied, as to the validity of the content of this article.

This article may be located in FileMaker TechInfo at this address:

All relational databases require an intermediate file to break a many-to-many relationship into two one-to-many relationships. You can name this third file anything you want. In this example, the third file is named Line Items. After resolving the many-to-many relationship, your diagram should look something like figure 7.

The diagram in figure 7 the following:
A customer can have many invoices.
An invoice can have many line items.
A product may appear on many line items.

Now that you have completed the first part of your entity relationship diagram, it’s time to turn the entities into files and specify the fields for those files (see figure 8).

Identifying Fields

We have identified the following entities for our database:
Customers
Invoices
Products
Line Items

Now let’s look at each one and identify the fields we’ll need. Take a few minutes and write down each entity and what fields you would create (see figure 9). As you identify the fields, think about your data needs in terms of where data is stored and where data needs to be displayed. For example, customer data is stored in the Customer file, but needs to be displayed in the Invoice file.

Keep the following in mind:
Each file must have a key field that will contain a unique value.
Some files will have a match field that will be used in creating a relationship.
Each file contains only those fields relating to it’s purpose.

Identifying Key and Match Fields

Now that we have our list of files and fields, let’s identify the key fields in each file. Take a few minutes and circle the key fields. Remember that key fields identify an individual record.

You may have identified the following key fields: Customers-Customer ID, Invoices-Invoice ID, Products-Product ID, Line Items-Item ID. The names of your key fields may be different.

Notice that the Invoices file includes the following match field: Customer ID. In order to display customer data in the Invoices file, you must have a common field between the two files to create a relationship. Customer ID is that common field. In the Customers file it is the key field, in the Invoices file it is the match field.

Take a few minutes and identify the match fields in your files. As you do this, think about where your data is and where you want to display it.

You may have identified the following match fields: Invoices-Customer ID, Line Items-Invoice ID and Product ID.

Now that you have identified the key and match fields, complete the diagram by indicating the type of relationship between the files by using one of the symbols pictured in figure 10.

Your final result may look like figure 11

Figure 11 indicates the following:
A customer can have many different invoices, but an individual invoice can have only one customer
An invoice can have many line items, but an individual line item appears on one invoice
A product can appear on many different line items, but an individual line item has only one product

Now that you have identified all of the entities, fields, relationships, and relationship types, it’s time to start creating your files, defining the fields and relationships, and creating your layouts.

Key points to remember:
Plan your relational database on paper first.
Identify the categories or entities of information.
Diagram the entities showing the relationships between them.
List the fields for each entity.
Look for redundancies in your fields between files and eliminate them.
Break up many-to-many relationships into two one-to-many relationships using a third file.
The more you plan ahead, the easier it will be to create your database.