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].
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."
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 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.
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 Customary, is no longer in favor among database designers. Most designers now use automatically generated numerical key valuescalled Access AutoNumber or SQL Server identity fields. The Northwind Orders and Products tables, among others, have primary keys that use the AutoNumber data type. The Employees, Shippers, Products, and Suppliers tables use AutoNumber keys to identify the persons or objects to which the table's records refer. Objects that are inherently sequentially numbered, such as checks, are ideal candidates for an AutoNumber key that corresponds to the check number, as mentioned in "Choosing
Primary Key Codes" later in this chapter.
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. Because GUIDs can't represent a property of an object, such as a check number, GUID keys are called surrogate keys. You can't select a GUID data type in Access's Table Design mode.
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.
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 (x) symbol, as in Figure
4.3. All the direct relationships between the tables in Figure
4.3 are one-to-many. One-to-manyalso called many-to-onerelationships 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, Products and Order Details act as linking tables between the Suppliers and Orders tables.
Note - Access 2007's new multivalue field feature automatically generates a hidden linking table "under the covers."
Tip - Don't add multivalued fields to tables of databases that
you might want to upsize to SQL Server some day. SQL Server doesn't support
the Allow Multiple Values option that enables or disables the feature.
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 Oakmont.accdb sample database file in the \Seua2007\Oakmont folder of the accompanying CD-ROM has a structure that differs from that of Northwind.accdb, but the design principles of the two databases are similar. OakmontSQL.mdf is an SQL Server 2005 database for use with ADP. ADP uses a special set of toolscalled the project designer or da Vinci toolset in this bookfor designing and managing SQL Server databases.The Oakmont files are course enrollment databases 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 Access 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. Access and SQL Server databases store information on table relationships as an object within the database file.
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 Access 2007 and SQL Server 2005 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 Access and SQL Server database applications.