Group- and bridge table and additional attributes describing relationship

View previous topic View next topic Go down

Group- and bridge table and additional attributes describing relationship

Post  jaffse on Tue Oct 04, 2011 7:45 am

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

jaffse

Posts : 2
Join date : 2011-10-04

View user profile

Back to top Go down

Re: Group- and bridge table and additional attributes describing relationship

Post  BoxesAndLines on Tue Oct 04, 2011 9:52 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Group- and bridge table and additional attributes describing relationship

Post  jaffse on Tue Oct 04, 2011 10:30 am

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

jaffse

Posts : 2
Join date : 2011-10-04

View user profile

Back to top Go down

Re: Group- and bridge table and additional attributes describing relationship

Post  BoxesAndLines on Tue Oct 04, 2011 1:55 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Group- and bridge table and additional attributes describing relationship

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum