Skip to main content

Choosing Between Flat and Relational Databases

Choosing Between Flat and Relational Databases
Unlike ice cream, databases come in just two flavors: flat-file and relational. Also unlike ice cream, it’s not really a matter of preference as to which one you choose. Some databases require a relational approach; others would be overwhelmed by it. Read on to figure out how to tell the difference.



Isolationist tables

In a flat system (also known as a flat-file system), all the data is lumped into a single table. A phone directory is a good example of a flat-file database: Names, addresses, and phone numbers (the data) are crammed into a single place (the database). Some duplication occurs — if one person has three phone lines at home, his or her name and address are listed three times in the directory — but that’s not a big problem. Overall, the database works just fine.

Tables that mix and mingle

The relational system (or relational database) uses as little storage space as possible by cutting down on the duplicated (also known as redundant) data in the database. To accomplish this, a relational database splits your data into several tables, with each table holding some portion of the total data.

Borrowing the preceding phone book example, note that one table in a relational database can contain the customer name and address information, whereas another can hold the phone numbers. Thanks to this approach, the mythical person with three phone lines has only one entry in the “customer” table (after all, it’s still just one customer) but has three distinct entries in the “phone number” table (one for each phone line).

The key to relational databases

The key field (or linking field) is the key to this advanced technology. All related tables in a relational database system contain this special field. The key field’s data identifies matching records from different tables.

The key field works just like the claim stub you receive when you drop off your dry cleaning. To pick up your dry cleaning when it’s finished, you present the claim check, complete with its little claim number. That number identifies (or links) you and your cleaning so the clerk can find it.

Likewise, in the phone book example, each customer can have a unique customer ID. The “phone number” table stores the customer ID with each phone number. To find out who owns a phone number, you look up the customer ID in the “customer name” table. Granted, it takes more steps to find someone’s phone number than it does in the plain flat-file system, but the relational system saves storage space (no more duplicate names) and reduces the chance of errors at the same time.

If this process seems complicated, don’t feel bad. Relational databases are complicated! But that’s mostly behind the scenes, where Access is doing the stuff it does when you make a selection in a tab or ask it to run a wizard for you. A good deal of the complexity is invisible to you; all you see is the power it gives you. When you’re ready to find out more about all that behind-thescenes stuff, check out Chapter 4.

But do your tables need to relate?

Now you at least have an idea of the difference between flat-file and relational databases. But do you care? Yes, you do. Each approach has its unique pluses and minuses for your database:
  • Flat-file systems are easy to build and maintain. A Microsoft Excel spreadsheet is a good example of a flat-file database. A list of records is stored, one record per row, and you have as many records as can fit on the worksheet. Simple, easy, and in many cases, the way to go — if your database is simple and easy, too.
  • Relational systems shine in big business applications such as invoicing, accounting, or inventory. They’re also a big help if you have a small business — your customer data, for example, could require several tables to store customer names and addresses, purchase history, and credit information. Storing everything you need to store about customers could be too big a job for a single, flat-file database.

Although Access is a relational database program, it does flat-file systems quite nicely because even though it lets you set up several tables and set up relationships between them, it’s also quite happy to set up a single flat-file table if you want one. Whether you choose flat-file or relational for your database project, Access is the right program.
Labels:

Comments

Post a Comment

Popular posts from this blog

Access Field Types and Uses

A field, you remember, is where your data lives. Each field holds one piece of data, such as Last Name or Batting Average. Because there are so many different kinds of information in the world, Access offers a variety of field types for storing it. In fact, Access puts the following field types at your disposal: Short Text Long Text Number Currency Date & Time Yes/No Lookup & Relationship Rich Text Attachment Hyperlink OLE Object Calculated There’s also an Autonumber field type, which is applied automatically to the first field in a new, blank database. The types just listed are those available for fields you create in addition to that first field — the ones that will contain your data. For now, suffice it to say that the Autonumber field is a field that contains an automatically-generated number so that each record is unique in that it has a unique autonumber, or ID. You get the word about the need for (and ways to create) unique fields later on, in Chapter ...
Post a Comment
Read more

Building a Database in Access

So you’ve read a few posts here at the beginning of the blog, maybe you’ve leafed ahead where I’ve referred to other chapters, and now you feel ready. You want to dive in and start building a database. Keeping in mind my previous advice to take it slowly, you can take a whack at it here. In the following procedure, you set up a new database and then use the Table Wizard to build the first table in the database. Ready? Here we go . . . 1. If Access is not already running, take a moment to start it. Chapter 1 shows you how to do this. In the Access workspace, a series of large template icons appears, below a Search for Online Templates box, accompanied by links to likely searches for templates that store Assets, Business, Contacts, Employee, and so on. 2. Click the Blank Desktop Database icon. A Blank Desktop Database dialog box appears, as shown in Figure 3-1. Figure 3-1: New blank databases need names. Give yours one here. 3. Type a name to replace the generic Databa...
1 comment
Read more

Databases with user forms

When you’re planning your database, consider how the data will be entered: If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record. Figure 1-1 shows a table of customers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view. You may want to use a form (shown in Figure 1-2) instead. A form is a specialized interface for data entry, editing, and for viewing your database one record at a time, if Someone else will be handling data entry Typing row after row of data into a big grid seems mind-numbing Figure 1-1: Datasheet view ...
1 comment
Read more