Modeling a dimension table

View previous topic View next topic Go down

Modeling a dimension table

Post  tawnyap on Tue Mar 29, 2016 6:55 pm

I have a scenario  where I have two dimension tables to hold the diagnostic related grouping information. One for the previous version which is Medicare Severity Diagnostic Related Grouping(MS) and the other dimension table to hold the new version that got introduced last year: All patient refined diagnostic related groupings.(APR)

So MS version supports both 32 and version 33, but APR supports just version 33. And the two tables I have include the following attributes. There are some overlapping codes between MS and APR and the weights differ between version 32 and version 33 of MS.

MS
ID       CD       DESC         WEIGHT          DC_CD       DC_DESC       V32_WEIGHT

APR
ID       CD       DESC         WEIGHT          DC_CD        DC_DESC      

I don't seem to understand the need of separating these two into two separate tables, because if they come up with another new name down the lane I don't want to introduce another table but be able to add that information to the existing dimension table. And I have the below two options in my mind and leaning towards using the first approach so I don't have to again snowflake off the type dimension table. Am I on the right track? Is there a reason why having these two as separate tables would make sense rather than combining all versions into one single table? And any advantages of having the type dimension table to include this information as opposed to having the flag columns to help identify the type? As always, thanks for the support.

Option 1:
ID         CD        DESC           WEIGHT         DC_CD        DC_DESC       VERSION_ID      MS_FLAG        APR_FLAG

Option 2:
ID         CD         DESC           WEIGHT           DC_CD        DC_DESC        VERSION_ID       TYPE


Type_dim

ID         NM
1           MS
2           APR

tawnyap

Posts : 2
Join date : 2016-03-29

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