Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A quick note before we get started, since the topics that we're going to be talking about in this chapter are going to be relationships, specifically relationships between tables: You're going to need to have multiple tables in your solution. In the example solution that we're creating, we do have multiple tables, and that's what we'll be using throughout the rest of this title. But also if you've determined in your own real-world database that you only have one table, well then you're not going to be able to have new relationships between tables. So feel free to go ahead and forward on to the discussion on fields so you can talk about assigning attributes to your table.
But at some point you will want to come back to relationships, because this really is a core concept behind, not only FileMaker databases, but databases in general. With that said, in the previous chapters I have mentioned things like relationships before. And when I'm talking about relationships, I'm 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. So the process of determining the relationships between tables in a database is known as data modeling, and the goal of data modeling is to translate a real-world problem into a structure that can then be captured in some kind of database, and in our case, a FileMaker relational database system.
A relational database system can be thought of as a collection of discrete data groups or tables, which have some connections between them, and these connections will be referred to more commonly when we use the terms relationships. So in order to decide on the data model for your database system, it's important to first determine what are the possible kinds of connections that we could have between our databases, and then we can choose from those types of connections, or relationships, to determine which ones we actually have. So in this movie we're going to quickly review the different types of relationships and give an explanation of each.
To keep things simple, we're only your focus on two types of relationships that account for most of the situations that you are going to encounter in your database, but there are three, technically. The first one that we're really going to skip over is called a one-to-one relationship. It's very rare, and it usually means that you've created a table for something that should actually be field. A lot of times you'll see one-to-ones when you have, let's say you thought you needed a table for phone numbers, but then you also have the table for customers. If a customer only has one phone number, then you don't need to have two tables. What you've got there is you should just have an attribute or a field defined inside customers.
So if you go through this process and find out you've got one-to-one, you should really take a look at it, and what you actually might have is just a field; they should be combined together into the same table. But the two relationships that you will want to be familiar with are the one that's called a one-to-many relationship, which could also be interpreted as a many-to-one, more commonly called one-to- many, and then also a many-to-many relationship. And when you're done data modeling, the way that you'll know you're done is because you've evaluated all of the possible relationships between your tables, and you've determined that all you have left are one-to-many relationships.
So that's the first notable learning point here is that you must resolve all your relationships to one-to-many relationships. And we'll talk about how identifying many- to-many relationships will factor in there. So another other way to interpret these types of relationships is if we look at the tables. Tables have records in them. So in the case of a one-to-one, like we mentioned before, that means that any one record in table A, for example, could only have one other related record in table B. So one customer can only have one phone number.
If it's true that a customer can have multiple phone numbers, then we don't have a one-to-one anymore. We actually have a one-to-many. So in the case of a one-to-many relationship between two tables, again, we look at the records, and that means that one record could have one or many related records in another table. So if we look at that again, in our case we've got a Customers table, and we've also got an Invoices table. So we've determined that any one customer could have one or many different invoices in the invoice table, or be zero for that matter, but ultimately they could have many.
So if we go to our customer table and we have a record in there that's customer XYZ, and we know that the customer XYZ has ordered five different times each reflected by an invoice, represented by a record in the invoice table, that means that we've got a one-to-many relationship between the Customer and Invoices table. Now in the case of many-to-many, that's just simply means that we've got a one-to-many going in one direction, and a one-to-many going in the opposite direction. In the case of the many-to-many that just tells us we've got two one-to-many relationships kind of butting heads with each other. But before we get further in to that, the most important type of relationship is known as a one-to-many relationship.
As I mentioned earlier, this is going to be the only relationship you want left when you're done with your data modeling process. Now I'm going to use some terminology throughout this title that I want to make you familiar with now. And it's common to refer to the table that access the one in a one-to-many, so let's say we have, the one in this case is the customer, so that's commonly referred to as the parent in the relationship, and in this case invoices is on the many side; that's referred to as the child. So we've got customers are related to invoices in a one-to-many fashion, and customers are the parent and invoices are the child.
I'm going to use that terminology a lot. Whenever I talk about child table or child record, I'm talking about the many side. Whenever I talk about parent table or parent record, I'm talking about the one side. So we should know in our system one customer can have many invoices; therefore, the parent is the customer. Remember, your goal is to resolve all relationships in your system to a one-to-many relationship. We're going to go through several examples of that. However, there is this other type of relationship, which is called a many-to-many relationship. Ultimately, a many-to-many relationship could be something like a product in an invoice.
So, for example, in our solution we've got customers and invoices. We know the relationship between those two. One customer can have many invoices. But what about the relations between a product and an invoice? One product could appear on many invoices, so it looks like we've got a one-to-many, but one invoice can also have many products. So we've got a many-to-many, in that case. And generally we're going to need to do some extra work in order to resolve these. Many-to-many relationships are common and powerful type of database relationship, but unlike one-to-many relationships, which can be constructed simply by adding appropriate key attributes to related tables, a many-to-many relationship is going to require a lot of extra work in our data model, and the reason we will eliminate these many-to-manys, as we'll talk about in upcoming movies, is because it makes it impossible for us to store our data properly, to enter our data properly or to even create reports based on the proper amount of data.
Data modeling in FileMaker, or any other database platform, allows you to determine the relationships between your tables, and you'll eventually set those tables up inside the database file, and you're also eventually going to set up the relationships. But first you need to know what their relationships are. So as we've talked about in this video, you can have a one-to-one relationship, or more commonly you'll have one-to- many relationships or many-to-many relationships, and once you understand those relationship types you can then go ahead and diagram the relationships.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86480 Viewers
80 Video lessons · 135881 Viewers
59 Video lessons · 54636 Viewers
52 Video lessons · 68514 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.