It may sometimes be impossible to model certain situations using a star schema. This problem can be overcome by adding a "bridge table" between two dimensions or between the fact table and a dimension, which will solve some of the problems described in this section.
Prerequisites

- All the fundamental concepts

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?

Source du schéma : Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema. Laurence Corr and Jim Stagnitto. DecisionOne Press, 2011.

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).

Source du schéma : Star Schema. Adamson, Christopher. Osborne/McGraw-Hill, 2010.

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).

Source du schéma : Star Schema. Adamson, Christopher. Osborne/McGraw-Hill, 2010.

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.

Source du schéma : Star Schema. Adamson, Christopher. Osborne/McGraw-Hill, 2010.

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.

Source du schéma : Star Schema. Adamson, Christopher. Osborne/McGraw-Hill, 2010.

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