Factless fact table with null foreign keys

View previous topic View next topic Go down

Factless fact table with null foreign keys

Post  bfrasca on Tue Nov 01, 2011 9:32 am

I have an unusual scenario. I have a factless fact table of entities. There are three types of entities---institutions (schools), corporations, and persons. The idea is to depict all of the relationships that can exist between these entities. For example, a person can have a relationship with another person, an institution or a corporation. Conversely, there will also be an entry for the reciprocal relationship. There would be six attributes in the table. There are other attributes on the table, i.e. address id, relationship type id etc but these are the significant components of the fact, i.e. they define the type of fact.

PersonID
PersonRelatedToID
Institution
InstitutionRelatedToId
Corporation
CorporationRelatedToID

In any given row only two of those attributes will be populated, an ID and a RelatedToID. Example: A person has a relationship with a school. In this case, the PersonID and the InstitutionRelatedToID would have values and the other four attributes would be null. The reciprocal relationship row would have the InstitutionID and the PersonRelatedToID populated.

I don't typically like to have null foreign keys in a fact table but I'm not sure how else to do this. I suppose I could create a fact table for each type of fact but I would end up with nine fact tables which starts to become a little clunky. Suggestions?

bfrasca

Posts : 1
Join date : 2011-11-01

View user profile

Back to top Go down

Re: Factless fact table with null foreign keys

Post  VHF on Tue Nov 01, 2011 4:09 pm

If you have globally unique IDs on your Person, Institution, and Corporation dimensions you could use a more generic approach:

EntityID
EntityRelatedToID

...where an Entity could be a Person, Institution, or Corporation. Then you would never have null FKs. I would not do this if each dimension has its keys (IDs) assigned inedpendantly, as there would the possiblity of getting incorrect results (oops, I meant Corporation 367, not Institution 367.) But if the IDs are unique across the system there is no possibility of mix-up.

You could add a RelationshipType demension which would identify if the relationship is Person-to-Person, Person-to-Institution, etc.





VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

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