Join Cris Ippolite for an in-depth discussion in this video Why relational databases?, part of FileMaker Pro: Relational Database Design (2011).
- Every database is a collection of data which are organized into files called tables. These tables provide a systematic way of accessing managing and updating the data stored within them. A relational database is one that contains these multiple tables, but the data in the tables are related to each other using special key fields. Conversely, a flat file database is a database that's designed around a single table. The flat file design puts all the database information to one table, or list, with fields that represent all the different parameters.
Spreadsheets are a common example of flat files. They store data in different worksheets and often these different worksheets contain similar or redundant data. A relational database, on the other hand incorporates multiple tables with methods for these tables to work together. The relationships between table data can be collated, merged, and displayed in database forms or layouts in the case of FileMaker all representing the stored data accurately. FileMaker Pro's an example of a related database system and can store the data just as easily as a spreadsheet but with data entry and integrity advantages over its flat file counterparts.
To understand the advantages of a related database imagine the needs of two small companies. One company is using a series of spreadsheets like the ones I have on screen. You'll see I've got one flat file storing just customer information, and another flat file storing order information. Each time an order is placed with this company they'll have to create a new row in their orders table and this means that customer's general information for example, the first and last name in this case need to be stored in the same table as the orders. If a customer places more than one order for example, in the case of Dennis Estrello we have to re-enter his first and last name for each order.
And wherever there's duplicate data, as in the case here many inconsistencies may arise when users try to search within this database. Additionally, any change, for example, in the change of the last name of one of these customers would require the manager of this database to find all the different rows inside the orders table and change that name manually each time. Let's say another organization has the same information but instead they're using FileMaker Pro. I've got an example of a FileMaker database that stores all the same information, customers and orders, and each of the customers has one and only one record of general information stored inside the customers table.
You can see those different records as we navigate through them. When the customer places an order, we create a new record, but what we do is we pull in related information from the customer table. This approach to entering data solves problems of duplicate data and also makes making changes to customer information a lot easier. So now I can just go into Customers and if I wanna change someone's name or their title or email, I just change it in one spot and then it propagates appropriately through all the related order records, where it's being used.
In addition, since the relationships between tables follow the business logic of the organization using a relational database, it'll make tasks like sharing, retrieving and reporting on data much easier and more efficient while minimizing data integrity errors. We're gonna see various different examples of this as we go through the title, but it's important to know the advantages of having a database like FileMaker Pro, because your data is gonna be related. This is what we call a related database system.
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.