Relation cardinality dictates how relations are generated when you create a logical model, and how they are used to generate underlying SQL JOIN queries when you run reports. Generally, the cardinality is set in the underlying database, but if it is not set up properly there, you can set the cardinality of a relation from the Administrator Dashboard in the Model Editor. For more information, see Modifying a Relation.

Relation Types

When you add relations between entities, ActiveReports 9 Server uses rules to determine cardinality. There are four ways that entities can relate:

One: One-to-one
Many: Many-to-many
OptionalOne: One-to-zero or one
OptionalMany: Many-to-zero or more

If you add relations between entity A (from table A) and entity B (from table B) using the corresponding sets of tables in AC and BC, the rules for determining cardinality are as follows.

Adding relation AB to entity A

If there is a unique constraint on table B that is nullable and exactly matches the columns in BC: OptionalOne.
If there is a unique constraint on table B that is not nullable and exactly matches the columns in BC: One.
Otherwise: OptionalMany. (ActiveReports 9 Server does not use Many in creating relations, but you can set it explicitly.)

Adding relation BA to entity B

If there is no unique constraint on table A that matches the columns in AC: OptionalMany.
If there is a unique constraint on table B that matches the columns in BC, and one of those columns is nullable: OptionalOne.
If there is a unique constraint on table B that matches the columns in BC, and none of those columns is nullable: One.

Example

We can add a relation between the Album and Artist tables using the ArtistID column. Assuming this relation does not exist in the database yet, if we add the relation from the Album entity, we get an Album-to-Artist relation with OptionalOne cardinality and an Artist-to-Album relation with OptionalMany cardinality.

SQL JOIN Types

ActiveReports 9 Server does not take role cardinality into account in determining what JOIN type to use. The relation in the data source controls the behavior.

If the relation belongs to the table that the query joins to, then it uses a LEFT JOIN.
If there is at least one column in the relation that accepts a null value, then it uses a LEFT OUTER JOIN.
Otherwise, it uses an INNER JOIN.

Basically, if it accepts null values, it uses outer joins, otherwise it uses inner joins.

Examples

The Album table has a foreign key constraint linked with the Artist table, there is a relation created for it, and it belongs to the Album table. So long as Album.ArtistID does not accept null, then for queries joining the Album table to the Artist table, we use an INNER JOIN, but for queries joining the Artist table to the Album table, we use a LEFT JOIN. However, if Album.ArtistID is nullable, then even in the case of queries joining the Album table to the Artist table, we use a LEFT OUTER JOIN.

Consider relations based on foreign constraints between Playlist, Track and PlaylistTrack tables via PlaylistID and TrackID. The relations belong to the PlaylistTrack table as they refer to Playlist and Track. Assume for this purpose that CollapseInRelations is not set for PlaylistTrack.

If you drop Playlist.Name and Track.Name to create a table, then the query tree looks like this:

PlaylistTrack

> Playlists

> Tracks

In both cases, it uses an INNER JOIN, as both relations belong to the table (PlaylistTrack) from which the query pulls.

In the case of the Album and Artist tables, the relation connects an Album to an Artist by the ArtistID. So you can drop the Artist.Name attribute and a "Total Albums" aggregate to create a table. If you drop the Name attribute first, then the query looks like this:

Artist

> Album

In this case, it uses a LEFT OUTER JOIN because the relation belongs to the table (Album) from which the query pulls.