Every now and then a discussion arises on social media, or during a conference meet-up, where the conversation or question focusses on the topic of Foreign Keys. Traditional modelers seem to occasionally struggle with foreign keys in respect to one-to-one, one-to-many, and many-to-many characteristics, or the minimal and maximum cardinality, or its direction. This is not a big issue in Fact Oriented Modeling, since foreign keys are simply generated from a conceptual model. Foreign keys are a result of fact types and their transformations. The direction and cardinalities of the foreign keys are determined by simple rules from the conceptual model.

A logical model is built with tables and columns, some of these columns are foreign keys. The most common situation of a foreign key is a set of columns in one table that refer to the primary key columns in a different table. A foreign key forces its values to exist elsewhere, namely the primary key it refers to. However, foreign keys do not exist in a conceptual data model: they are automatically derived from other parts of conceptual data models. For example, Fact Oriented Modeling only knows Fact Types and Label Types. In addition to a few constraints, such a conceptual model is used to generate tables and columns (including foreign keys columns).

This article explains how Fact Types and a slight variation in constraints generates very different foreign key implementations.

CaseTalk

We make IT better. Together!