Relational Databases and Tables

Tables are among the most fundamental building blocks of a relational database. The Northwind sample database that ships with Access includes several tables that track important information about a fictitious business. The Customers table tracks information about customers, such as company and contact name. The Products table tracks product-related data, such as product name, units on hand, and unit price.

Each table in a database should contain information that is appropriate for only one specific type of entity. For example, a typical school contains students, teachers, and classes (among other things). Students enroll in the classes; teachers lead the classes. A database application for the school might contain a table for students, a table for classes, and a table for teachers. The Teachers table should contain information about teachers, but not about students or classes. Likewise, the Classes table should not contain the addresses, social security numbers, and telephone numbers of teachers.

Database tables have a structure similar to that of spreadsheets. The rows, or records, in a table represent unique instances of the entities stored in the table. For example, a Customers table includes a single row for each customer, and a Products table has a single row for each product. (There is no significance to the order of rows; you can arrange them in any order without changing the meaning of the information in a table.)

Each field (column) in a table holds a specific type of information. For example, each customer in the Northwind database has a name, an address, a phone number, and so on, and this information is stored in the fields of the Customers table.

Many database tables include one or more fields that uniquely identify each row in the table. Such a unique identification is known as a primary key. The primary key of the Customers table in the Northwind database is the CustomerID field.

Normalization

Normalization involves applying a set of design rules to database tables. Normalization offers at least four benefits:

• It eliminates redundant information. Many unnormalized databases require that the same contact information be entered on multiple forms. Eliminating this redundancy reduces the likelihood of data entry errors that can corrupt the database. It can also simplify database maintenance because a value is stored and therefore deleted and updated in just one place.
• It reduces the size of a database. Because each type of information is stored in just one location, your database does not have to save multiple copies of the same information. Normalization also minimizes the number of columns in a table, which minimizes the overall size of the database.
• It simplifies searches. Database professionals who understand normalization rules will instantly know how to navigate a database's tables to find the information that they seek. Casual database users will find the table design logical because each table describes a single entity and all of its properties are columns in that table.
• It simplifies querying. A table column holds a single type of data, such as first name or last name, but not both names. By storing last names in a separate column, a database can readily provide a list of all rows with a specific last name. With an unnormalized database that stores first names and last names in the same column, a query must extract the last name before selecting on a specific value for it.

There are three popular normalization rules—typically called First Normal Form, Second Normal Form, and Third Normal Form—as well as a variety of special rules.

First Normal Form

First Normal Form states that every field in a table should contain a single indivisible item. The Order Details table in the Northwind database illustrates this rule. Figure 3-1 shows an excerpt from the table that displays three orders. Each column contains a single type of data. The second column contains just product names—not product names and prices. Also, there is just one instance of a product in each row.

Figure 3-1. An excerpt from the Order Details table illustrating the first normalization rule. The elements in each column, or field, are of a single type, and for any row the elements in a column are indivisible.

Database tables that fail to comply with First Normal Form generally do so in two ways. First, they put more than one item in a single field. (Notice that the table in Figure 3-1 contains just one item per field.) An example is a table that stores a first name and a last name in a single field. This complicates extraction of information. A second way that First Normal Form is often violated is that a table has a field for each product in an order, which requires multiple unit price, quantity, and discount fields, further violating First Normal Form. This design increases the size of a table, and many of the values for some columns are likely to be empty.

Tables that comply with First Normal Form are likely to be organized around logical entities, which makes it easy to find information, and they are likely to have a primary key, which ensures that each row in a table is unique. Recall that database tables represent instances of entities. In the table shown in Figure 3-1, each row is one item from a customer order. The primary key for the table is a compound key based on the OrderID and ProductID numbers because each item from a customer order can be identified by a unique combination of the customer order number and the product's ID.

Second Normal Form

Second Normal Form specifies a required relationship among the columns in a row. To comply with Second Normal Form, a table must comply with First Normal Form, plus all values within a row must contain information about the specific entity represented by that row. Also, no two fields can be dependent on one another. One common violation of this rule is placing fields for two distinct entities in a single table.

The Northwind Order Details table complies with Second Normal Form. The quantity field is clearly appropriate for each item. Unit price and discount can change independently for each item, and placing unit price and discount in the table enables an application to easily track those items for each order.

The Northwind database has the unit price in both the Order Details and Products tables. This might appear to violate Second Normal Form. However, this design enables an administrator to update item prices without affecting the prices of items already ordered. Individual orders can use the price and discount schedule in the Products table as a guideline rather than as a rigid rule for all transactions.

Third Normal Form

Third Normal Form specifies that all the fields in each row must be unique and not dependent on one another. For example, this rule permits only one date field per row. If the Orders table contains an order date field, it must not also contain fields for order day, week, or month. (The application can derive the month of an order from the order date.) The Orders table does have several fields that contain Date/Time values, but these refer to the time of the initial order, the date by which a customer requires an order, the date an order ships, and so forth.

Following the rules

While it is wise to follow normalization rules, you might sometimes have reason to selectively comply with them—for example, to avoid having too many tables. A postal code field along with dependent geographic code fields can stand alone as a separate table. When you know the postal code for an address, you also know its country, state or province, and city. Therefore, you do not have to store both the postal code and the city in the same table.

Setting up multiple tables to store postal code, city, geographic region, and country information might not be practical for some small and medium-sized database projects. Even for a large database project, such a design requires the joining of the main Contacts table with multiple tables on each occasion that requires contact information. This can seriously degrade performance—especially if there are many such instances requiring the joining of multiple tables.

Relationships Between Tables

When you create tables for an application, you should also consider the relationships between them. These relationships give a relational database much of its power. There are three types of relationships between tables: one-to-one, one-to-many, and many-to-many.

One-to-one relationships

In a one-to-one relationship, each record in one table corresponds to a single record in a second table. This relationship is not very common, but it can offer several benefits. First, you can put the fields from both tables into a single, combined table. One reason for using two tables is that each field is a property of a separate entity, such as owner operators and their trucks. Each operator can operate just one truck at a time, but the fields for the operator and truck tables refer to different entities.

A one-to-one relationship can also reduce the time needed to open a large table by placing some of the table's columns in a second, separate table. This approach makes particular sense when a table has some fields that are used infrequently. Finally, a one-to-one relationship can support security. Access applies user-level security at the table level. Therefore, if a subset of the fields in a table requires security, placing them in a separate table lets your application restrict access to certain fields. Your application can link the restricted table back to the main table via a one-to-one relationship so that people with proper permissions can edit, delete, and add new records to those fields.

One-to-many relationships

A one-to-many relationship, in which a row from one table corresponds to one or more rows from a second table, is more common. This kind of relationship can form the basis for a many-to-many relationship as well. The Customers and Orders tables from the Northwind database have a one-to-many relationship. Any customer can have one or more orders, but each order belongs to exactly one customer. Figure 3-2 on the below shows the Design view of the Orders table. Its primary key is OrderID. Each OrderID value can appear in just one row. The table also includes a field, CustomerID, which links to the Customers table. (The CustomerID field is the primary key of the Customers table. A field that serves as a link to another table is known as a foreign key.) CustomerID values can appear multiple times in the Orders table. In fact, a customer ID appears once for each order that the customer makes. This foreign key links the "many" side of a relationship back to its "one" side.

Figure 3-2. The Design view of the Orders table. The CustomerID field serves as a foreign key for the one-to-many relationship with the Customers table.

Many-to-many relationships

The many-to-many relationship exists only indirectly; it builds on two one-to-many relationships. The Orders and Products tables are in a many-to-many relationship. Any order can have many products in it. Similarly, a single product can appear in many different orders.

I imported four tables—CustomersNW, OrdersNW, Order DetailsNW, and ProductsNW—from the Northwind database to this chapter's sample database. Figure 3-3 shows the relationships among these tables in the Relationships window. The CustomersNW table is in a one-to-many relationship with the OrdersNW table, the OrdersNW table is in a one-to-many relationship with the Order DetailsNW table, and the ProductsNW table is in a one-to-many relationship with the Order DetailsNW table. The Order DetailsNW table acts as a "junction box" that links the OrdersNW and ProductsNW tables in a many-to-many relationship.

Figure 3-3. The Relationships window view of the many-to-many relationship between the OrdersNW and the ProductsNW tables.

All many-to-many relationships have a "junction box" design, in which a third table, the junction box, serves as a link between the two named tables in the relationship. This design helps to maintain the efficiency of the database because the junction box can have a large number of rows but you can define it to contain just a few columns. As with all normalized tables, you should restrict the columns to those that characterize its entities. This rule is especially important with junction box tables because they can contain such a relatively large number of rows.

Referential integrity

You can make your databases more robust by incorporating referential integrity and cascading updates and deletes. The rules of referential integrity ensure that relationships between tables are valid. These rules also prevent you from accidentally changing related data. (For example, you don't want to delete a customer if that customer has one or more unpaid orders.)

Referential integrity does not permit the addition of a row to the many side of a relationship with a foreign key value that does not match the values on the one side of the relationship. You can, however, enter a missing, or Null, foreign key value that does not match any primary key value on the one side of a relationship.

Referential integrity also helps to avoid orphan records—records in a table on the many side of a relationship that don't have matching records on the one side. It does this by blocking the deletion of records on the one side that still have matching records on the many side. (For example, you typically wouldn't want to remove the sales associated with an employee if that employee leaves the company.) In some cases, orphan records can undermine the validity of results that you obtain from a database. At the very least, they can bloat a database with unnecessary and unusable records.

At times, you'll want changes to occur on both sides of a relationship. In these situations, the cascading delete and cascading update features of referential integrity are useful. These features are not automatic; you have to explicitly invoke them. With the cascading delete feature enabled, deleting any record on the one side of a relationship removes all matching records from the many side of the relationship. Cascading updates works the same way for primary key modifications. If you change a primary key value on the one side of a relationship, it cascades to the many side. All matching foreign key values update automatically.