Join expressions

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 can best be illustrated by examples.

Examples of Join Expressions

Consider a bidirectional one-to-many relationship between tables:

Progress.dbo.ORDER and Progress.dbo.ITEM both have primary key ID (integer) and Progress.dbo.ITEM.ORDER_ID is a foreign key that “points” to primary key Progress.dbo.ORDER.ID. In such case the join expressions would be as follows:

Vocabulary Association	Join Expression
Order.item	Progress.dbo.ORDER.ID = Progress.dbo.ITEM.ORDER_ID
Item.order	Progress.dbo.ITEM.ORDER_ID = Progress.dbo.ORDER.ID

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.

For a multi-column primary key, all key columns must be specified in the join expression; in such case, the join expression becomes a set.

Again, consider a one-to-many, bidirectional association between Progress.dbo.ORDER and Progress.dbo.ITEM, but assume that both Progress.dbo.ORDER and Progress.dbo.ITEM have multi-column primary keys (ID1, ID2), and that Progress.dbo.ITEM also has multi-column foreign key (ITEM.ORDER_ID1, ITEM.ORDER_ID2). In such case, the join expressions would be as follows:

Vocabulary Association	Join Expression
Order.item	{ Progress.dbo.ORDER.ID1 = Progress.dbo.ITEM.ORDER_ID1, Progress.dbo.ORDER.ID2 = Progress.dbo.ITEM.ORDER_ID2 }
Item.order	{ Progress.dbo.ITEM.ORDER_ID1 = Progress.dbo.ORDER.ID1, Progress.dbo.ITEM.ORDER_ID2 = Progress.dbo.ORDER.ID2 }

Note the braces surrounding the comma-separated relational expressions, denoting that in this case, the join expressions are sets.

Finally, consider a bidirectional many-to-many association between two tables:

Such an association will involve a join table, an artificial table not represented in the Vocabulary, whose sole purpose is to associate records in PERSON and ORGANIZATION. Typically, this join table would have a self-documenting name such as PERSON_ORGANIZATION and would contain foreign keys that “point” to PERSON and ORGANIZATION (for example, PERSON_ORGANIZATION.PERSON_ID, PERSON_ORGANIZATION.ASSOCIATION_ID).

In such case, the join expressions would be as follows:

Vocabulary Association	Join Expression
Person.organization	{ Progress.dbo.PERSON.ID = Progress.dbo.PERSON_1.PERSON_ID, Progress.dbo.PERSON_1.ORGANIZATION_ID = Progress.dbo.ORGANIZATION.ID }
Organization.person	{ Progress.dbo.ORGANIZATION.ID = Progress.dbo.PERSON_1.ORGANIZATION_ID, Progress.dbo.PERSON_1.PERSON_ID = Progress.dbo.PERSON.ID }

Again, set notation is used, but instead of multi-column primary keys, the relational expressions describe the relationships between the two tables and the connecting join table.

Inferring Join Expressions

Because join expressions are cumbersome to enter, it is crucial that Corticon 5 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.