Types of Database Relationships


Cardinality describes how many possible occurrences of one entity can be associated with the number of occurrences in another.

One-to-One Relationships

One-to-one relationship is when one record in a table is associated with only one record in another table.

  • primary key in the first table is often the primary key in the other table as well

One-to-One Relationship in Microsoft Access

  • symbols on the relationship line indicate the type of relationship that exists between these two tables
  • can be only one record per student in tblStudents and only one record per student in tblGiftedEntry
  • Thus, the cardinality of the two tables is a one-to-one relationship

One-to-Many Relationship

One-to-many relationship is when one record in a table is associated with multiple records in another table or tables.

  • a primary key is joined to a foreign key

One-to-Many Relationship in Microsoft Access

  • Each time our student enters into a new school year, we use their StudentID from the student information table to associate them to their new enrollment record for the year in the Enrollment table
  • StudentID is a primary key in tblStudents but a foreign key in tblEnrollment
    • establishes the one-to-many relationship

Many-to-Many Relationship

Many-to-many relationship is when many records in a table are associated to many other records in other tables.

  • Data tools can’t resolve a many-to-many relationship without the use of another table that uses the associated keys from each table to serve as a bridge
  • A bridge table is a table designed to “bridge” the many-to-many relationship
    • performs as a one-to-many relationship with each of the tables that have many records in both tables
    • typically, holds only key fields

Many-to-Many Relationship with a Bridge Table in Microsoft Access