An association represents a relationship between two views. It is defined by two endpoints and a list of mappings. Each endpoint is associated with a view and has a “role name”, a description and a multiplicity. The list of mappings defines the relation between the fields of the two views and allows traversing the association. A mapping is defined by a pair of expressions, each one expressed over the fields of one of the views.

Optionally, you can mark the association as a Referential constraint and set a Role precondition. When you define an association as a referential constraint, the association is considered as the traditional primary key/foreign key constraint of relational databases.

At runtime, users can navigate through associations using navigation queries (SELECT_NAVIGATIONAL) or the RESTful Web service. See more about navigation queries in the section Navigational Queries of the VQL Guide.

Why You Should Define Associations Between Views

When you create base views from a database, the administration tool has the option of automatically import the primary key/foreign key constraints from the database. This is not possible for other types of sources because they do not provide this information or they do not do it on a standardized way.

There are several reasons why it is good to define associations between base views or derived views:

  1. From a performance perspective, some queries are executed much faster; there are some optimizations that the query optimizer can only apply if the appropriate associations are defined.

  2. Virtual DataPort publishes information about the associations marked as referential constraints. If you use a business intelligence tool to query Denodo, this tool may take into account this information to execute more efficient queries.

  3. From a metadata perspective, they help define a better semantic model because you can define referential constraints (primary key/foreign key relation) between views.

    If you are using an external governance tool, the associations of views will provide additional semantics to your model.

  4. From a usability perspective, they simplify the join views definition. When referential constraint associations are defined, the client tools will be able to suggest the associated views as interesting views to add to the join and to automatically create join conditions based on those associations.

  5. From the perspective of the Data Catalog, the associations allow business users to discover views that are related. For example:

    • When querying a view, you can traverse its associations.

    • The associations of the views are displayed in the “Relationships” dialog of the views.

  6. From a usability perspective, using the HTML representation of the RESTful Web service, you can browse across views that have an association between them.