Join Cris Ippolite for an in-depth discussion in this video Understand relationship types, part of FileMaker Pro 15 Essential Training.
- View Offline
- [Voiceover] An important note before we get started here. If you went through the tables section and determined that you only have one table in your solution, you can certainly skip the entire relationships chapter if you want to. I still think it's so important that you shouldn't do that, but if you have one table, there's not gonna be any relationships. But I also get worried if you just have one table, why don't you watch this chapter anyways to make sure that you in fact really should just have one table? Because what you might have is a flat file instead that needs to be related. Anyhow, this chapter explains how to set up relationships in FileMaker, one of the most critical foundational elements of understanding the programming environment.
So far in the process in creating our new FileMaker custom app, we've talked about the concept of tables. We know the tables are gonna be these little buckets of information where we store different groups of information, and because we can have different groups of information in one single FileMaker database, we need to have a place to store them and keep them compartmentalized. That's what tables are, so we've determined what tables we need, and then we have to talk about how these tables relate to each other, because that's the core element behind relational databases, is the relationships between the tables.
We're gonna take a moment to understand what kind of relationships we could have, and what kind of relationships we want to have, and then we're gonna go into the modeling process so we can actually see what relationships we have within our system. I'm gonna discuss the concepts of relationships in this chapter, but you should understand that relationships can get complex pretty quickly. I'm gonna keep it simple here in essential training, but I strongly recommend that you go check out a title that's called Relational Design with FileMaker Pro, where I take a lot of extra time to discuss the relational concepts, and I dig a lot deeper than we're able to do here.
Now, when we talk about relationships in database terms, we're specifically talking about how different tables are related to each other, and how the relationships between those tables really define the data that's stored inside the tables. The relationship structure between tables really is just a rule that says, how do the records stored in a table relate to each other? In order to decide on the data model for our database system, it's important to first determine what the possible kinds of connections are between the tables, and then we can choose from those types of connections, or relationships as they're called, to determine which ones we actually have.
In this movie, we're gonna quickly review the different types of relationships, and give an explanation of each. I'm gonna keep things simple, and we're gonna focus on the three types of relationships that account for all situations that you will encounter when you're planning your database. We're gonna review the relationship types. They really come in three categories. The first type is a one-to-one relationship, which is very rare, and you might want to note this is one of the relationships we want to eliminate before we build it into our database. Then there are the one-to-many, which really could be called many-to-one, because we evaluate them in both directions.
We'll get into more details on that in a second. These are the ones that we want. All of our relationships need to be one-to-many before we proceed with building them in the database. Then there's the many-to-many database. These will look in our diagram in different ways. We'll talk in later movies about how the boxes and the lines and all that relate to what kind of relationship it is, but the one-to-ones, you see how they're represented, many-to-manys as well. Those two are the ones we want to eliminate, but the one-to-manys are the ones that we want to focus on and end up with when we're done. First of all, one-to-one relationships, what is a one-to-one relationship? Let's say for example one customer only has one address, but in our data modeling exercise, we said that we might need a customer table and an address table, so we might think that potentially we have different information that describes different entities or different groups and the attributes need a place to be defined, but then when we really look at it, we're evaluating that actually one customer can only have one address, and that one address can only be assigned to a certain customer.
In that case what we have is what's called a one-to-one relationship, and often we'll diagram those. It's only temporary because we have to eliminate them, but they can be diagrammed as a single hash on both sides of the relationship. What that really means is we've got a bunch of fields, like let's say customer address, billing address, or whatever, that really just need to be defined in the customer table. The way we resolve a one-to-many is we eliminate the redundancy and move those fields or the address fields in this case over to the customer table, so eliminate the redundant table and move those fields into the parent table, so you're really just getting rid of two tables making them one.
Now the other type of relationship is one-to-many. These are the good relationships. This is where we have the one table is related to the many. Now in this example we've got one customer can have many orders, so we'd look at it from the customer to order side, but we're actually also evaluating it backwards from the order to customer side, so one order can only be from one customer. That's the important part, so when you see these referred to as one-to-many, you'll often hear them also referred to many-to-one, that's how you actually truly know you have a one-to-many relationship. We'll get into this in more detail as we go forward, but the third type, the many-to-many, these are the dreaded relationships.
These are relationships that you actually can build into FileMaker, but they need to be eliminated, and they're eliminated by actually adding an additional table, so remember, one-to-ones, we take two tables and we make them one. In a many-to-many, we take two tables, we actually make them three. We're gonna talk about how we do that, but in the data modeling exercise, you might run across a many-to-many, and you know you've got a many-to-many when you evaluate it in both directions. For example you say, one order can have many products on it, but then one product can appear on many orders, so you actually have one-to-manys going in both directions.
That's the first clue. We call that a many-to-many and it can create a lot of different problems for us in our data modeling exercises. Ultimately, we end up having to resolve these relationships. For example, the customer and address, the one-to-ones, we need to get rid of those. The many-to-manys, like in the example here with the one order can have many products on it, and one product can be on many orders. We need to eliminate that as well. We'll talk about how we do that with a join table later on in this chapter, but the ones that are good, the ones that we're looking for, or really our goals here is to make every relationship a one-to-many relationship.
That's how you know you're done, when every one of your relationships is one-to-many, you can then move on to FileMaker and start defining these.
- Understanding the FileMaker Pro interface
- Creating new FileMaker apps from spreadsheets
- Building tables and relationships
- Defining primary and foreign keys
- Working with fields
- Creating, editing, and deleting records
- Importing and exporting data in a FileMaker Pro database
- Creating and formatting layouts
- Finding data and sorting data
- Printing from FileMaker
- Building reports
- Using calculations
- Triggering scripts