UNDERSTANDING RELATIONS BETWEEN TABLES



UNDERSTANDING RELATIONS BETWEEN TABLES

Before you learn to create relationships, it is important to understand them.

A review of relationships

First, you can create relationships between tables at two places: in the Relationships window that will relate them at a table level, and when you create queries to display information from those tables (known as the query level).

Relationships established at the table level take precedence over those established at the query level. If you specify a relationship between tables at the table level (in the Relationships window), Access will recognize it automatically when you create a multiple-table query that uses fields from more than one table.

With that said, it is now important to understand that there are 3 types of relationships that you can set between two tables:

• One-to-one

• One-to-many

• Many-to-many

Understanding the four types of table relationships

When you physically join two tables (by connecting fields with like information), you create a relationship that Access recognizes.

The one-to-one relationship

The one-to-one relationship, though rarely used in database systems, can be a very useful way to link two tables together. A good example of a one-to-one relationship occurs in most billing systems; a billing file is created to allow additional information necessary to invoice customers at a location other than their listed addresses. This file usually contains the customer number and another set of address fields.

Only a few customers would have a separate billing address, so you wouldn’t add this information to the main customer table. A one-to-one relationship between a customer table and billing table may be established to retrieve the billing address for those customers who want to have a separate address for billing purposes and one for catalogs or other uses. Although all the information on one table could be added to the other, the tables are maintained separately for efficient use of space.

The one-to-many relationship

The one-to-many relationship is used to relate one record in a table with many records in another. Examples are one sale to many line items or one customer to many customer contacts. Both of these examples are one-to-many relationships.

The many-to-many relationship

The many-to-many relationship is the hardest to understand. Think of it generally as a pair of one-to-many relationships between two tables, with a special table created (called a junction table) that is used to link them together. The junction table is composed of a minimum of two fields—the foreign keys from both tables it is linking together. These two fields are subsequently used to create the primary key in the junction table.

For a true many-to-many relationship between these two tables, there has to be a junction table that is composed of a complex primary key created by joining the primary key from both tables in a single primary key in the junction table.

Understanding Referential Integrity

In addition to specifying relationships between tables in an Access database, you can also set up some rules that will help in maintaining a degree of accuracy, or Referential Integrity, between the tables. For example, you would not want to delete a contact (buyer or seller) record in your Contacts table if there are related sales records in the Sales table. If you did delete a customer record without first deleting the customer’s sales (or a seller without first deleting any items sold to you by them), you would have a system that had sales without any buyers. This type of problem could be catastrophic.

Imagine being in charge of a bank that tracks loans in a database system. Now imagine that this system has no rules that say, “Before deleting a customer’s record, make sure that there is no outstanding loan.” It would be disastrous! So a database system needs to have rules that specify certain conditions between tables — rules to enforce the integrity of information between the tables. These rules are known as referential integrity; they keep the relationships between tables intact in a relational database management system. Referential integrity prohibits you from changing your data in ways that invalidate the links between tables.

Referential integrity operates strictly on the basis of the tables’ key fields; it checks each time a key field, whether primary or foreign, is added, changed, or deleted. If a change to a value in a key field creates an invalid relationship, it is said to violate referential integrity. Tables can be set up so that referential integrity is enforced automatically.

When tables are linked, one table is usually called the parent and the other (the table it is linked to) is usually called the child. This is known as a parent-child relationship between tables. Referential integrity guarantees that there will never be an orphan, a child record without a parent record.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download