Types of connections

Connections are the most important and difficult things in modelling. Objects only rarely exist with no connections to other objects. The saying “No man is an island”[1] expresses this with regard to people, and other objects are just the same.

When modelling data, we normally model a class of objects using one or more tables in a database.  Each table contains columns which represent attributes of an object. With connections between objects, we need ways of linking different objects, and that normally means linking a row from one table to one or more rows in another table.

Types, sorts, categories and so on

Connections can be categorised in different ways because connections can have different attributes. A connection between two objects will always have some particular meaning. It is never enough to know that two objects are connected: we always need to know what the connection means and the easiest way to show this is to give a connection a name which shows its meaning. For example, a country and a city can be connected in many different ways and knowing that they are connected doesn’t really tell us much. The city may be the capital of the country or it may be the oldest, newest, biggest or smallest city in the country. If we don’t know the meaning, the mere fact of a connection is not very useful.

The meaning of the connection will often imply another attribute of connections: the number of possible objects there are at each end of a connection.

Examples

Example is the easiest way to make this clear, so here are a few to think about.

Relationships between mouths and teeth

  • A tooth can be only in one mouth
  • A mouth can have many teeth

Relationships between authors and books

  • An author can write more than one book
  • A book may have more than one author

Relationships between cities and countries

  • A country can have many cities
  • A country can have only one capital city
  • A country can have only one largest/smallest/newest/oldest city
  • A city can only be in one country (normally!)

Relationships between wheels and a 4 wheeled car:

  • A wheel can be fitted to only one car
  • A car can only have 4 wheels fitted
  • A car may have one or more spare wheels
  • A car will normally have two front wheels
  • A car will normally have two rear wheels
  • A car will normally have two driving wheels (or 4 for an all wheel drive car)

Relationships between words and letters

  • A word must have at least one letter
  • A word can have many letters
  • A letter can be found in many different words
  • A letter can be found more than once in a particular word

Relationship between words and verses in the Bible

  • A verse must have more than one word in it
  • A word can appear in more than one verse
  • A word can appear more than once in a verse

Relationships between poles and wires

  • A pole can have more than one wire attached
  • A wire can be attached to only one pole at a given point
  • A wire can be attached to more than one pole (at different points)
  • If a wire is attached to the same pole more than once, it is probably a mistake

This last example also takes us to hierarchies of connection. If poles need to carry more than one wire, they will normally have cross arms, and the wires are then connected to the cross arms rather than the pole itself. Yet often, poles with cross arms can still carry a single wire at the top of the pole. Maybe you can think how we might express the connections between poles, cross arms and wires.

Any number of examples of connections can be given, and the number of items which can be involved in connections can vary enormously.

Naming connection types

We can have simple groupings where a certain number of objects makes up a larger grouping: 7 days in a week, 13 items in a baker’s dozen, 66 books in the Bible, 8 bits in a byte, 3 wheels on a tricycle, 10 millimetres in a centimetre, 25.4 millimetres in an inch, 24 hours in a day, even two faces on a hypocrite!  In mathematics, this kind of grouping is called “cardinality” – the number of items in a set.

In data modelling, the same idea is used, but generally simplified to a limited set of choices such as “one to one”, “one to many”, “many to one” or “many to many”.

These names may all be clear and simple to you, but I have always found the names confusing although I have worked with data modelling for much of my working life.  As we look at these different relationships, I will try to explain why I find the names confusing.

One-to-one relationship (mandatory)

A one to one relationship is the simplest of all relationships.  One object is connected to one, and only one, other object.  They are very rare in the physical world, but quite common in databases.

Let me explain.

Imagine the relationship between countries and cities.  We can say that a country must have one and only one capital city, and a city can only belong to one country.  However this example is contrived.  There is nothing special about a capital city compared with any other city.  A capital city is still a city.  The one-to-one relationship is quite artificial.  From a modelling point of view, having this relationship as a one-to-one relationship would describe two tables: a table of countries and a table of capital cities.  If feels much more natural, however, to have a table of all cities, not just capital cities.  The data has been artificially partitioned to create the example.  If the data was really modelled in this way and you wanted to fetch all the cities in a country, you would need to fetch them from two tables (a table of capital cities and another table of non-capital cities) or store the same data in two places (a table of capital cities and another table of all cities with some entries in both tables).

Again, you may say that a computer can have only one keyboard and that a keyboard must be connected to only one computer.  However, if this is so, why not put the two into one record describing a computer and its keyboard?  Of course, the reason why you wouldn’t do this is because a computer actually can exist without a keyboard and having spare, unused keyboards can be very useful at times.

Usage of one-to-one relationships in databases

The common use of one-to-one relationships in databases is where an object has been split into two table for various access or storage reasons.  Some typical examples are: to avoid unnecessary retrieval of too much information – particularly information that is rarely needed; avoidance of row locking; and access control (security).  These will be discussed later.

If a true one-to-one relationship exists, the simplest way to model it is by having a column in one of the tables which can store the unique identifier of the related object.  Either table can have the extra column and the number of rows in each table will be exactly the same since each must have one – and only one – connected object in the other table.

In a database, this is a foreign key relationship, defined as a column that contains the primary key or unique identifier of a row in another table.

In the next article, we look at optional one-to-one relationships.

[1] From Meditation 17 of “Devotions upon Emergent Occasions” by John Donne, 1624 (http://www.online-literature.com/donne/409/)