Hands on: Organisation in databases

A recent email from Ian Park provides the starting point for a ‘back to basics’ theme, which covers the art of building many-to-many relationships.

Ian keeps his church’s book catalogue in an Access database. He classifies each book into one or several subjects, for instance, ‘suffering’, ‘testimony’ and ‘the cross’. This is a classic example of a many-to-many relationship: each book can have multiple subjects and each subject can be covered by many books.

In the current database, each book occupies one row of a table and Ian uses one text field to store the classification(s) of the book. He can search the text field to find books dealing with a particular subject, but the process is unwieldy. Ian asks how he should redesign his database, so here we go.

Our sample data is held in two simple tables, Book and Subject. In order to model a many-to-many relationship between these, we introduce another table called a joining (or intersection) table, giving three tables. The joining table is BookSubject and is the central one in the screen.

Can we tell, by looking at the data, what subject is covered in which book? Yes. The first row in the BookSubject table has a 1 in the BookID column and a 2 in the SubjectID column. Glancing at the Book table tells us the book with the BookID of 1 is Damascus Road, and shifting our gaze to the Subject table tells us that the subject identified by SubjectID 2 is ‘epiphany’. The second and third rows in the joining table tell us that book 2 (Stations of the Cross) deals with two subjects, 1 (‘the cross’) and 3 (‘suffering’).

Database design description
It’s easy to visualise how the three tables work together, but let’s add some detail in database design terms. In the Book table, BookID field is the primary key field: each value in this field uniquely identifies one book. The same is true for SubjectID field in the Subject table: each value within the field is unique.

In BookSubject, BookID is a foreign key that ‘points’ to the primary key in the table Book; just as SubjectID is a foreign key pointing to the primary key of Subject. In addition, the primary key of BookSubject is made up of the two fields: BookID and SubjectID.

This means that we cannot have two identical rows in BookSubject. For example, we can have one row with a 1 in the BookID column and a 2 in the SubjectID column; but not two rows. It may not be completely clear, at this stage, why this is important, but all will be revealed shortly.

This is the classic way to model a many-to-many relationship in a relational database. It actually consists of two one-to-many relationships.

The first is that one book can cover many subjects – the Book table is at the ‘one’ end of the relationship and the BookSubject table is at the ‘many’ end.

The second is that one subject can occur in many books. The Subject table is at the ‘one’ end and the BookSubject table at the ‘many’ end.

The result of making these two one-to-many relationships with their ‘many’ ends in a joining table, is a many-to-many relationship between books and subjects.