Knowing about table relationships

A table relationship connects records between tables. The benefit of relating tables is reducing data redundancy and increasing data integrity. To relate two tables together, each table must contain fields that have values in common. For example, one table may list all of the parcels in a city, while a second table may list all of the land owners in the city. The parcels table can have an OwnerID field that also exists in the owners table so that you can relate the correct owner from the owners table to the correct parcel in the parcels table. This is beneficial in cases where one owner owns multiple parcels, but we only have to store the owner information one time, which saves space and is easier to update as we only have to update owner information in one record.

A field can be defined as an index. A field that's set as an index must only contain values that are unique for each record, and therefore it can be used to identify each record in a table uniquely. An index is useful for two reasons. Firstly, it allows records to be quickly found during a query if the indexed field is part of the query. Secondly, an index can be set to be a primary key for a table, allowing table relationships to be built.

A primary key is one or more fields that uniquely identify a record in its own table. A foreign key is one or more fields that uniquely identify a record in another table. When a relationship is created, the record(s) from one table is linked to the record(s) of another table. With related tables, more complex queries can be executed and redundancy in the database can be reduced.