Hands on: Relationships in databases

One of the joys of writing about databases is the number of places you get to visit; the downside is the travelling.

But occasionally it yields lighter moments. I recently stayed at the Renaissance Hotel at Gatwick and while I was settling the bill I noticed that a tip I had added for a meal had been increased.

“Oh,” I was told. “That’ll be the new computer system. We had a lot of work done and the new server sometimes just adds random numbers to the bills.”

Right, excellent. I must remember that explanation next time I have to account for errors in one of my databases.

Tables
One of the central tenets of relational databases is that we split data into multiple tables. There is a formal process (called normalisation) for working out what data should go into each table, but there is also a good rule of thumb. Simply identify each real-world type of object that you want to store data about (Customer, Order or Employee), give each one a table and include in each table only the fields that are relevant for that object. For example, in an Employee table we might make fields such as FName, LName and DOB (Date Of Birth).

The table must also have a primary key. This is usually one field and must contain a unique value for each record. So, our three tables might look like the image (above, left): the primary keys in each case are the first column in the table.

Primary keys are significant because we use them to link the data in the separate tables together. If you look at the Order table you can see that Order 1 was placed by customer 2 (Sally Jones) and taken by employee 3 (Harry). The column that points to the primary key (for example, the CustID field in Orders) is called a Foreign key.

The good news about splitting the data into separate tables is that we never store the same data more than once. So, for example, the date of birth of each employee is only ever stored once. We always know where to find it: it has to be in the Employee table. But how can we get to it? For example, how do we list all of the orders, together with the DOB of the associated employee?

Simple, we use the primary and foreign keys to pull the data back together. In this case we could create a query (You’ll find the OrderEmployeeDOB in DBCFeb08. mdb here) that links them like this and it would return the correct information.

In these examples I’ve used a number field to make the joins, which is common, but there is no reason why you cannot make a join between two text fields. If you do so, then the join is case insensitive, so it will happily join “Harry” with “harry”. Most of the time this is what we want, but occasionally people want a case-sensitive join.