Table Relationships

Instructor: Charles Ott

Lecture Outline

Text: pp 48-52. Some of this material is also covered in Chapter 1.

Other introductions to relational databases: How Stuff Works, Database Journal.

1. Table Relationships.
2. Referential integrity.
3. Creating SALEORDER and ORDERLINE tables, and the general topic of many-to-many relationships.

Foreign Keys

A "foreign key" is a pointer in one table that points to a record in another. The foreign key always points to the primary key in another table (that is, the "ID" field). The foreign key links two tables together, and it always means that these two subjects have some relationship in real life.

For example, in real life, a Product is usually made by a Supplier. Therefore, a Product table probably has a field called "SupplierID". The number in that field is the SupplierID of whatever company supplies that Product. In other words, the foreign key says, "If you want to know who supplies this product, follow this link to the Supplier table."

Table Relationships

Access lets you formally define foreign-key relationships using the Relationships dialog. The company in this example has a SALESREP table and a DISTRIBUTOR table. Each sales rep sells to many distributors, but each distributor has one and only one sales rep.

After you have selected this relationship with the dialog, Access displays it as a diagram like this one. The line relates the field "SalesRepNo", which is the key to the SALESREP table, to the field of the same name in the DISTRIBUTOR table. The symbols on the line mean "one" and "any number".

Referential Integrity

As soon as you join two tables together this way, tricky questions arise as soon as you think about adding or deleting records:

• Suppose Mr. Dumont was the sales rep for Wilson Distributors. Now Dumont retires. Do we delete his record from the table? What happens to the record for Wilson Distributors? It would be pointing to a non-existent SALESREP record.
• Also, all of Dumont's sales orders over the years have had his number on them. If we delete his record, all of those SALEORDER table records have a pointer to a non-existent record.
• Suppose Wilson Distributors goes out of business? If we delete their DISTRIBUTOR record, all of the SALEORDER records that refer to them are pointing to a non-existent record.
• Suppose we get a new distributor to sell to? Can we add them into the database before we have assigned them to a sales rep? In other words, can we leave the SalesRepNo field blank for a while?

"Integrity" in a person means that he is reliable and you can trust what he says. "Integrity" in a database means that all the foreign keys that point to other tables are pointing to real records, and you can follow those pointers and get the related records.

In the case of Mr. Dumont, here is what integrity means: You can't delete Mr. Dumont's SALESREP record as long as a single pointer to him remains in the DISTRIBUTOR or SALEORDER tables. Access can determine this, and enforce this rule. What this means in practical terms is that you can never get rid of a sales rep's record. We would have to add another field, probably a "Yes/No" field called something like ActiveEmployee, to mark whether a sales rep is still on the payroll or not.

Enforcing Referential Integrity

By checking the boxes on the Table Relationships form, you can have Access enforce integrity between two tables. This means:

• You can't add a reference to another table record (that is, you can't add a foreign key) unless that record already exists.
• You can't delete a record as long as other records still exist that point to it. Access will ask you for permission to delete all of these "dependent" records if you try to delete the primary record. CAUTION: you usually don't want to do that!

Note that enforcing referential integrity means that records have to be added in a particular order. In the example above, the SALESREP records have to added first, because DISTRIBUTOR records have to be able to point to SALESREP records.

Storing Orders in the Database

Orders are what we want more of in a business: orders mean somebody bought something, and we make money. An order has these characteristics:

1. Every order has exactly one Customer. You may not always know the Customer's name, such as a cash register sale in a retail store, but there always has to be one.
2. Every order has at least one Product. A Customer can't buy zero products (why would we write up an order?), but he can, and we hope he does, buy more than one Product. An order can have any number of Products — we will be glad to sell the Customer as much as he wants to buy.
3. Every Product has a Quantity, which might be 1 or more. For some kinds of businesses, we might use a Weight or a Volume instead of a Quantity.
4. Every order may or may not have an Employee related to it. In some businesses, the sales rep who sells an order gets a commission on every sale, and we will definitely need to tie that sales rep to that order. In other companies, it doesn't matter who writes up the order, and we may not need to keep the Employee's name.

Creating a SALEORDER Table

When we actually start to build a database table to record orders, some problems become apparent. Funnyism: you should not make a table called Order, because this is one of the "reserved words" for SQL language and it will cause difficulties later. Instead, we'll call the table SALE or SALEORDER. Here's a design we might try out:

At first glance, this seems to have everything we need. Each Sale has one Customer, one Product and one Employee. We keep the Quantity in the Sale table, and get the names of the Customer, Product and Employee from their tables.

This would work fine right up until a Customer wanted to buy two different Products.

Now what? We could make the Customer ring up two separate sales, one for each Product, but he'll think (correctly) that we're fools. We could put two fields into the database, one for ProductID1 and one for ProductID2. That would work until somebody wanted to buy three Products.

In fact, we want to be able to sell a Customer as many Products as he wants to buy. That's how we make money. We'll need a different table design.

Table Relationships (Again)

1. A one-to-many relationship is the most common kind. For example, each PRODUCT has one SUPPLIER, but each SUPPLIER has many PRODUCTS. Most of the table relationships you create, by far, will be this kind.
2. A one-to-one relationship is possible but not very common. It is sometimes done to separate data that some people should not look at. For example, each EMPLOYEE may have a separate EMPLOYEE_HEALTH record, so that nobody except medical personnel can see the medical record even if they can see the rest of the employee's record. In this case, each EMPLOYEE has exactly one EMPLOYEE_HEALTH record, and each EMPLOYEE_HEALTH record is tied to exactly one EMPLOYEE record. However, this situation happens only occasionally.
3. A many-to-many relationship is what we have between SALEORDER and PRODUCT. Each SALEORDER has many PRODUCTS (we hope!) and each PRODUCT can appear on many SALEORDERS. But there's a problem: there is no way to represent a many-to-many relationship in a relational database.

Of course, there's a solution. We will add another table in-between SALEORDER and PRODUCT, and break up the many-to-many relationship with two one-to-many relationships. Our final design will look like this:

The ORDERLINE table is called a "junction" table because it ties together the SALEORDER and PRODUCT tables. Note that the ORDERLINE table does not have a primary key field called "OrderlineId". Instead, it has a two-field key: the combination of the SaleId and ProductId fields will always be unique, so these two fields together form the primary key for the ORDERLINE table.

The relationship of SALEORDER to ORDERLINE is one-to-many. That is, each SALEORDER can have any number of ORDERLINE records, which means that we can sell any number of Products to a Customer.

Lookup Tables

It's getting to be pretty inconvenient to enter all of those table ID fields directly. Fortunately, Access has a very simple way to make enter easier. The Lookup Wizard appears as one of the data types in the Design View of a table, such as the ORDERLINE table shown here. If you have a foreign key field (that is, a field that points to another table's primary key), the Lookup Wizard will set up the table for these features:

• Instead of the ID number, Access will display a pull-down listbox with a readable name, such as Customers, Products or Employees.
• When you choose a name from the list, the related ID number is actually stored in the database.
• Even better, when you look at the table later, you will see the name instead of the number — even though it's the number that is actually stored.

We will demonstrate in class how to use lookup fields for all of the foreign-key relationships in the fictitious company tables.

Simple Forms with the Form Wizard

IMPORTANT: Create the SALEORDER and ORDERLINE tables, add the lookup fields (above) to these tables, and link all the tables using the Relationship tool, before using the Form Wizard.

The Access Form Wizard will create an on-screen form that makes entering and reading table data even easier. This Wizard expects that you will have set up all the table relationships and lookup fields before you start. Given that, it really does a very nice job of generating a correct form with very little effort.

We will demonstrate this in class using Access 2010.

Week 4 Homework

1. Create SALEORDER and ORDERLINE tables for your fictitious company database, as described above.
2. Create relationships as shown above between your tables. If your tables do not have the right foreign keys to make these relationships, then add fields as necessary.
3. Use the Lookup Wizard to create lookup fields for the SALEORDER and ORDERLINE tables.
4. Create a form for the SALEORDER table as described above. Show this form to the instructor in class, and make sure you get credit for it.
5. Use the form to create at least 20 SALEORDER records. Most records should have more than one ORDERLINE.
6. Print the SALEORDER table in datasheet view, and turn it in.