Group- and bridge table and additional attributes describing relationship

[jaffse]

Hello.

I am in the process of creating a data warehouse for tracking visits for inmates in prisons. An inmate can have multiple visitors, so I've created a group and bridge table for tracking who participates in each visit. However, each visitor has a relation to the inmate (family, friend, girlfriend etc.) which isn't necessarily the same for each visit (a girlfriend can turn ex-girlfriend). How would you model that? By having additional attributes in the bridge table? The following describes what I have so far.

FactVisit
=========
DateKey (FK)
InmateKey (FK)
VisitorGroupKey (FK)

DimVisitor (SCD1)
==========
VisitorKey (PK)
VisitorSSN (NK)
Name

DimInmate (SCD1)
=========
InmateKey (PK)
InmateSSN (NK)
Name

VisitorGroup
============
VisitorGroupKey (PK)
Visitors (e.g. "256|234|456")
Count

VisitVisitorBridge
==================
VisitorGroupKey (FK)
VisitorKey (FK)
Count

Thank you,

/J

[BoxesAndLines]

Change the grain of the fact table to visitor and create a visit dimension to aggregate visitors. This eliminates the need for a bridge table and solves your inmate relation problem.

[jaffse]

Thanks a lot for your fast reply. So what you're suggesting is altering the FactVisit table to something like

FactVisitor
===========
DateKey (FK)
InmateKey (FK)
VisitKey (FK)
VisitorKey (FK)
RelationshipKey (FK)

I would then have to extract all attributes unique to the actual inmate-visit-visitor relation and add these as dimensions (as done with the relationship attribute)?

Thanks a lot,

/J

[BoxesAndLines]

It doesn't look like you have any attributes for Visit Dimension, so I would just make it a degenerate dimension.