On the relationship of database tables to a pivot/junction table

Question

In a database, one may create a many-to-many relationship between tables by creating a third table which maps the two together using foreign keys. What is the relationship between the third table and the two original tables?

For example, if table A and table B have a many-to-many relationship and table AB is the pivot table, is the A->AB relationship always a many-to-one relationship, and is the B->AB relationship also always a many-to-one relationship?

Answer 1

I believe the short answer is "Yes". :)

Simply put, foreign key relationships must map an AB column to a single column in A or a single column in B. But since the contents of AB represent the many-to-many relationship between A and B, there can by definition be multiple instances of either the A key or the B key in AB. At the same time, a duplicate record in AB, ie. representing the same A-B relationship multiple times, would be in error.

So, yes: one-to-many on either side of AB.

Answer 2

Being picky, A->AB and B->AB are one-to-many relationships, not many-to-one: an entity in A should appear once in A, but can appear many times in AB (linking it to several B instances); and an entity in B should appear once in B, but again could appear many times in AB (linking it to several A instances).