Anatomy of a table relationship

When you create a relationship between two tables, you need to know how the individual record values will relate to each other. You need to determine if one or more than one record in the first table will be associated with one or more than one record in the other table. How records in two tables relate to one another is called their cardinality.

For example, suppose you have a parcels feature class and a nonspatial table of parcel owner data. There are four possibilities for how the records in the two tables are related:

1. Each parcel is owned by only one person.

2. One parcel is jointly owned by several people.

3. Several parcels are owned by one person.

4. Multiple parcels are jointly owned by multiple people.

When creating a relationship, you need to know which of these four possible relationships is the correct one for your data.

Table records can have four possible relationships (called cardinality): one-to-one, one-to-many, many-to-one, and many-to-many.

Cardinality is determined by the role that each table plays in the relationship. The primary table in a relationship is called the origin table. The table that is related to the origin table is called the destination table. Often, a feature class table is the origin table and a nonspatial table that stores additional attributes for the features is the destination table.

In the parcel features and owners example, the table containing the parcel features is the origin table and the table with owner records is the destination table. The relationship between the two is maintained through attribute values in the fields that the tables have in common; these fields are called the key fields. In the origin table, the key field is called the primary key; in the destination table, the key field is called the foreign key.

Key fields in the origin and destination tables. Both fields contain parcel ID numbers. Once the relationship is created, the key fields are marked with an asterisk (*). The asterisk indicates that the field cannot be deleted by the user. You must remove the relationship before you can delete a key field.