Comparing flat vs. relational databases

- [Instructor] So what makes storing data in an application like FileMaker different than just storing your data in a spreadsheet? Well, there are so many different ways that you can store data on your computer, for example in a spreadsheet or even a word processing document, but these are examples of what we call in database circles a flat file. A database, on the other hand, is a collection of data that are organized into these things that we call tables. These tables provide a systematic way of 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 those at length later on in this course.

Now that flat file design that we talked about before puts all the database information into a single table or list, instead of compartmentalizing it into its own tables. Now although it's nice to have all your data in one place just like you do with databases, there are some complications with storage of that data in the case of a flat file. A flat file may contain many different fields, often with duplicated data that are prone to database corruption or confusion. And if at some point you decide to merge data between two flat files, what you're going to have to actually do there is copy and paste the data between the two files.

Version control can be an issue and sharing of your data is almost impossible in many cases if you're deciding to store it in a flat file. Spreadsheets, of course, are a common example of these types of flat files. They store data in different worksheets and often these different worksheets contain similar or redundant data. A relational database, however, incorporates multiple tables with methods for those tables to work together elegantly. The relationships between the table data can be collated, merged and displayed in database layouts all representing the data stored accurately.

FileMaker Pro is an example of a relational database system that can store the data just as easily as a spreadsheet, but in the case of FileMaker, it has so many different advantages for data entry as well as how to manage database integrity that gives it a distinct advantage over its flat file counterparts. Now to better understand the advantages of a database, let's imagine the needs of two small companies that take customer orders for their products. First, let's look at company A. They use a couple of different spreadsheets, the one that you see here on screen. They've got a spreadsheet that stores all their customer information and then they have a spreadsheet where they store all their order information.

When a customer places an order with this company, a new record will have to be created, which means a new row will be created in their order spreadsheet. Because company A only has one table of data, all the information pertaining to that order must be put into a single row. So for example, we'll have to enter in the customer name along with all the data that pertains to just the order. This 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.

If a customer places more than one order, their general information will need to be reentered and thus duplicated for each order that they place. So for example, the name Dennis Lambo is not only stored in the customers spreadsheet, as you see up here, but then it's duplicated as many times as Dennis Lambo places an order within the order spreadsheet. Whenever there's duplicate data like this, many inconsistencies may arise when users are trying to query the database or search for data. Additionally, if you change customer information like for example if Dennis Lambo wanted to change his first name, you'd have to go find all the iterations of Dennis Lambo in your order spreadsheet and change those manually as well.

This is what we call a data integrity issue. Conversely, if you're company B, they're much better off with a relational FileMaker database. As you see here, this is doing the same thing, storing all the customer information and all the order information. And when they create a new order, instead of having to duplicate all the information, what we do is we link to the information in another database. And since each customer has one and only one record in the database, it's easy to find them and make changes if we need to.

And the best thing about it is that you don't have to go and do any manual changes. Because all of this information is linked together through relationships, you'll see that all related data is then propagated so that you don't have to worry about things like data integrity issues. The relational database approach to entering and managing data solves problems of duplicate data and also the problems that come with changing customer information. Frankly, this is the reason that related databases were created in the first place back in the '70s. So if you're in charge of managing your company's information, you need to only change the data in one spot and then it gets propagated appropriately throughout all the different places where that data is found.

In addition, since relationships between tables and FileMaker follow the 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 these data integrity errors. So the benefits of using a related database system over flat file counterparts are obvious. We're going to see various different examples of this as we go through the course, but it's important to know the advantages of having your data stored inside a FileMaker related database because your data is then going to be related within the different related tables.