Designing Databases for Historical Research
Historical Research Handbook: Designing Databases for Historical Research
D3. Types of relationship
Once the need for related tables arises, it is important to understand that there are different kinds of relationship that can exist between two tables. These differences are not technical, in the sense of being dictated by or a product of the mechanisms of the database; they are instead a function of the logical, semantic connection between the information between the two tables.
D3i – The three types of relationships
There are three types of relationship that can exist between two tables in a database, not all of which are useful or desirable.
- This relationship exists where a record in Table A can only have one related record in Table B, and a record in Table B can only have a single matching record in Table A
- For example, an MP can have only one constituency, and a constituency can have only one MP
This type of relationship is unusual in a database, as in many cases where a one-to-one relationship exists the information in the two tables could exist in a single table. This is not to say that if, as a part of your Entity Relationship Modelling process (see Section E), you discover a one-to-one relationship between two of your designed tables you should redesign the tables into a single one, only that you can if you want to. For example, if you wanted to, you could create a single table to enter information about Members of Parliament and about their constituency, as when it came to entering this data you would not encounter the duplication of information that was problematic in the example of people and hats in Section D2. On the other hand, tables are supposed to be discrete entities, and so logically speaking you might prefer to conceive of ‘MPs’ and ‘Constituencies’ as two different entities, and thus two different tables. The important thing to remember with one-to-one relationships is that the database software that you use to build your database will allow you to create this kind of relationship, and that it will not create any problems when it comes to running queries.
- This relationship exists where a record in Table A can have no, one or more matching record in Table B, but a record in Table B can only have one matching records in Table A
- For example, a mother can have more than one child, but a child can have only one biological mother
This is the most common type of relationship in found in databases, and is usually the type that you want to build into your designs. As illustrated in the people and hats scenario (Section D2) this type of relationship is used to overcome the kinds of problems that arise within the database when the information drawn from the sources would require the duplication of data if entered into a single table.
- This relationship exists where a record in Table A can have no, one or many matching records in Table B, and a record in Table B can have no, one or more than one matching record in Table A
- For example, an author can write more than one book and a book can be written by more than one author
If you discover this kind of relationship operating within your database design at the end of the Entity Relationship Modelling process, then you have a problem which will need to be addressed before you can proceed to actually building the database. Many-to-many relationships will not work in databases, as they will essentially break any query you try to run on the tables related, throwing the query into a ‘loop’ which will generate gibberish as results.
Given how problematic this type of relationship is, it is somewhat disheartening to see how frequently they crop up when modelling historical information! The way of dealing with a many-to-many relationship requires something of a conceptual leap, as it requires the creation of a table, sometimes called a Junction Table, to sit between the two related tables. This Junction Table will act in an abstract fashion – the data it will contain will not be information as such, but they will serve to split the many-to-many relationship into two one-to-many relationships.
D3ii – Many-to-many relationship between Author and Book tables
Take the database which contains a table about Authors and a table about Books, which might be designed according to the Entity Relationship Diagram depicted in image D3ii (for Entity Relationship Diagrams see Section E2). The arrowheads indicate the ‘many’ side of a relationship, here indicating that both tables are on the ‘many’ side, clearly highlighting a problem. To overcome the many-to-many relationship, we would insert a Junction Table to spit the relationship into two one-to-many relationships, as indicated in image D3iii.
D3iii – Many-to-many relationship between Author and Book tables split with a Junction Table
Note that each record in the Junction Table contains three fields: a unique ID for each record (Junction ID), and then a field for each of the Author IDs and Book IDs. Each record therefore becomes a unique combination of Author and Book IDs, which indicates which books were written by which authors:
D3iv – Many-to-many relationship between Author and Book tables split with a Junction Table – showing data
The Junction Table here is effectively circumventing the many-to-many relationship between books and authors, and each record it contains acts as a statement linking one or more author with one or more books. The first two records in the Junction Table, for example indicate that Author ID 1 was the writer of Book IDs 1 and 2, whilst the last two records indicate that Book ID 9 was co-authored by Author IDs 2 and 5. The relationship between books and authors is managed by the Junction Table, whilst the details about books and authors are kept in their respective tables.
This arrangement, whilst somewhat convoluted, will enable the database to run queries that draw on information in both the Book and Author tables when it would otherwise not be able to due to the many-to-many relationship. It is therefore a very valuable technique to bear in mind when identifying relationships between tables as part of the database design process.