Peck Tech Designs - Introduction to Relational Databases

Click below to see
some references:

Introduction to Relational Database Design

Let's begin with an example.

The Problem

One of our customers has clients throughout the country, who are serviced by local sales reps.

Periodically our customer sends out mailings on behalf of their sales reps, to each sales rep's clients, so they print the sales rep's name and address on the letters instead of their own.

Their original programmer put the sales rep's name and address with each client in the client table to make printing these letters easier, so part of their client table looked like this:

Here's an example of what the data looked like:

Perhaps you can see the problem they had: Every time a sales rep changed his address, they had to re-type his address as many times as that rep had clients - and some of them had over 20!

Obviously, this was a terrible waste of time, and it was just asking for errors.

How we fixed the problem

First, we set up a new table for sales reps, and put the information for each sales rep in just once:

Note that we also told the database program to assign a "unique identifer", or "ID", to each sales rep, called a "RepID".

Once this had been done, we were able to remove all the sales rep information from the clients table and replace it with just the unique RepID:

The clients table is now half the size it was!

And, thanks to the built-in features of modern "relational" databases, our customer can still print the sales rep's name and address on their letters. Using the RepID, the printing program simply "looks up" the sales rep information it needs from the Rep table.

As is standard procedure, we also added a "ClientID" to the clients table. Here are both the new tables together, so you can see better how it works:

What does "relational" mean?

Here's what the new "table design" looks like.

Note the lines drawn between the "RepID" field in both tables. This indicates a "relationship" between the two tables.

It means that to get the correct sales rep information for any particular client, the system should use the information for the RepID stored for that client.