Join Cris Ippolite for an in-depth discussion in this video Reviewing relationships types, part of FileMaker Pro: Relational Database Design (2011).
- When we talk about relationships in database terms we're talking specifically about how the different tables are related to each other and how the relationships between those tables really define the data that's stored within them. In order to decide on the data model for your database system it's important to first determine what the possible kinds of connections are that you could have between your tables, and then from those we'll chose the types of connections that we actually have within the database that we're working on. So in this movie we're gonna quickly review the different types of relationships and I'll give explanations of each. To keep things simple we're gonna focus on three types of relationships that account for all the situations you're going to encounter within your database and your data modeling exercises.
They're called the one to one relationship, the one to many, or also known as the many to one relationship, and finally the many to many relationship. The first one is called a one to one relationship, it's very rare. And what it means is that you've actually created a table for something that should actually just be a field. So for example, in this case we've got one customer can have only one address, and one address can only be related to one customer. So the relationship that we have between these two items is just a one to one.
And in this case, as in any other case where you've identified a one to one relationship, that means that address is really an attribute of the customer, and therefore should just be a field defined inside the customer table, not its own table related to the customer table. So one to one relationship simple mean an attribute has been improperly identified as an entity. So if you go through the process and find out you've got a one to one you should really just take a look at it, because what you actually might have is just a field. But the main relationship that you'll wanna be familiar with are the ones that are called one to many relationships.
These indicate that you have a parent table that's related to records in a child table. The parent, child vernacular is common to data modeling and I'm gonna be using it quite a bit in this title, so what I'm talking about there is that on the one side of the one to many we call that the parent table and on the many side of the one to many we call that the child table. So one parent can have many related children. You may also encounter relationships that are called many to many relationships, and they should be eliminated. Many to many means that you've got two related parent tables, but their relationship is not actually direct.
Instead it needs to be related through an additional child table, so in fact you'll have a one to many relationship. We'll discuss this in detail in the later chapter on many to many relationships. The way that you'll know that you're done with the data modeling portion of your database planning is because you will have evaluated all of the possible relationships between your tables and then determined that all you've got left are one to many relationships. So that's the first notable learning point here is that you must resolve all of your relationships into one to many relationships, thus removing all one to ones, and all many to manys, and leaving yourself with just a series of connected one to many relationships.
Later in this title we're gonna look at some examples of relationships that we might find in our modeling process so we can identify their specific types. I'm gonna use the terminology I've established here a lot in those exercises, so it's important to define them first. Whenever I talk about a child table or a child record I'm talking about a table or record stored in the table one the many side of a one to many relationship. Whenever I talk about a parent table or parent record I'm talking about a table or a record on the one side of a one to many relationship.
Once you understand these relationship types and can identify them then you can move on to diagramming the relationships during your database planning stages.
The course applies to versions of FileMaker Pro from 7 through the most current version.
- Reviewing relationships types
- Diagramming relationships
- Resolving many-to-many relationships
- Determining which tables need key fields
- Defining tables in FileMaker Pro
- Using the Relationship Graph
- Using multiple match fields in one relationship
- Using global fields to filter portals
- Creating self relationships
- Creating aggregate functions using relationships
Skill Level Intermediate
Q: The exercise files for this course do not work on a Mac. It downloads executable files. Can you provide a workaround?
A: The files provided for this course were made pre-FileMaker Pro 12. The system does not recognize the .fp7 files immediately as something that can be opened with FileMaker Pro 12 and instead shows them as Unix Executable Files.
The solution is quite simple. Just double-click the file, at which point the Mac will prompt you to choose an application to open the
file with. Select FileMaker Pro 12 and it will then convert the file from a .fp7 file into the new FileMaker Pro 12 file format, which is .fmp12.
Q: This course was updated on 5/12/2015. What changed?
A: We moved a video from the "Using Relationships" chapter of FileMaker Pro 13 Essential Training into this course, where it will remain evergreen.