Data Model objects: Joins

Cardinalities for joins

The cardinality for the joins are specified in accordance with the database design logic for the various entities. For example, An Order Header may not always have a Carrier Service Code. Therefore, the cardinality for the Order Header join with Organization Carrier is 0:1 on the Carrier side.

Additionally, to allow flexibility in reporting, some of the joins have optional cardinality (0:1 or 0:n). This is done so that, in a query, the main entity's data set is not restricted by any lack of data. The Data Model allows changing of the optional cardinality to a direct join at an individual report level. For example, see "Join Cardinality Flexibility".

Join cardinality flexibility

The Item and Item Alias entities have a 1:1-0:n join in the business layer. When a Cognos® 11.1 report containing these two entities is created, it is as follows:

select T1."MANUFACTURER_NAME" as c1,
  T2."ALIAS_NAME" as c2
from ("YFS_ITEM" T1 left outer join "YFS_ITEM_ALIAS" T2 on T2."ITEM_KEY" = 
T1."ITEM_KEY")
--If we now uncheck the Report->Query->Access->”Include the missing table join 
--values (outer join)” check box, the query changes to
select T1."MANUFACTURER_NAME" as c1,
  T2."ALIAS_NAME" as c2
from "YFS_ITEM_ALIAS" T2,
YFS_ITEM" T1
where (T2."ITEM_KEY" = T1."ITEM_KEY")

However, if the join in the business layer was 1:1-1:n instead, the above flexibility would not have been possible to achieve.