1. Linking things

Relational databases can be used in various ways for linking things. In this database, different tables represent different types of data, with rows representing individual entries for things such as manuscripts, texts, stanzas, words and so on. Each row has an arbitrary and unique identifier in the form of a number (e.g. 143) which is used to link tables together.

Native linking

The native way of linking items in a relational database is to use foreign keys which define a one-to-many relationship, that is, many rows in one table can link to a single row in another table. Examples are manuscripts and collections: a manuscript collection contains many manuscripts, but any manuscript may only belong to one collection. There is a link in the manuscript table to the collections table: this may be represented by a drop-down menu or an auto-fill field. This type of linking is handled natively by the database which will automatically check whether the reference is correct (so that you can’t link to a non-existent entry in another table), and whether the link is optional, depending on how it is defined in the database.

Many-to-many fields

Many-to-many relationships are required by different kinds of data. An entry for a text may require multiple keywords, for example, which also may be used by other rows. In other words, there are multiple keywords linking to multiple texts.

A simple way of creating a many-to-many relationship is to have a field in one table that includes multiple unique key values from another table, separated by commas. The database can search these in order to find all the references to a particular row in the target table. These can be edited using checkboxes, for example.

The database cannot check such fields automatically, however, to ensure the references are correct. If you delete a row from the target table, you may be left with a ‘ghost’ reference to it in another table which uses this type of field. The way in which these fields are searched and linked is much slower than the native database way of linking.

Linking two tables by a third table

The robust way of creating a many-to-many relationship in a relational database is to create a third table which links the two together. Rows in the third table have one-to-many links to both tables that require linking. Each link between a row in one table to a row in another has its own row in the third table.

This approach has the advantage that additional columns can be used to define the nature of the relationship, such as if a link is tenuous, or recording who has made the link, or notes about the link.

Editing this kind of information is more complex, however. In this database such tables are usually included in the editing forms for each individual table, so that multiple entries in the third table can be added or removed.

Generic linking table

Using the model of a ‘triple’ relationships can be defined by any row in any table using a generic linking table. This contains references to a subject table (name and identifier) and an object table (name and identifier) as well as a predicate table (identifier) which defines the nature of the link between the two, e.g. a manuscript (subject) is part of (predicate) a collection (object).

This type of table has the advantage that anything in the database can be linked to anything else, but the database has no way of ensuring the references are correct. It can only list the references for each entry without being able to define the ways in which these links can be manipulated or represented.

References