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.
Comments
Post a Comment