Looking for advise on loading keys into factless fact tables

View previous topic View next topic Go down

Looking for advise on loading keys into factless fact tables

Post  cogplay on Wed Aug 18, 2010 11:28 am

Please consider the following options and recommend an approach. Thanks in advance.

Option 1
PERSON_KEY DEGREE_KEY LANGUAGES_SPOKEN_KEY ROLE_TITLE_KEY
P1 D1 L1 R1
P1 D2 L2 R2
P1 D1 L3 R3
P1 D2 L3 R4

Option 2
PERSON_KEY DEGREE_KEY LANGUAGES_SPOKEN_KEY ROLE_TITLE_KEY
P1 D1 null null
P1 D2 null null
P1 null L1 null
P1 null L2 null
P1 null L3 null
P1 null null R1
P1 null null R2
P1 null null R3
P1 null null R4

cogplay

Posts : 1
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Looking for advise on loading keys into factless fact tables

Post  ngalemmo on Wed Aug 18, 2010 12:10 pm

How about none of the above?

You are mixing 3 different, independent states in a single fact table. What are the true relationships?...

A person has zero or more degrees
A person speaks one or more languages
A person serves in one or more roles

What degree a person has has nothing to do with the languages they speak or their role.
What languages someone speaks has nothing to do with their degrees or roles.
What role a person has has nothing to do with their degrees or languages.

3 fact tables, or more likely, 3 bridge tables.

Also... NEVER have a null foreign key. Rows should reference a 'not applicable' row in the corresponding dimension table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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