We have reviewed data modelling connection types over the past several weeks (see Connections) and seen that a few simple names can describe different types of connections in a general way:

To some extent, these data modelling connection types can be enforced or controlled by constraints in a database, but it is only the simple configurations that can, and even so, the constraints are not as simple as you might expect:

  • A mandatory one-to-one relationship can be implemented as a foreign key relationship enforced as not being null. Thus each object must store the unique identifier of a connected object of the correct type. A uniqueness constraint must also be added to make sure that each row stores the identifier of a different connected object. A little messy, but relatively simple.
  • An optional one-to-one relationship can also have a foreign key relationship in the same way, but since it is optional, a null or empty value can also be accepted. Simple. The complicated part is in making sure that only one foreign key entry can point to any connected object. With some database management systems this can be easily done with a nullable column. However others require extra effort and it can be quite difficult to get the protection you would like while using standard constraints or indexes.
  • A many-to-one relationship is probably easiest of them all. A foreign key from the “many” end of the relationship stores the unique identifier of a row in the table for the “one” end of the relationship.
  • A one-to-many relationship is normally created as a many-to-one relationship as discussed above. This is easy as long as it is an optional connection. However, if each row in the “one” end table must appear in the foreign key column of at least one row in the “many” table, enforcing this is more complex.
  • A many-to-many relationship uses an intermediate table with two foreign keys, one pointing to each end of the relationship. Each connection requires a row in this table and constraints can make sure there are no duplicate connections if this is necessary. The connection table can also store additional information about the connection as required.

Through examples, we have already seen that the exact number of connected items can often be very important. For example, most cars require four wheels. A fifth, spare, wheel may be an optional extra, but the first four are mandatory. There is no simple connection name that indicates such a connection type. Ordinarily in a database, this would be stored as a many-to-one relationship with four wheel records using a foreign key reference to point to a car record. However, no simple database constraint can make sure that 4 or 5 objects are connected, no more and no fewer. This must be done through other means in the database or in some controlling software that supervises data changes.

Having limits on the number of connected objects is very common. Upper limits and lower limits, but often not just the relatively easy zero or one.

Grammatical Aside

In English, we often show cardinality of relationships through the use of plurals and the location of apostrophes:

  • Doctor’s patient – one doctor, one patient
  • Doctors’ patient – more than one doctor, one patient
  • Doctor’s patients – one doctor, more than one patient
  • Doctors’ patients – more than one doctor, more than one patient

Even English grammar can help us when studying data modelling connection types!