Managing multiple cardinalities
Let’s use the example of a fact table ORDER_FACTS
containing the orders for a PRODUCT
placed on a given DAY
, by a given CUSTOMER
but potentially followed by several salespeople SALESREP
.
How can we model the fact that a fact table has an N-M (several to several) relationship with a dimension?
The solution consists in inserting a table, called a bridge table, in order to manage the multiple cardinalities. This bridge table enables the management of different situations:
- when the N-M relationship is situated between the fact table and a dimension (multivalued dimension)
- when the N-M relationship is situated between two dimensions (multivalued attribute)
Now let’s consider these two situations in greater detail.
Multivalued dimension (N-M relationship between a fact table and a dimension)
Let’s continue with our example in which an order (a row in ORDER_FACTS
) is associated with several of the company’s salespersons. We shall use a bridge table named SALES_GROUP
to resolve this problem by enabling the definition of teams of salespeople. The ORDER_FACTS
fact table will only have a single group to reference, while each group may be associated with several salespeople (figure below).
Note that the SALES_GROUP
table contains an allocation
key, which enables a proportion of the order (irrespective of the chosen measure) to be assigned to the different salespersons.
Reading Multivalued dimension: Star Schema. Christopher Adamson. pages 195, 198-200
Reading Multivalued dimension: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 265-266
Reading Bridge table on managing multiple cardinalities: Star Schema. Christopher Adamson. pages 199-204
Reading Bridge table on managing multiple cardinalities: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 268-276
Multivalued attribute (N-M relationship between a dimension and an outrigger table).
Let’s consider another example in which a CUSTOMER
is associated with a type of INDUSTRY
. A problem arises when several types of industries can be used to qualify the customer.
We solve this problem by placing the bridge table between the CUSTOMER
and INDUSTRY
dimensions (figure below).
Reading Multivalued attribute: Star Schema. Christopher Adamson. pages 195, 207-210
Managing variable-depth hierarchies
Let’s consider the example of a company that manufactures its products on a B2B basis only (i.e. for another company). However, some of its clients are businesses belonging to a more global group, and so on.
It is impossible to know the required hierarchical level for each client. That is why we must use a design template known as a variable-depth hierarchy in this case.
Reading Recursive hierarchy / Variable-depth hierarchy: Star Schema. Christopher Adamson. pages 219-221
Reading Recursive hierarchy / Variable-depth hierarchy: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 76, 175
Reading Bridge table for a variable-depth hierarchy: Star Schema. Christopher Adamson. pages 227-235
Reading Bridge table for a variable-depth hierarchy: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 176-178