New eBook now available – FileMaker Relationships 101

Are you new to FileMaker Pro or relational design?

Are you having trouble understanding how to create solid relationships in a database?

We have written an introductory eBook for you. And best of all – it’s free!

Contents
  • Relational Structure
  • Using Keys
  • Defining Relationships
  • Using Relationships

Download the eBook here. It is available in PDF format or as an interactive iBook.

The A-Z of FileMaker: K is for Key

K is for Key

In a relational database like FileMaker Pro, key fields are used to establish relationships between tables. Depending on where you get your information, there are many types of keys. In this article, I will focus on just two – primary and foreign keys.

Primary keys defined

A primary key (PK) is a field that contains data unique to the record in that table. As such, it can be thought of as a record identifier. There are some required characteristics of a primary key:

  • never be empty
  • contain unique data (in the table)
  • never change

It is also most useful if it is not real or meaningful data. To understand this better, let’s consider some fields that seem to fit the requirements for a primary key but should not be used as such.

Avoid using these as primary keys
  • tax file number (TFN)
  • email address
  • student number
  • phone number

Although most people in Australia (and certainly those who are employees) will have a TFN, it can fail the PK test. First, if a person cannot provide their TFN for whatever reason, the field is left empty. Second, if the number is wrongly entered, it will need to be changed.

An email address could be shared by multiple people and may change.

A student number is generated by another database system. As for a TFN, it may not be available or may be incorrectly entered. Student numbers may be re-issued in some circumstances.

A phone number may be shared by multiple people and will likely change.

Good primary keys

The best primary keys are generated by the database system in which they are used.

The most basic is a simple serial number. In FileMaker Pro, the field option can be set to auto-enter a serial number on record creation. This will ensure sequential creation of unique serial numbers even in multi-user systems.

serial field option key field

Many database systems can generate a universally unique identifier (UUID). In FileMaker databases (as in many others), the UUID is a unique 16-byte (128-bit) string. In the standard representation, the UUID is 32 hexadecimal (base 16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12. An example of this is:

012F5382-37FF-413B-A93C-D957A5C6F451

The function used to generate this is Get ( UUID ). To create a primary key in this way, set the field options to auto-enter a calculation that uses the function. We covered this (Set a primary key) in a previous blog article The A-Z of FileMaker: G is for Get.

Foreign keys defined

A foreign key (FK) is a field that contains a unique identifier from a PK of another table. In this way, the record with the FK is linked (or related) to the record with the key data in the PK.

Consider a FileMaker solution for recording issues with equipment. There is a table for equipment with a PK field. There is a table for issues with a PK field. The issues table also has an equipment FK field.

When a new issue is created, the unique identifier of the equipment is copied into the equipment FK field. This serves to relate the issue record to a specific equipment record.

By definition, the values in a FK will not be unique. In the above example, all issue records for a specific equipment item will have the same data in the FK.

Usually the FK will not be empty. In the above example, if the FK is left empty, the issue is not attached to any equipment and would be considered invalid. In some cases, it may be valid to leave the FK empty.

Generally the FK will not change. If it does change, this means that the record is relating to a different record. In the above example, the FK may be changed to correct an error when the wrong equipment was identified.

Referential integrity

Referential integrity refers to a rule that every foreign key value must match a primary key value in an associated table. In other words, the FK value must exist in one record of the other table.

Naming Keys – What do you think?

Most developers have opinions and conventions for naming their keys. For what it is worth, I name all my PK fields with a simple name – ID. I find that the context of the field is usually self-evident. It also allows me to copy and paste a PK into a new table.

I name my FK fields simply too – IDtable. In the above example, the FK would be named IDequip or IDequipment.

Here is an ERD from a previous post on the Join Table.

Do you have a naming convention for keys and a reason for using it? Feel free to leave a comment below.

Other keys

Often when we import data from other systems, we may be provided with a key from that system. It may seem like we have a ready-to-use PK supplied for us. Beware! Do not use the PK from another system. However, it is useful to store it with our data. In reporting, we can then refer to records with our key or their key.

The A-Z of FileMaker: J is for Join Table

J is for Join Table

A join table is used to resolve a many to many relationship with a flexible structure for data storage.

The FileMaker Platform works around a relational database. The structure of a relational database includes tables and relationships between tables. There are three major types of relationships:

  • one to many
  • many to many
  • one to one

The most common relationship is one to many. This is where one record in table A may be related to many records in table B. Conversely, each record in table B is related to only one record in table A.

For example, consider customers and sales. One customer is (hopefully) related to many sales. Each sale is related to only one customer.

Other examples of one to many relations are:

  • one venue – many events (each event = one venue)
  • one employee – many payments (each payment = one employee)
  • one diver – many dives (each dive = one diver)
  • one student – many certifications (each certification = one student)

These are often represented with a crow’s foot notation where the crow’s foot is the ‘many’ end of the relation;

When working through the data structure for a database, we often come across many to many relationships. Some example are:

  • houses and owners – one house has many owners (over time); one owner owns many houses
  • students and classes – one student enrols in many classes; one class has many students enrolled
  • cars and mechanics – one car can be serviced by many mechanics; one mechanic services many cars
What is the problem with many to many relationships?

While it is possible to create a many to many relationship in FileMaker Pro and other database platforms, it is generally not done. When we find a many to many relationship, it presents a problem with the storage of data associated with the relation.

For example, with a relationship between houses and owners, where would you store data for the sale price and date purchased? If you put fields in the house table, then you will need to store a price and a date each time the house is sold; if you put them in the owner table, you need a price and a date each time they buy a house.

A join table is the solution

To correctly model a many to many relationship, we break it down using a join table. This is a table that represents the relationship between the tables. There is often an action word that represents the relation:

  • owners purchase houses; houses are purchased by owners
  • students enrol in classes; classes take enrolments by students
  • cars are serviced by mechanics; mechanics perform services on cars

In the case of house and owner, we add a table between called purchase. There is a relationship between owner and purchase (one owner makes many purchases; each purchase made by one owner). And there is a relationship between purchase and house (each purchase is for one house; one house can have many purchases).

Each purchase record relates to one house and one owner. The purchase record is the right place to store data such as sale price and date purchased. So this solves the data storage problem with one purchase record created every time an owner purchases a house.

Many to many resolved

Each table will have a primary key (named ID in each table below). The relationships are created to foreign keys (ID owner, ID house) in the join table.

When creating a purchase record, you need a process to select the owner and the house. The primary key (ID) of each is inserted into the foreign key fields of the purchase record to create the join.

Your turn

Draw the tables and relationships required for the other many to many examples above. Name the join table and the foreign keys required.