The Differences between Flat and Relational Access Databases

In the first article in this series, we discussed the basics of Access 2007 databases in relation to the other core programs in the Microsoft Office 2007 suite. Unlike Excel, which is like a two-dimensional database, Access is a multi-dimensional application for storing, retrieving, and serving information in both online and offline contexts.

In the second article, we delved deeper into the advantages of using Access 2007 over other programs. We discussed the basics of databases such as tables, fields, and records and covered some other pertinent topics leading up to the information in this article. We also discussed the nature of data and the types of data that a database can store.

In general, databases (not just those created with Access 2007) fall into two major categories. These two categories are referred to in this article as Flat databases and Relational databases. The remainder of this article is dedicated to discussing these two types of databases and the appropriate time to choose one over the other.

Flat databases are similar to the two-dimensional databases discussed in the previous article in this series. In this type of database, all of the data is contained in just one table. The contact information example in the last article is a good example of a Flat database.

Flat databases are like Excel worksheets because the data doesn’t need to extend beyond one worksheet (or table). With a Flat database, you are not able to take advantage of many of the reasons why you are turning to a database in the first place.

Still, Flat databases are useful when you have only one type of data to store and retrieve. Some small websites use Flat databases to serve only one type of dynamic to website visitors. They have no need to use a database that extends beyond two dimensions.

click to enlarge

Relational databases use a minimal amount of storage because they typically do not contain duplicate data that a Flat database often does. Take our contact information example from the last article. Suppose you had a contact in your database that had more than one address. You would either have to choose which address to include in your Flat database or have multiple “address” fields such as “address 1,” “address 2,” and “address 3.”

The problem with this approach is that you may eventually have contacts with lots of new addresses necessitating constant additions of an “address” field to accommodate the one contact with the most number of addresses. In this way, many of your contacts would have empty fields making for a messy and larger-than-necessary database.

This is where a Relational database becomes useful. Using multiple tables, you can create one that contains just the names of your contacts and a completely different table that contains the addresses. In this way, you no longer have to add fields to the one table in a Flat database every time someone adds an address to their contact information.

To make Relational databases work, all tables in relation to one another use a unique identifier called a Key. This Key field data identifies records that match from multiple tables. In our example above, there needs to be a key field that matches the name from one table to the multiple addresses in the other.

Luckily, Access 2007 handles many of the most powerful database functions all by itself. Relational databases are indeed complicated but many of these complications are hidden from the user. These are the behind-the-scenes functions and calculations that make Access 2007 so powerful and yet so accessible to the beginning and intermediate user.

There is no doubt that Access 2007 is a powerful database application. Only a few of its powerful features have been discussed in this series so far. When you start designing your database, the first question to ask is if you need to take advantage of a Relational database or whether a Flat database will suffice. If you are reading this article, you are likely a beginner with databases and Access 2007. You will find that starting your learning of Access 2007 will proceed must more smoothly if you begin with a Flat database. When you have mastered the basics, move on the more powerful Relational database to advance your learning.