Here's your definition: A relational database stores data in one or more tables, and these tables can be joined in a variety of ways to efficiently access the information.Assuming that definition still leaves you with a question or two, let me give you an example. Say my database contains two tables: The first is named Clients, the other Cars.
| clientID | fname | lname | email | carID |
| 85 | John | Doe | jdoe@whatever.com | 45 |
| carID | carname | carrate |
| 45 | Buick LeSabre | 1 |
Given the information above, we can determine the name of the car owned by John Doe and the rate for that car by joining these tables at the carID field. The joined table would look like this:
| clientID | fname | lname | email | carID | carName | carrate |
| 85 | John | Doe | jdoe@whatever.com | 45 | Buick LeSabre | 1 |
Just so you know, the language that allows us to join these tables is called SQL (structured query language), which we'll be covering in more detail in Lesson 3.
OK. You may wonder, why is this joining necessary? Why not simply put the carName and rate in the same table with the client information? Well, there just so happens to be an excellent explanation for that. To see what I mean, let's try it your way first:
| clientID | fname | lname | email | carName | carrate |
| 85 | John | Doe | jdoe@whatever.com | Buick LeSabre | 1 |
| 86 | The | Monkey | admin@electric-type.co.uk | Ford Pinto | 1.2 |
| 87 | Another | Monkey | anothermonkey@wired.com | Buick LeSabre | 1 |
There are two major problems here. First, look at the rate for the Buick LeSabre. Say I realized Buicks were harder to park than I first bargained for, so I decided to raise the rate I charged from 1 to 1.1. Thanks to the way this table is constructed, I'd have to go into the records for both client ID 85 and 87 to make the change. This may not sound like a big deal, but imagine how heinous a job it would be if this table had 25,000 records instead of just three. If there were two tables, however, one for clients and one for cars, I'd only have to change the rate information once. To apply the updated rate information to the appropriate records, I'd simply join the tables.
There's another problem here. Look at the record for client ID 86. Let's say I find out this guy's a deadbeat and I want him expunged from the database. If I do that, I'm going to lose information not only about this client, but about the car as well. Again, if we divide the information into two tables, this won't happen. Sure, there would be a record for a car that wasn't in the Clients table, but that's fine. What's more important is that we're prepared for the next client who comes along with a Pinto.
Now, are you ready for some abstruse language - the kind you can use to intimidate those who haven't read this article? I thought so.
This first problem, where the same information would have to be changed in more than one record, is what those in the know refer to as an "update anomaly." The second problem is, as you may have guessed, a "deletion anomaly."
Finally, there is a third type of anomaly. Let's say we add a row of data to the table:
| 88 | Greatest | Primate | simian@wired.com | Pinto | 1 |
If we want to change the rate for Pintos, we have to go in and change both Client ID 86 and 88. It's the same problem we uncovered with the Buick update anomaly, but we may not have noticed it until we added this row. When you add data that reveals an anomaly, it is an "insertion anomaly."
When we set up our data, we want to avoid these three kinds of anomalies at all costs. This may sound easy, and in the examples to come, it may even seem easy. But in the real world, when you have to deal with complex data, you'll soon come to understand what a royal pain this can be. Often you'll find yourself well into a project before you notice one of these buggers staring you right in the face.
Now that we have this "no anomaly" goal tucked away in the backs of our minds, let's go ahead and define our tables.
next page»
