Home

Understanding many-to-many from a different angle

Many-to-many relationships

In real-life situations, elements of information are often in a many-to-many relationship with each other. An Employee can work on a number of Projects, and a Project can be worked on by a number of Employees. A Member can borrow a number of Books from a library, and a Book can be borrowed by a number of Members. A Student can enrol in a number of Courses, and a Course can have a number of Students enrolled.

However, when trying to model this information into a relational database structure, Access doesn't allow us to directly establish a many-to-many relationship between 2 Tables.

The Junction Table idea

Advice for handling this situation is often based on the concept of a Junction/Resolver/Link Table. We are told to create a third table and put into it a field which can serve as a Foreign Key to the Primary Key fields of each of the tables at either side of the many-to-many relationship. It is not my purpose here to expand on this concept. It is explained in Access's Help, and in such places as http://support.microsoft.com/?id=304467.

Another approach

Well, different people think differently. I have found that some people can easily relate to the idea of a junction table, and some can't. I can't myself. I do not use this concept. I find it easier to think in terms of entities. Sometimes you have an entity which is on the "many" side of a one-to-many relationship with at least 2 other entities.

I find the example of the Library a useful illustration. A library has Books, a library has Members, and the Members borrow the Books. As alluded to above, some people simply say there is a many-to-many relationship between Members and Books, so we have to insert a faceless Junction Table, which they will probably call MemberBooks. If you are one of those people who find it easy to think like this, that's fine. If you are one of those people who find this confusing, because it puts structure ahead of function, read on.

I personally find it easier to understand that there is an entity called Loans. These are the data entities you are dealing with: Members, Books, and Loans. So, you make 3 tables, one for each of these data entities. There is a one-to-many relationship between Members and Loans, and there is a one-to-many relationship between Books and Loans. And these one-to-many relationships are constructed in exactly the same way that other one-to-many relationships are constructed, i.e. you have a field in the "many" side table which is a Foreign Key to match the data in the Primary Key field of the "one" side table. So, there is a BookRef field in the Loans table, and a MemberID field in the Loans table. In this case, you have a table (Loans) which happens to be on the "many" side of two one-to-many relationships.

(simplified for the purpose of example)

A parallel approach applies to other examples. If you run dog shows, you have (among other things) 3 data entities... Dogs, Events, Entries. If you run a restaurant, you have (among other things) 3 data entities... Diners, Dishes, Consumption. Of course, you get the same end result as regards your table design, and to some people the distinction may be subtle. But to give Loans, Entries, and Consumption their true status as data entities in their own right, rather than relegating them to merely serving a linking function, may help you understand your data in a more useful way.

Steve Schapel