References in data marts

A reference is a join between two database tables that indicates how the tables in the data mart are related to each other.

One-to-many joins

A one-to-many join connects the columns of a primary key, unique constraint or unique, nonnullable index of the parent table with columns of the child table. Any row or tuple in the child table is related to a maximum of one row or tuple in the parent table. If the table has a primary key, the corresponding key columns are selected automatically. You can override this automatic selection by selecting another unique constraint or unique index.

If the parent table does not have a primary key, select one of the unique keys. At least one unique constraint or unique nonnullable index is required, otherwise the one-to-many reference cannot be created.

Important: One-to-many joins lead to a better query performance than many-to-many joins. If one of the tables that you want to use has a unique constraint, unique index, or primary key on the join columns, use a one-to-many join.

Many-to-many joins

In a many-to-many join, one or more columns of the parent table are joined with an equal number of columns in the child table. The values of these columns do not have to be unique and you do not have to enforce uniqueness through the selection of a constraint. This means that any row or tuple in the child table can relate to multiple tuples in the parent table.