Join Cris Ippolite for an in-depth discussion in this video Diagramming relationships with entity-relationship (ER) diagrams, part of FileMaker Pro 13 Essential Training.
So far in the process of creating a database, we've learned that we have to determine what tables we're going to need inside the database. Then, once we determine what tables we need, we need to understand that since we can potentially have multiple tables in one database. We need to evaluate the kinds of relationships that those tables are going to have with each other. Understanding now that there are three different types of relationships, one to one, one to many, and many to many. It's time now to create a diagram of all the tables that we're going to have and then how they're going to be related to each other.
This will help us set up the relationships inside the FileMaker database when we're ready to start creating the database. In this movie, I'm going to expand on the techniques that we've talked about already. And we're going to learn a general method for diagramming database systems and it's called Creating an Entity Relationship Diagram. Entities are the different groups of information that eventually become a table. The relationships are the relationships that they have between them and the diagram is a way we represent these. These are commonly referred to as an ERD.
ERD modeling breaks into three phases. First, we develop a list of entities or tables that are going to belong in the data model. Next, we determine in the diagram the relationships between those entities which is what we're going to focus on here. And then we review the diagram for correctness and consistency. This is the step where we're going to eliminate things like one to one relationships and many to many relationships. So in the database we've been talking about so far in this title, we're determining that we need a basic system. Where we want to have the ability to manage customers and invoices.
So we've got a database that manages invoices, where one invoice can be per one customer. And we have products in the system as well, but we've determined that there could be multiple products appearing on a single invoice. So the first thing we want to do, is determine what might be the possible tables in this environment. We've talked about this a little bit already, but we can determine that we've got invoices, customers, and products. Will isolate these as our potential entity list. However, just identifying the entities or the potential entities is really only half the task.
We must then determine how the tables will be related to each other before they actually become tables in our database. Now that we've got this list, we can start to determine the relationships between them. And you can begin anywhere. And this is also the part of the process where we're going to want to create our ERD, or our Entity Relationship Diagram. You don't need any custom software or anything like that to do this. You can just get a pen and paper, or a whiteboard or something like that. But before I proceed, I want to tell you a little bit about some things that you're going to want to know when you're creating an ERD. In ERDs it's common to represent your entities as a box with the entity name inside of it.
These will eventually become the table names that you use in your database. To visually represent the relationships between these two,. It's common to draw a line between the two of them. Now, keep in mind relationship between any two tables will eventually need to become a one to many relationship. So, the line that you draw is ongoing to tell you which is the one and which is the many. You'll notice on the parent side of the relationship, we don't have any hash. You can either have no hash or a single hash, which represents the one side of the relationship. On the child side of the relationship or the table that contains the many records, we've got a double hash.
So this tells us by just looking at this that the table on the right is the child and the table on the left is the parent. Now taking what we've learned there remember that we've already determined that we'll need at least three tables in our database. Lets represent these as boxes in our Entities Relationship Diagram. What we want to do now before we can draw the line between them is determine what types of relationships they will have. Lets isolate two of these table first and isolate the relationships that we might have between them. Lets start with customers and invoices.
The way that you evaluate the potential relationships between these two tables, is by asking certain questions. So here are the types of questions you that may ask about the relationships between these two tables. For example, can a customer have many invoices? If you've determined that that's true or could possibly be true. Then you know that you at least have a one to many going from customers to invoices. But you need to be able to evaluate the relationship in both directions. So, for example, can one invoice belong to more than one customer? If the answer is no, we've determined that one invoice can only have one customer.
That means that we've got a one to many relationship. One customer can have many invoices, but any one invoice can only belong to one customer. This is a true one to many relationship. Let's evaluate the relationships with other tables now. We'll keep the invoices table, but in this case we'll bring in the products table. And let's ask the same types of questions. Can one product appear on many invoices? The answer there is yes. So it appears that we could have a one to many relationship. But let's evaluate the relationship going in the other direction.
Can one invoice have many products on it? Well, in this case the answer to that is yes as well. So what we actually have here is a many to many relationship. One product can have many invoices and one invoice could have many products on it. Now remember we've already determined that we need to eliminate many to many relationships. So that's what were going to have to do here. The way that you element a many to many relationship is actually by creating two one to many relationships instead. The way we do that is by adding a third table.
So we've got products and invoices. And now let's add a third table to eliminate the many to many and let's call it invoice line items. Now that we've got this third table we can actually two one to many relationships. One from the products to invoice line items indicating that one product can have many of these invoice line items. And one from invoices to invoice line items. The records in the invoice line item table will represent one record every time a product appears on an invoice. Now we haven't lost a relationship between products and invoices by creating this third table.
Instead they are related through the invoice line item relationship. As a result, this table is commonly refered to as a join table, thus joining the two original tables together. Now you might ask yourself, why do we need to eliminate those many to many relationships? And why do we need to add, a third table, well if you don't do it, you're going to run in to some problems. So you can keep your many to many relationships. because certainly FileMaker will allow you to create them in side the data base. But eventually you're going to encounter issues. For example, if one product can be ordered many different times, then we need to store the many different records that represent each one of those product orders.
Where would we put that data? Well we can't put that data in the products table or define fields in the products table because there's only one record per product there. So in that case if a product can be ordered many times. Where would we have those many product records being ordered many times? Not in the products table, and certainly not in the invoices table either. So if we start duplicating products in the products table to be able to accommodate that, our data gets really messy and instantly unreliable. So the way to resolve the problem is create a table, where one record equals a product appearing on an invoice.
And that's what we've done here with this join table that we're calling invoice line items. Another area where you'll run into problems with many to manys is when you're creating reports. Let's say you want to create a report where you see a product, all the different invoices that it appeared on. And then, the next product and all it's invoices, and so on. In order to create that product. You'd need to have a table where each line in the report indicates a product appearing on an invoice. Unless you create this third invoice line items table. That report would be impossible to create. The concept of diagramming your relationships and certainly eliminating many to many relationships.
Certainly a lot to understand. But what we know so far, is that we've determined that we need a products table, an invoices table, a customers table. And now, this fourth table called invoice line items. So, we can start creating these tables in our FileMaker database. We've already created customers, invoices, and products. So now, based on our data modeling exercise, I'm going to add our fourth table which is called invoice line item. Now even if you have a small project, it might not seem like you need to sketch things out ahead of time.
But I cannot emphasize enough how important it is to make these decisions on tables and relationships. Before you start working with the data base. A lot of the reasoning there is because of the pitfalls you could encounter if you haven't taken something into consideration. You'll end up then having to pull out a lot of wires and deleting tables and relationships and fields and moving them all around. And all you're doing there is really creating a lot of instability in your database. So after a little bit of what is called data modeling. We have determined what tables we need to build into FileMaker. And we've even determined what relationships those tables are going to have with each other.
Now we can move out of our planning stage and move into the architecture stage inside the FileMaker application. This is where we'll define those table relationships. And in upcoming movies in this chapter, we're going to use a relationship graph right inside FileMaker Pro to establish. And then maintain the relationships in our file maker database solution.
- Creating databases from Starter Solutions
- Determining what tables you need
- Defining key fields and creating relationships between tables
- Creating fields
- Creating, duplicating, editing, and deleting records
- Importing and exporting data
- Managing layouts and layout objects
- Using the Layout Assistant
- Applying themes to layouts
- Finding records and working with found sets
- Building reports and charts
- Exploring calculations
- Creating and triggering scripts
- Working with relationships in scripts, calculations, and charts