5.12.6: Table Relationships
- Page ID
Access is a relational database management system. In a relational database, users divide information into separate, subject-based tables. Keeping data separated in related tables produces the following benefits:
- Consistency – Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, storing a customer’s name only once in a table about customers versus storing it repeatedly (and potentially inconsistently) in a table that contains order data.
- Efficiency – Recording data in only one place results in less disk space used. Additionally, smaller tables tend to provide data more quickly than larger tables. Finally, not using separate tables for separate subjects will introduce null values (the absence of data) and redundancy into the tables, both of which can waste space and impede performance
- Comprehensibility – The design of a database is easier to understand if the subjects are properly separated into tables.
In relational database terms, a relationship is a situation where multiple tables can contain related data that is linked by a common field. A relationship consists of a parent table and a child table. The child table references the parent table by having a field that matches a field in the parent table. The child’s field is the foreign key, whereas the parent’s field is the primary key. In a relationship, any data entered into the child’s foreign key field must match a value from the parent’s primary key field. By ensuring that the foreign key’s data matches data in the primary key, Access ensures that all records in the child table will have an associated record in the parent table. There are three types of relationships:
- One-to-One – A row in table A can have only one matching row in table B, and vice versa. i.e. A baseball player can have only one Jersey Number.
- One-to-Many – A row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. i.e. A baseball player can have many games played.
- Many-to-Many – A row in table A can have many matching rows in table B, and vice versa. This is achieved through the use of a third table (commonly called a junction table) that contains lookup data for both tables. Multiple players can play multiple games.
Determining the relationships between tables helps ensure that the right tables and columns exist. When a one-to-one or one-to-many relationship exists, the tables involved need to share a common column or columns. When a many-to-many relationship exists, a third table is needed to represent the relationship.