Using tables for Reference Purposes

Establishing Reference between Tables Exercise

Introduction

In a properly designed database, data in one table may refer to complimentary data in another table. As an example, in the Book table we have a field called pubcode. This field contains the publisher code for the publisher who has published the current book. In order to maintain data integrity we want to ensure that any publisher code value entered into the pubcode field of the book table will already exist in the publisher table as the key field. By ensuring this we maintain the data integrity of the books and authors since the system will not allow us to enter any publisher code that does not already exist in the publisher table.

Note: What if the publisher of a book we are entering does not exist in our publisher table?

Answer: Enter the publisher’s details first into the publisher table and then enter the book’s details.

Establishing Reference between two Tables

Go to top

Fig 1

In Fig 1 above we see the Book table in design view, with the pubcode field selected. In the section where we specify field properties, we select the Lookup tab. Here the Display Control property is set to Text Box by default. We alter this to Combo Box.

Once we do this the display will alter to that shown below in Fig 2.

Fig 2

Here the Row Source Type property has changed to Table/Query. This is what we want for our case and thus we leave it alone.

In the Row Source property, we have a drop down list, which gives us the tables in our database. Since we want to cross-refer to the Publisher table, we select that from our list.

Fig 3

Now that we have selected our reference table, i.e. Publisher, we now tidy up a few more properties.

The first property we look at is Bound Column. The default value for this is 1 – which is the value we want in this case. The value 1 tells the system to use the first field in Publisher as a reference for pubcode in the Book table. The result of this reference is that the system will not allow any value to be entered into the pubcode field of the Book table unless it already exists in the first field of the Publisher table. This means that when entering data into the pubcode field of the Book table the system will provide us with a drop down list that will display all of the field values in the first field of the table Publishers – in other words all of the publisher codes.

Key fields such as the publisher code are very efficient in maintaining data integrity in a database. However, they are not very human friendly. For the human doing data entry names like ‘Penguin’ or ‘Heinemann’ are far easier to remember than a code such as 3682157998. This is where the property Column Count comes in. Initially set to 1, it has been now set to 2. This means that when entering data into the pubcode field a drop down list will give us both the first and second field in the Publisher table. Result is that the data entry person can see both the publisher code and the publisher’s name. This greatly reduces the risk of assigning a book to the wrong publisher due to two publisher codes similar to each other.

Fig 4

Fig 4 above show what the drop down list looks like once Column Count has been set to 2. It is a vast improvement on having only numeric codes for the publisher because now we can see the actual names beside the codes.

Despite greater level of user friendliness, the two columns in the drop down list makes it look messy. To remove this messiness we make one more alteration to the properties of our reference – the Column Widths property.

Fig 5

Fig 5 above shows the Column Widths property with the strange-looking value of 0cm;8cm. What this means is that the first column – the publisher code – will be zero centimeters wide, or in other words that it will be invisible, while the second column will be 8 cm wide. The result is that it will only show us the name of the publisher. This is demonstrated in Fig 6 below.

Fig 6

Exercise

Go to top

  1. Use Lookup on the Author Id field of the AuthorBook table to ensure that any values entered will already exist in the Author ID field in the Author table

  2. Use Lookup on the isbn field of the AuthorBook table to ensure that any values entered will already exist in the isbn field in the Book table