Each association in a Corticon Vocabulary will have a join expression that is used to establish the relationships between matching columns in the database. The syntax is similar to the SQL WHERE clause and are illustrated here by examples.
One to Many Association with Single Primary Keys
The samples in this guide have a bidirectional one-to-many relationship between tables:
PatientRecords.dbo.Patient has the integer primary key patientId, and PatientRecords.dbo.Treatment has treatmentId as its primary key. PatientRecords.dbo.Treatment.patientId is a foreign key that “points” to primary key PatientRecords.dbo.Patient.patientId. In such case the join expressions would be as follows:
Note that in a bidirectional association, the two join expressions are mirror images of one another. Unlike ANSI SQL, the order of operands in the join expression is significant.
In Corticon Studio, the sample association mapping in the data integration samples is:
A closer look at the expression shows the correct inferred join expression for the Patient:
Clicking opens the Join Expression dialog for the connection, as shown:
If you want to revise the expression, each field entry area opens a dropdown menu for that field where you can choose a value that is in the scope of the connection, or clear the value. You can also just click in the box and enter a value.
One to Many Association with Multiple Primary Keys
Consider the sample as having a multi-column primary key. All key columns must be specified in the join expression; in such case, the join expression becomes a set.
This is a one-to-many, bidirectional association between PatientRecords.dbo.Patient and PatientRecords.dbo.Treatment, where both have multi-column primary keys (patientId, patientName, treatmentId, PatientCode), and PatientRecords.dbo.Treatment also has multi-column foreign key (Treatment.patientId, Treatment.patientName). The join expressions would be as follows:
Note the braces surrounding the comma-separated relational expressions, denoting that in this case, the join expressions are sets. To extend the Patient association to enter the multiple keys, add another line in the Join Expression dialog box, as shown:
In this case, the join expression was extended as shown:
When you click OK, the expression is constructed as shown, a bit hard to read yet exactly as described:
Let's create the same construct using simple tokens:
The resulting join expression is:
The braces surround the comma-separated relational expressions: The join expressions are sets.
Best effort at inferring Join Expressions
Because join expressions are cumbersome to enter, it is crucial that Corticon have the best possible logic for automatically inferring them from metadata. For one-to-many associations, the join expression can frequently be inferred from primary and foreign key metadata, assuming that the entities can be successfully mapped to particular tables, and the foreign key relationships between those tables are properly declared. Exceptions to this rule include:
Unary one-to-one associations (that is, self-joins), where it is impossible to infer which side of the association corresponds to the primary or foreign key
Unary many-to-many associations, where it is impossible to infer which of the join table foreign keys should be used for each side of the association
Tables that have multiple foreign key relationships between them with different meanings for each.
Corticon recognizes when it is not possible to unambiguously infer the proper join expression, and allow the user to choose from a set (drop-down list) of choices.
Corticon infers the join expressions in all cardinalities.
opens the Join Expression dialog for the connection, as shown:
Unary one-to-one associations (that is, self-joins), where it is impossible to infer which side of the association corresponds to the primary or foreign key