Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)

As stated in the introductory paragraphs, a relational database separates information into multiple tables.

"So why break database information into multiple tables?" you might ask. "Why not contain everything in one table, like a spread-sheet? Isn't that a lot less complicated?"

To understand why relational databases use multiple tables, consider the following Employee table (see Table 6-1).

In Table 6-1, notice that there are the following:

• Six employee records

• Two employee records for each department

Looking at this table, you might notice a lot of duplicated information. For example, there is duplicate information in the Dept Name and Loc fields.

So what's so bad about duplicate information?

Well, for one, a lot of unnecessary memory is used because of this duplicate information. In Table 6-1, IT, Accounting, Sales, Plano, Austin, and Dallas are all records used twice. Six extra records might waste about 28 bytes on a database server. Now that doesn't seem so bad, does it?

But what if you have 5,000 employee records stored within this Employee table? Before you know it, your duplicate records might add up to about 13MB of wasted memory. Or worse.

Understanding the Relational Model

If you split the data shown in Table 6-1 into two different tables (for example, a Department and an Employee table), you'll improve the efficiency and maintainability of your database. Tables 6-2 and 6-3 summarize how a database might separate the information from Table 6-1 into two separate tables.

In the world of relational databases, these multiple tables are joined (or linked) through a common field. This common field must contain identical information for both tables. In Tables 6-2 and 6-3, this common field is DeptID.

The following advantages occur by separating data into multiple tables:

• Relational databases are easier to maintain. For example, if a particular department (say, the Accounting department) moves from Dallas to Houston, all you would have to do is change one city in the Department table (you would change the Loc field for DeptID 20 to Houston).

  In a table that does not follow the relational model (such as the one displayed in Table 6-1), you would have to update all the individual employee records for those who worked in that department.

• Relational databases remove duplicate information (also known as data redundancy). Instead of duplicating the department name and location for every employee record, the new tables display the department name and location once. This conserves the server memory that a database uses.

The xtreme.mdb database is based on the relational model. Thus, you'll find multiple tables within this database, all linked together to reduce data redundancy. This method of organizing data in the least redundant manner is better known as database normalization.

Linking Tables

As you may already be deducing, there's a hard truth about report writing. Mainly, the data you need for your reports is probably not available within a single table, but rather a number of tables. When you correctly link two or more tables together, all the data in those tables is available for your reporting purposes. The link matches up the records from one database table with those from one or more other database tables.

And how does one know which tables and fields to link within a database? That's where your database documentation comes in.