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.
A Fact Model
Let's illustrate this by starting with a small pre-existing example model. It concerns employees and projects. An employee is identified by an employee number, and has a first name. A project is identified by a project number and requires a project title. The cardinalities may be used in the generation of the implementation; columns could become nullable or require a value.
We want to add data about which employees are working on which project. In logical modeling this would either require adding a foreign key to table Employee, or adding a foreign key to table Project, or even creating a new table Employee_Project with two foreign keys, depending on how many employees can work on how many projects. No wonder this is an issue at the level of a logical model. In contrast, in Fact Oriented Modeling we simply add another fact type and specify uniqueness constraints, from which the correct foreign keys follow automatically when a logical schema is derived. We illustrate this by adding a fact type that verbalizes the relationship between Project and Employee. Once we have added this fact type, we can use a wizard in CaseTalk to easily find the correct uniqueness constraints.
The diagram below shows how the added fact type (Project Employee) is visualized. Additionally the diagram displays the added constraint to specify projects requires at least one Project Employee (and the employee may have a project employee). These are added to demonstrate cardinality variations in the generation of our foreign keys.
Population Variations
CaseTalk derives foreign key columns adn references and even additional tables using the uniqueness and totality constraints from the fact model. Most important are the uniqueness constraints. Using the user supplied values from the newly added fact type, CaseTalk’s Uniqueness Wizard presents a list of population variations for the modelers to specify whether or not these variations are valid by answering simple yes/no questions - Yes it is a valid population / No it is not a valid population.
A first variation may look like this:
- Employee 9 works on project 99.
- Employee 9 works on project 89.
After answering the question with a simple Yes/No, a second variation is presented to the user:
- Employee 9 works on project 99.
- Employee 8 works on project 99.
Depending on the answers given by the modeler, there can be four different outcomes. We show all variations in answers to illustrate how CaseTalk then generates the appropriate tables and their foreign keys to reflect the fact type Project Employee.
Deriving Foreign Keys
Depending on the validity of above population, as answered by the modeller the following outcomes are possible:
Yes,No
Within the population of fact type Project Employee, every project must be unique.
When the conceptual model is transformed and shown as a logical diagram, the Project Employee fact type is grouped into the Project table. A foreign key is created in the Project table to the Employee table, and the cardinality specifies: An employee can occur in 0 or more projects, and every project must have exactly one employee. For clarity reasons, we also included a UML Class diagram to render another display of cardinality and relations.
No,Yes
Within the population of Project Employee, every employee must be unique.
When as the conceptual model is transformed and shown as logical diagram, the Project Employee is grouped into the Employee table. A foreign key is created from the Employee table to the Project table, and the cardinality specifies: An project must occur in at least one employee, but can occur in more employee records, and every employee may have a project reference. For clarity reasons, we also included a UML Class diagram to render another display of cardinality and relations.
No,No
Within the population of Project Employee, both projects and employees must be unique.
When the conceptual model is transformed and shown as logical diagram, the Project Employee is grouped into the Project table. A foreign key is created from the Project table to the Employee table, and the cardinality specifies: An employee can occur in 0 or 1 project, and every project must have exactly one employee. For clarity reasons, we also included a UML Class diagram to render another display of cardinality and relations.
Yes,Yes
Within the population of Project Employee, every combination of project and employee must be unique. Therefor a project may occur more than once, as do employees.
When the conceptual model is transformed and shown as logical diagram, the Project Employee fact type is created as a linking table between Employee and Project to realize the many to many relationship between the two. Two foreign keys are created in the Project Employee table. For clarity reasons, we also included a UML Class diagram to render another display of cardinality and relations.
Every Project Employee must have 1 employee. And the employee may be in multiple Project Employees.
Every Project Employee must have 1 project. And projects must occur at least once in Project Employee.
Conclusion
So by answering the two questions about possible populations with a simple Yes/No, CaseTalk is able to work out which uniqueness constraints should be placed. Once this has been completed the model can be automatically transformed into a logical model.
Foreign keys are derivable from fact types and their constraints. The constraints themselves can be created using a simple question-and-answer wizard presented to the domain expert and modeler without much hassle of confusion, the modeler is able to communicate the population to the domain experts and the whole discussion about foreign keys has become almost a non-issue.
This article shows how foreign keys and the difficulty with them is easily tackled when using fact based modeling. This is achieved by using the population and the verbalization of these examples in natural language. Even untrained users can answer these questions, eventually leading to correct foreign keys in the final logical model.
