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?

score 2 · Answer 1 · edited Feb 08 '18 at 17:41

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!

Beside the naming, how is that different from the answer I posted previously? — Alexis.Rolland, Feb 08 '18 at 16:26
Your answer yesterday had a transaction Id in the bridge table. I thought it worth posting my own answer rather than needing you to edit yours — Rich, Feb 08 '18 at 16:27
Fair enough, you deserve the upvote ;-) I got a downvote on mine after correcting it, not too sure why — Alexis.Rolland, Feb 08 '18 at 16:29

score 1 · Answer 2 · answered Feb 07 '18 at 22:34

1

You need a joining table TransactionPerson (or something like that), where Person to TransactionPerson is 1:M relationship and then TransactionPerson to Transaction is M:1 relationship.

That way you can have multiple people relating to one transaction indirectly.

answered Feb 07 '18 at 22:34

Vidmantas Blazevicius

4,652
2
11
30

That makes sense. Do you have a link which shows this approach and usually the stuff I find doesn't have joining tables between the facts and dimensions. – More Than Five Feb 07 '18 at 23:02
@MoreThanFive Joining tables (also called mapping tables) are very common solution to normalizing many-to-many (M:M) relationship into two one-to-many (1:M) relationship. A biproduct of this normalization is an extra joining table. If you google how to normalize many-to-many, pretty much everything should point you to this approach. – Vidmantas Blazevicius Feb 07 '18 at 23:10

Alexis.Rolland · Answer 3 · 2018-02-08T16:23:17.243

1

I would propose to use a Bridge table in combination with your transaction and person tables. Ex:

Table: fact_transaction

transaction_id (primary key)
transaction_person_id (foreign key)
...

Table: bridge_transaction_person

transaction_person_id
person_id

Table: dim_person

person_id (primary key)
...

edited Feb 08 '18 at 16:23

answered Feb 08 '18 at 00:10

Alexis.Rolland

5,724
6
50
77

1

The standard Bridge table in dimensional modelling wouldn't rely on having information about the fact in the bridge table (i.e. there would be no transaction id). Instead, the bridge table would represent the group of people, and the fact table would have a foreign key to that. – Rich Feb 08 '18 at 07:52
@Rich ok maybe, I am not an expert on bridge tables, in this case I will remove my last comment and adjust the solution – Alexis.Rolland Feb 08 '18 at 07:54

What if your fact has multiple instances of the dimension?

3 Answers3