What if your fact has multiple instances of the dimension?

Question

In a star schema for a clothes shop, there is a transaction fact to capture everything bought. This will usually have the usual date, time, amount dimensions but it will also have a person to indicate who bought it. In certain cases you can have multiple people on the same transaction. How is that modelled if the foreign key is on the Fact table and hence can only point to one Person?

Answer 1

The standard technique in dimensional modelling is to use a 'Bridge' table. The classic examples you'll find are groups of customers having accounts (or transactions), and for patients having multiple diagnoses when visiting a hospital.

In your case this might look like:

Table: FactTransaction

• PersonGroupKey
• Other FactTableColumns

Table: BridgePersonGroup

• PersonGroupKey
• PersonKey

Table: DimPerson

• PersonKey
• Other person columns

For each group of people you'd create a new PersonGroupKey and end up with rows like this:

PersonGroupKey 1, PersonKey 5
PersonGroupKey 1, PersonKey 3
PersonGroupKey 2, PersonKey 1
PersonGroupKey 3, PersonKey 6

PersonGroupKey then represents the group of people in the Fact.

Logically speaking, there should be a further table, DimPersonGroup, which just lists the PersonGroupKeys, but most databases don't require this so typically Kimball modellers do away with it.

That's the basics of the Bridge table, but you might consider modifications depending on your situation!