Before we define our tables, I need to explain one more term: the Primary Key. The Primary Key, quite simply, is a column in a table that contains a unique value in every row. Every table in a database needs one of these - something that makes each row distinct. It's common practice to assign the Primary Key with an AutoNumber field (as records are added to a table, the database automatically inserts a unique numerical value for that field).
Now, back to defining our tables: Let's take a look at the data we want to include in our database (in case you've forgotten, we're building a site that will take appointments for my soon-to-be-profitable parallel parking business).
The whole point of this endeavor is to keep track of my appointments. To do this efficiently, there's a bunch of ancillary information I need to keep track of - all the who, when, what, and how much data. To make sure I'm including everything, I'm going to start the table-definition process by building one big table that includes all the information I'll ever need to keep track of my appointments.
appointmentID
clientFname
clientLname
clientEmail
carName
carSize
carRate
transmissonType
transmissionRate
slopeName
slopeRate
app1Time
app1Status
app1Cost
app2Time
app2Status
app2Cost
1
Evany
Thomas
evany@wired.com
Toyota Tercel
small
1
standard
1
flat
1
4/1/99 3:00 pm
reserved
120
2
Josh
Allen
josh@wired.com
Jaguar XJ6
large
1.3
standard
1
moderate
1.2
4/2/99 3:00 pm
open
100
3
Nadav
Savio
nadav@wired.com
Maxima
medium
1.2
automatic
1.2
friggin' steep
1.4
4/3/99 3:00 pm
open
100
4/7/99 3:00 pm
open
100
4
Nate
Manchester
nate@wired.com
Maxima
medium
1.2
standard
1
flat
1
4/4/99 3:00 pm
reserved
110
Glancing at this table, I bet you can see there are a bunch of anomalies. Don't worry - this table is just a sketch, a jumping-off point if you will, which we'll use to make sure we create an anomaly-free data structure. We do this by going through the table above and eliminating each anomaly, one by one. This process is known as normalization, which is actually a fairly formal, well-defined system - one you should get to know well if you're serious about creating databases.
The first step is to remove repeating groups of information and move each group out into its own table. Why should you remove repeating groups of information? The answer, quite simply, is because I said so. It's a rule. You will look both ways before crossing the street, you will cook chicken thoroughly, and you will separate repeating groups of data. (It's part of the anomaly removal process. Trust me.)
You may have already noticed the repeating data in the appointments area: See how Nadav has two appointments? Think about what would happen if he had three, four, or even 10 appointments. I think you can see where this is going, so let's separate these columns out into another table.
Now that we've removed the appointment data, we have two tables: one called Appointments, the other Clients. Notice I've given each a Primary Key.
clientID
clientFname
clientLname
clientEmail
carName
carSize
carRate
transmissonType
transmissionRate
slopeName
slopeRate
1
Evany
Thomas
evany@wired.com
Toyota Tercel
small
1
standard
1
flat
1
2
Josh
Allen
josha@wired.com
Jaguar XJ6
large
1.3
standard
1
moderate
1.2
3
Nadav
Savio
nadav@wired.com
Maxima
medium
1.2
automatic
1.2
friggin' steep
1.4
4
Nate
Manchester
nate@wired.com
Maxima
medium
1.2
standard
1
flat
1
appID
appTime
appStatus
appCost
1
4/1/99 3:00 pm
reserved
120
2
4/2/99 3:00 pm
open
100
3
4/2/99
reserved
100
4
4/3/99 3:00 pm
open
100
5
4/4/99 3:00 pm
reserved
110
"Hold the phone!" I can here you screaming. "Hold that phone just one gosh-darn-stinking minute. This isn't going to work at all. There are still anomalies all over the place." Well, you're very, very clever. We do, in fact, need to continue with project Anomaly Eradication.
The next step in organizing this data is to look at the information in terms of dependencies. I don't particularly relish the word dependency, but it is descriptive. Essentially, it means separating out information that is not dependant on the table's Primary Key.
Look at the Clients table above. You'll notice Toyota Tercel has little to do with any one person's email address. Let's separate car information out into another table called Cars. Once we give this table its AutoNumber Primary Key, it'll look something like this:
carID
carName
carSize
carRate
1
Toyota Tercel
small
1
2
Jaguar XJ6
large
1.3
3
Maxima
medium
1.2
If you look at the table we started with (at the top of this page), you'll see that the transmission and slope information should be treated the same way as the car data. So we'll have tables labeled Slope and Transmission, and each will have an AutoNumber Primary Key. And we should be good to go, right?
Wrong. There's still a sneaky anomaly in the Cars table. Think what would happen if we were to add another medium-sized car, say a Honda Civic Sedan. We'd have to enter the rate, which would be 1.2, just like the Maxima. Then what would happen if we wanted to update the rate associated with medium-sized cars? Hmmmm.
You see the anomaly, don't ya? Let's separate the car size and rate information.
OK. This has been one rough page. Read it again if you need to. Then do some stretching, limber up, get a can of your favorite cola, and come back when you're ready to fine-tune our new Car-size and Car-rate tables.
