An associative table, also called a junction table, represents a many-to-many relationship. Instead of linking two entity tables directly, it sits between them and turns the relationship into two one-to-many relationships. section 5.7 section 5.10
Use one when each side can be connected to many rows on the other side. The table stores the primary keys of the two participating entities, which also act as foreign keys. Together, those keys can form a composite primary key that uniquely identifies each pairing. section 5.7 section 5.10
To model a many-to-many relationship:
- Identify the two entity tables whose maximum cardinalities are both many. section 5.7
- Create a junction table between them. section 5.10
- Put the primary key from each entity into the junction table. section 5.7
- Treat the two keys together as a composite primary key, then draw two one-to-many relationships pointing toward the junction table. section 5.7
Authors and books
An author can write many books, and a book can have multiple authors. Create author_book with author_id and book_id. The pair of columns is its composite primary key. Author and book are then related indirectly through author_book rather than directly. section 5.7
A junction table is a weak entity by nature: it depends on the strong entities it connects. For example, purchase_product links purchase and product and uses their primary keys as its composite primary key. section 5.9
Inspect the relationship diagram to see a many-to-many relationship replaced by two one-to-many relationships through a junction table. section 5.10
