In this section we will really explore the staple that makes a database work: the relationships that are established between the different tables of data. When designing a database, this is the most challenging step and often the place where most of the confusion with databases arises. Fortunately, databases are nothing more than tables of data that are related. Consider the Order Details table in the Northwind database. The table contains over 2100 records! But no matter how big a table might be in a computer, you can easily draw any table using a piece of paper and a pencil. Consider the following simple database:
You have a table of employees, and a table of expenses that have been made by your employees. As time goes by, the Expenses table will grow to become quite large. Imagine that you want to make a query that will find out the phone number of every employee that submitted an expense. It is impractical to place the employee phone number in the same table as the expenses. It will create a lot of extra data that is not really needed if there is another table containing employee data elsewhere. The relationship between the two tables is created because of the two common fields, EmployeeID. In particular, the Employees table and the Expenses table are in a “one-to-many” relationship, meaning that one entry in the Employees table can relate to many entries in the Expenses table. Let’s examine the relationships
in the Northwind sample database. You can view the relationships by
clicking the Relationships button (
A lot of the action happens around the central Products table in this database. A product contains many attributes, in particular a SupplierID (who makes/sells the product) and a CategoryID (what type of good a particular product fits into). The Suppliers and Categories tables are in a one-to-many relationship with the Products table. SupplierID and Category ID also happen to be primary keys, and as such are in bold text in their respective tables. The Products table also has a relationship with the Order Details table. One product that Northwind sells has the potential to be sold many times, therefore each sale of each product is logged in the Order Details table. The Products table is in a one-to-many relationship with the Order Details table. Before we learn how to create or remove relationships, there is one more important thing to note. We know that bold field names indicate primary keys. We also know that primary keys cannot be duplicated in a table, and primary keys cannot be empty. So why does the Order Details table have two bold field names? Because it has two primary keys! Open the Order Details table in Design view – notice the two small key icons beside OrderID and ProductID?
Now look at the table in Datasheet view:
Hold on a second – some of those Order ID’s are the same – how then can Order ID be defined as a primary key? Because the Product ID is also a primary key. Note that you can’t see the actual Product ID’s in the Datasheet view, but it is there. This is because the product name appears as a lookup value; meaning the Product ID field has been replaced with a combo box.
Each entry in the combo box corresponds to a unique Product ID. In particular, “Alice Mutton” is ID # 17, “Aniseed Syrup” is ID # 3, etc. (Check the Products table!) We will cover lookup values later in this manual. Back to our two primary keys in the Order Details table. The first three entries in the Order Details table are obviously not unique, even though this field is a primary key:
But when combined with the next field, namely the Product ID, you end up with three unique entries in the Order Details table if both fields are established as primary keys:
So what does the data in the
table actually mean? Well, Order ID # 10248 consists of the purchases of
these three products. You can verify this by clicking the Report object
in the database window and double-clicking the Invoice object. Click the
(
Sure enough, Order ID # 10248 contains the three items listed in the Order Details table. Now that we have covered the complex issue of how relationships work, actually creating the relationships is very simple if the fields in your tables have been well planned. Imagine for a moment that Northwind Traders has just opened, and has not sold anything yet. The relationship between the Products and Order Details tables does not exist, but because of the design of the Order Details table, we can link the ProductID from products to the Order Details table. To establish a relationship between the two tables, simply click and drag the ProductID listing in the Products table on top of the ProductID listing in the Order Details table:
When you release the mouse button, Access will show the Edit Relationships dialogue box:
Access has determined that the style of this relationship is one-to-many, based on the data that was collected from the drag and drop operation. Before clicking the Create button, you should click the Enforce Referential Integrity check box. Referential Integrity is a set of rules and conditions that make data entry into databases safer. Access (as well as most other database programs) tries to enforce referential integrity whenever possible. It insures that all related fields are valid when considered together in a database, and prevents you from accidentally deleting related data. To make referential integrity work, the following three conditions must be satisfied:
Since all of the necessary conditions have been satisfied, click Create to establish the relationship:
When deleting a relationship, remember that doing so can have a big impact on how the database works. Make sure you actually do need to remove the relationship! Deleting a relationship in the Relationships window is easy, just right-click on the relationship you want to remove and click Delete:
Access confirms that you want to delete the relationship, click Yes to confirm. |
) on the
Database toolbar or by clicking Tools 
) button
to go to the very first order:
