As we saw in the list of relationships at the end of last week’s newsletter, most relations do not demand one and only one connected object. It is much more common for the limit of one object to be only on one end of the connection.

A tooth can be in only one mouth, a child has only one mother, a driver’s licence has only one owner, a hen’s egg can only come from one hen, a city belongs to only one country, a petal belongs to only one flower, and so on.

Incidentally, some of these example relationships are generally true but can have exceptions in the real world. In data modelling, if we model something as one-to-one, or one-to-many, there must be no exceptions. None.

In the practical application of data modelling, the simplest and most efficient way to model a one-to-many relationship is by having a column in one of the tables which can store the unique identifier of the related object.

Here we come to the nub of my confusion with relation naming. I understand what the name “one to many” means, but it feels all wrong because of the practical implementation normally used. The normal method is actually a “many to one” relationship using a foreign key – many rows can store the unique identifier of the one object.

Unfortunately, the name “one to many” is also much more popular than “many to one” despite the typical implementation being actually “many to one”.

If this confuses you too, don’t worry. I blame it all on misleading terminology, but you can get used to it. A one to many relationship is essentially the same as a many to one relationship – although some would argue that names reflect a different ownership or control of the relationship. This is true, and as a pedant, I find the argument convincing. For the moment, however, we will ignore it.

The name “one to many” or “many to one” just depends on your point of view. A mouth has a “one to many” relationship with teeth – although with my grandfather it almost became one to one! Teeth have a “many to one” relationship with mouths.

Recap: one-to-many / many-to-one

A “one to many” relationship is normally implemented in a database by storing the unique identifier of the “one” object in a column for each of the “many” objects it is connected to. In other words, the “one to many” relationship is actually stored as a “many to one” relationship. Many different objects can be connected to one object by storing its identifier in a column.

If you really want to model a “one to many” relationship, a separate connecting table is required which can store many rows which link one row in one table to many rows in another table. This is a true one to many relationship in my mind, but in practical terms, the other is exactly the same without the requirement for another separate table. Do you prefer to be pragmatic or picky?