The most important part of any database is the data model. In today’s spreadsheet driven data design, we often see a single flat table with columns as far as the eye can see. In this article we will examine the principle’s of good data design, and how to apply them using the Relationship Diagram in FileMaker Pro.
Know Your Entity
The data table is the basic building block of your entity relationship diagram. It includes fields which are represented as columns in a spreadsheet, and records, which are represented as the rows of data. In a relational database like FileMaker Pro, it also has relationships to other data tables. The important aspect of a data table is that it represents a real world entity. What that means is don’t try to track your apples and your oranges in the the same table. It also means don’t track interface fields in data tables, but that is the subject of another article.
Let’s use the example of a database for tracking drivers of cars. A driver is one entity and a car is another entity, so we will set up a separate table for each one. Each table will have a list of fields which represent properties of that entity. The first field will be a unique identifier, which allows us to distinguish each car record as unique, even if they are both blue Ford Fiestas. This is a functional field which is referred to as the primary key. The primary key is used to refer to a specific record throughout the database.
Identify Unique Records
Often an entity already has a unique identifier. Even though all cars have a unique VIN, do not use a real property as a unique identifier. It is a best practice to always generate a unique identifier, using the auto-enter function Get ( UUID ). The user will never see or use this 16 digit alphanumeric string. The reason for always setting a unique identifier is because properties can always change. Now, a VIN is not likely to change, but what if it was initially entered incorrectly? Correcting the VIN would mean going through the entire database and also correcting any other records that were referencing that specific car by its VIN. By using a self generated unique identifier, there is never a need to change it.
Next, add the properties of cars to track. Each property is tracked in a field, so we will add fields for color, number of doors, manual or automatic transmission, and number of seats. Of course there are many other properties of a car, but these are the only ones we are interested in. We can always add more fields later. By default, FileMaker also adds a creation and modification time stamp, the creation account name and the modification account name. These are audit fields which are useful for determining when a record was created or modified and by whom. Another important functional field is a summary count. This is a summary field which presents a count of the primary key, it is useful when wanting a count of a group of records.
Types of Relationships
Now we have a car table which is made up of functional fields, property fields, and audit fields. We can similarly set up a person table with the relevant property fields for tracking people. And then we come a question of how to relate a person to a car. Is the car a property of a person, or is a person a property of a car? The answer is neither, so let’s explore how they’re related to each other.
First, let’s take a look at the types of relationships we will encounter and how to handle them in a database. These three relationships cover everything we will model in our relationships graph, but knowing when and how to implement them is the complex part.
One to One; a car can have only has one steering wheel, and a steering wheel can only be attached to one car at a time.
One to Many; a car can have many tires, but a tire is only attached to one car.
Many to Many; a car can be used by many different drivers, and a driver can drive many different cars.
Where To Put Your Keys
If your data entity only relates to one item, then you can add a field for that table that stores the unique key of the related item. Since these keys are coming from another table, they’re called a foreign key. This works well for the one to one and one to many relationships. In the one to one example, both entities will only relate to one other, so the foreign key can go on the car table or the steering wheel table. Since every car must have a steering wheel it makes sense to track the steering wheel foreign key on the car table. The foreign key will usually go on the entity that requires the relationship. This field will store the primary key of the steering wheel attached to the car. For the one to many, put the foreign key on the tire table, since the tire is related to only one car.
The many to many relationship is a little more complicated. Our example is like the last case, a car can have many drivers, and a person can drive many cars. The car is not a property of the person and the person is not a property of the car, so we will not be using a field on the car or person table to track the relationship. For these types of relationships, we will use a third table, a relationship table. This table will have two functional fields; one for the primary key of the person and another for the primary key of the car. In this way, the many to many relationship many to one to many relationship.
Keep It Simple
In summary:
1:1; the foreign key is stored in the table that requires or is more likely to be related to the other
1:many; the foreign key is stored on the table that relates to the one other
many:many; a relationship table is used to store the foreign keys from both tables
By separating tables into unique entities, it becomes less likely to have tables with more than 50 fields. When that does occur, there is usually an opportunity to break that table up into two tables that are related by a one to one relationship. For example, a car table could have one table for tracking interior properties and another table for exterior properties. Keep this in mind when designing data tables, a field with no data takes up more space than a table with no records. Don’t be afraid to split out large tables with many fields into smaller, related tables with fewer fields.
