Designing Databases for Historical Research

D. Relationships

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.

One-to-one relationships:

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.

One-to-many relationships:

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.

Many-to-many relationships:

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:

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.