Join Cris Ippolite for an in-depth discussion in this video Compare flat vs. relational databases, part of FileMaker Pro 15 Essential Training.
- [Voiceover] So over many years of working with customers and their data we often work with customers that are so used to working with spreadsheets that it's difficult for them to understand how to break free from some of that spreadsheet abuse. So what makes storing data in an application like FileMaker different than just storing your data in a spreadsheet? While there are many different ways that you can store data on your computer, for example in a spreadsheet or in a word processing document, but those are examples of what we call in database circles a flat file. A database on the other hand is a collection of data that is organized into different tables and these different tables provide a systematic way of accessing, managing, and updating the data.
A relational database, like FileMaker, is one that contains multiple tables that all relate to each other through special keys. Now we'll be talking about these later on in this course, but they provide an advantage over flat file. A flat file puts all of the database information into just one single table or list, and it still has fields that represents all the different parameters, but there are some issues with flat file storage. A flat file could contain many different fields however they're often duplicated and prone to database corruption or confusion.
Now at some point you may decide to merge data between flat files, for example, but then you'll need to copy and paste all the relevant data from one table to another. Duplicate management is also quite a big issue when it comes to flat file data management. Spreadsheets are common examples of these types of files. They store data in different worksheets and often use different worksheets to contain similar or redundant data. A relational database however incorporates multiple tables with methods for those tables to work together. The relationships between the tables can be collated, merged, and displayed in database layouts all representing the data stored in an accurate fashion.
Relational databases will also eliminate the redundancy, thus significantly reducing the risk for data error. FileMaker Pro is an example of a relational database and it 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. Now to better understand the differences between storing your data in spreadsheets, versus storing your data in a relational database let's take a look at two companies. Company A chooses to store their customer and order information in spreadsheets like the ones you see here on screen, and we'll look at Customer B, who decides to store their data in relational databases.
Now you'll notice that we've got two spreadsheets here one with customers, the one on the top, and one with orders, the one on the bottom. Now every time Company A creates a new order they have to go in and create a row in the spreadsheet on the bottom. And each time they create an order and add a new row they have to enter in all the information. The customer's first name, customer last name, order date, and order total. And because Company A only has the one table of data for orders, all the information pertaining to that order must be put into a single record. So that means that the customer's general information, in this case their first and last name, is stored in the same record as the order information, but it's also stored in the customer table.
It doesn't seem like a big issue and this is just a very small example of that, but what happens for example if Pamela Dreyfus, who exists twice in the order table, and once in the customer table decides to change her last name. Well now some user has to go in and find every single instance of the Pamela Dreyfus record and make those changes. But also what if they misspell one of the Pamela Dreyfus records when they're manually entering in the new order record? Well all of that additional risk, although is very small in this one example, when compounded across data and hundreds of records and thousands of different attributes that are stored in our data can significantly increase the risk and compromise the integrity of the data that you're storing.
Not to mention that it makes retrieval very difficult and sharing almost impossible. So now conversely let's look at Company B. Company B is using a relational database in this case FileMaker. As you see here, I have a relational database set up that stores not only order information, but customer information and even my products all in one place. And when we're creating a new order all I have to do is link to one of the existing customer records, and all of the information gets pulled over from the customer table.
So this way the data doesn't actually live in the order table I'm just referencing it from the customer table. So that way if I had to go back and make a change to one of my customers I could change my address or phone number or any of these attributes in one spot and it would be propagated to all the related orders, thus eliminating any duplicate entry issues and increasing the integrity of the data that's stored in the database. Each of these customers is uniquely identified by an ID. You see this customer ID here. Instead of linking the records in the customer table and the records in the order table by the name of a customer we're doing it by a numeric value that can never be duplicated, and thus eliminates a great deal of risk when managing our data.
This approach to managing data in a relational database solves problems of duplicate data and also the problems that come when changing customer information. Database manager needs to only change the data once in the appropriate table if someone decides to update their info. In addition, since the relationships between the tables follow business logic of an organization using the database it makes tasks like sharing, retrieving, or even reporting on the data much easier, stable, and more efficient while minimizing data integrity errors. FileMaker allows you to take all your data put it into one place, lock it down if you need to, share with whomever you want, create customized user interfaces, all while increasing the integrity of your data and minimizing data risks.
- 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