Why Multiple Tables?

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).

Table 6-1: An Employee table (without the relational model)

ID	Name	DeptID	DeptName	Loc
6000	Anderson	10	IT	Houston
6001	Andrzejewski	20	Accounting	Dallas
6002	Sims	30	Sales	Austin
6003	Burris	20	Accounting	Dallas
6004	Tull	10	IT	Houston
6005	Jensen	30	Sales	Austin

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.

Note

A byte is an abbreviation for binary term, which is a unit of storage capable of holding a single character. On almost all modern computers, a byte is equal to 8 bits.

A bit is an abbreviation for binary digit and is the smallest unit of information on a computer. A single bit can hold only one of two values: 0 or 1. Meaningful information (such as a single character) is obtained by combining consecutive bits into larger units.

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.

Table 6-2: An Employee table (within the relational model)

ID	Name	DeptID
6000	Anderson	10
6001	Andrzejewski	20
6002	Sims	30
6003	Burris	20
6004	Tull	10
6005	Jensen	30

Table 6-3: A Department table (within the relational model)

DeptID	DeptName	Loc
10	IT	Houston
20	Accounting	Dallas
30	Sales	Austin

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.