Defining the Structure of Relational Databases

Relational databases consist of a collection of self-contained, related tables. Tables typically represent classes of physical objects, such as customers, sales orders, invoices, checks, products for sale, or employees. Each member object, such as an invoice, has its own record in the invoices table. For invoices, the field that uniquely identifies a record, called a primary key [field], is a serial invoice number.

Figure 4.1 shows Access's Datasheet view of an Invoices table, which is based on the Northwind.mdb sample database's Orders table. The InvoiceNo field is the primary key. Values in the OrderID, CustomerID, EmployeeID, and ShipperID fields relate to primary key values in Northwind's Orders, Customers, Employees, and Shippers tables. A field that contains values equal to those of primary key values in other tables is called a foreign key [field].

Figure 4.1. This simple Invoices table was created from the Northwind Orders table and doesn't take advantage of Access's extended properties, such as the field captions, lookup fields, and subdatasheets in the Datasheet view of the Orders table.

To learn more about primary keys in Access tables, see "Selecting a Primary Key," p. 193.

If you need information about a particular invoice or set of invoices, you open the Invoices table and search for the invoice(s) by number (InvoiceNo) or another attribute, such as a customer code (CustomerID), date (ShippedDate), or range of dates. Unlike earlier database models, the user can access the Invoices table independently of its related tables. No database navigation programming is needed. A simple, intuitive SQL statement, SELECT * FROM Invoices, returns all the data in the table. The asterisk (*) represents a request to display the contents of all fields of the table.

Removing Data Redundancy with Relationships

The Invoices table of Figure 4.1 is similar to a spreadsheet containing customer billing information. What's missing is the customer name and address information. A five-character customer code (CustomerID) identifies the each customer to whom the invoice is directed. The CustomerID values in the Invoices table match CustomerID values in a modified version of Northwind's Customers table (see Figure 4.2). Matching a foreign key with a primary key value often is called a lookup operation. Using a key-based lookup operation eliminates the need to repeatedly enter name, address, and other customer-specific data in the Invoices table. In addition, if you change the customer's address, the change applies to all past and future invoices.

Figure 4.2. Foreign key values in the Invoices table must match primary key values in the Customers table.

Tip

Using derived key values, such as alphabetic codes for CustmerID, is no longer in favor among database designers. Most designers now use automatically generated numerical key values called Jet AutoNumber or SQL Server identity fields. The Northwind Orders and Products tables, among others, have primary keys that use the AutoNumber data type.

Another method of generating unique keys is by use of Globally Unique Identifiers(GUIDs), which also are called Universally Unique Identifiers (UUIDs). GUIDs are 16-byte computed binary numbers that are guaranteed to be unique locally and universally; no other computer in the world will duplicate a GUID. SQL Server's uniqueidentifier data type is a GUID. You can't select a GUID data type in Access's Table Design mode, but Jet uses internally-generated GUIDs for data replication.

The Invoices table also connects with other tables, which contain information on orders, sales department employees, and the products ordered. Connections between fields of related tables having common values are called relationships (not relations). Figure 4.3 shows Access's Relationships window displaying the relationships between the Invoices table and the other tables of the Northwind sample database.

Figure 4.3. Access's Relationships window displays the relationships between the tables of the Northwind sample database, plus the added Invoices table. Every relationship between these tables is one-to-many. The many-to-many relationship between Products and Orders is an indirect relationship.

Relationships come in the following three flavors:

• One-to-many relationships represent connections between a single primary key value (the "one" side) and multiple instances of the same value in the foreign key field (the "many" side). One-to-many relationships commonly are identified by the number one and the infinity () symbol, as in Figure 4.3. All the direct relationships between the tables in Figure 4.3 are one-to-many. One-to-many also called many-to-one relationships are by far the most common.

• One-to-one relationships connect primary key values in two tables. You might think that the relationship between the Orders and Invoices tables could be one-to-one, but an order requires more than one invoice if one or more items are backordered and then shipped later. One-to-one relationships are uncommon.

• Many-to-many relationships require three tables, one of which is called a linking table. The linking table must have two foreign keys, each of which has a many-to-one relationship with a primary key in two related tables. In the example of Figure 4.3, the Order Details table is the linking table for the many-to-many relationship between the Orders and Products tables. Many-to-many relationships also are called indirect relationships.

There are many other indirect relationships between the tables shown in Figure 4.3. For example, there is a many-to-many relationship between the Suppliers and Orders tables. In this case, the Products and Order Details act as linking tables between the Suppliers and Orders tables.

The Relationships window displays the names of primary key fields in a boldface font. Notice in Figure 4.3 that the OrderID and ProductID field names are bold. The OrderID and ProductID fields comprise a composite primary key, which uniquely identifies an order line item. You can't repeat the same combination of OrderID and ProductID; this precaution makes sense for products that have only one stock-keeping unit (SKU), such as for Aniseed Syrup, which comes only in a carton of 12 550-ml bottles.

Note

The one-product-entry-per-order restriction prevents shared use of the Order Details table as an invoice line items table. If you short-ship an order item on one invoice, you can't add another record to the Order Details table when you ship the remaining quantity of the item. Microsoft didn't add an Invoices table for Northwind Traders, probably because of the complexity of dealing with backorders and drop-shipments.

The OakmontSQL.mdf sample database file in the \Seua2003\Oakmont folder of the accompanying CD-ROM has structure that differs from that of Northwind.mdb, but the design principals of the two databases are similar. OakmontSQL is an SQL Server 2000 database for use with ADP. ADP use a special set of tools called the project designer or da Vinci toolset in this book for designing and managing SQL Server databases.

The Oakmont model is a course enrollment database for a college. Figure 4.4 shows the Database Diagram window for the OakmontSQL database. The SQL Server Diagram window is similar to the Relationships window for Access's traditional Jet databases. The key and infinity symbols at the ends of each line represent the one and many sides, respectively, of the one-to-many relationships between the tables. Jet and SQL Server databases store information on table relationships as an object within the database file.

Figure 4.4. The SQL Server Database Diagram window for the OakmontSQL database shows one-to-many relationships between primary key fields (identified by key symbols) and foreign key fields (infinity symbols).

This book uses the Jet 4.0 and SQL Server 2000 versions of the Northwind and Oakmont sample databases in almost all examples. The tables of the Oakmont database have many more records than the Northwind tables. The large number of records in the Oakmont database makes it better suited than Northwind for predicting the performance of production Jet and SQL Server database applications.