Join Cris Ippolite for an in-depth discussion in this video Why relational databases?, part of FileMaker: Relational Database Design.
- [Instructor] So what makes storing data in an application like FileMaker different than just storing your data in a spreadsheet? There are many different ways that you can store data on your computer, for example in a spreadsheet or word processing document, but those are examples of what are called in database circles a flat file. A database on the other hand is a collection of data that are organized into different tables. These tables provide a systematic way of accessing, managing, and updating data. A relational database is one that contains multiple tables that all relate to each other through special key fields and we'll be talking about that concept later in this title.
And a flat file design puts all the database into one single table or list and still has fields that represent all the different parameters, however there are some issues with that type of flat file storage. Flat files may contain many fields, often with duplicated data that are often prone to database corruption or confusion and if at some point you decide to merge data between two flat files you'll need to copy and paste the relevant information from one file to another. Spreadsheets are a common example of these types of flat files. They store data in different worksheets and often those different worksheets contain similar or redundant data.
A related database however, incorporates multiple tables with methods for those tables to work together. The relationships between table data can be collated, merged, and displayed in a database layout all representing the stored data accurately. Now FileMaker Pro is an example of a relational database system that can store the data just as easily as a spreadsheet but it has many different advantages for data entry and integrity over its flat file counterparts. To better understand the advantages of a database let's imagine the needs of two small companies that take customer orders for their products.
Company A takes all their orders and manages all their customers in spreadsheets. So you see where they've got a spreadsheet here where they manage all their customers and they've got a spreadsheet down here where they put all the order information in there. When a customer places an order with this company a new record or a new row will be created in the order table. Because Company A only has one table of data, all the information pertaining to to that order must be put into a single record. So the customer first name, last name, order date, and so on. This means that the customers general information, in this case their first and last name, are stored in the same record as the order information.
If a customer places more than one order then their general information will need to be registered again. For example, here, with Francis Schrodinger. Whenever we have redundant data like this many inconsistencies may arise when users are trying to query the database or search for this type of data. Additionally, a change in customer information like for example if Francis Schrodinger gets married and changes her last name, could cause some chaos in the order table. But conversely, Company B manages all their information in one single FileMaker file.
As you see here I have a relational FileMaker database that stores all the customer information, much like a spreadsheet does, but it also has related order information. So instead of having to duplicate the data every time I create a new customer record, instead what I do is simply just pick one of my existing customers and all the related data is linked. Not duplicated but linked to its original record. Each of the customers has only one record and one single point of truth. There's just one area where their information can come from.
That way if I change the last name of one of my contacts, it will immediately get propagated to every area where it's been shared throughout our system. This approach to entering data solves problems of duplicate data and also the problems that come with changing customer information. The database manager needs only to change the data once in the customer record if someone decides to update their info. In addition, since the relationships between the tables follow the business logic of an organization using the database it makes tasks like sharing, retrieving, or even reporting on the data much easier, more stable, and efficient because they're all happening inside one platform.
And also since there's no limit to the amount of tables that you can have in a FileMaker application, you can have tons and tons of different data all centralized in one location giving you the ability to dashboard or analyze this information for business intelligence purposes and we're going to see various different examples as we go through this title but it's important to know the advantages of having your data stored inside a related database like FileMaker instead of a flat file.
This course applies to versions of FileMaker Pro from 16 through the most current version.
- Diagramming your relationships
- Problems caused by many-to-many relationships
- Adding a join table
- Defining primary and foreign key fields
- Working with relationships and queries
- Creating child records
- Using advanced relationship techniques