Factless fact table with null foreign keys
2 posters
Page 1 of 1
Factless fact table with null foreign keys
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?
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
Re: Factless fact table with null foreign keys
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.
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
Similar topics
» Derived Fact table with additional measures / foreign keys ... ?
» Fact Table with huge number of Blank (or Empty) foreign keys
» Business keys or Natural keys in the Fact table
» Looking for advise on loading keys into factless fact tables
» NULL Values in Fact Table
» Fact Table with huge number of Blank (or Empty) foreign keys
» Business keys or Natural keys in the Fact table
» Looking for advise on loading keys into factless fact tables
» NULL Values in Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum