1 to many relationship

View previous topic View next topic Go down

1 to many relationship

Post  Jeff Smith on Wed Feb 17, 2010 3:22 pm

I work for a health related insurance company. A member can belong to multiple Groups. A member can have only 1 primary care provider. I am creating a fact table to be used to tell Groups the Frequency at which their members are seeing their provider for maintenance visits regarding their illness. I want to use the same data to tell Providers the Frequency of their patients are coming for maintenance visits.

I figure I have 2 choices: 1) Create a fact table with Member, Group, and Primary Care Provider. Either add a column to weight Members based on the number of Groups they belong to when rolling up the data for Providers (sum Weighted Count) or Count Distinct. Or 2) Created a bridge table with Group and Member and have only the Member and Primary Care Provider. If I create the Bridge Table, I either need to include the Year Month (we are aggregating the data monthly) or create a Begin and End date for the relationship between the Member and Group.

Which option would have the best performance? Option 2 with Begin and End dates feels like the correct answer but I'm wondering if Option 1 would have the best performance. In the Reporting Tool, create "Provider Measures" that multiple the actual measures in the Fact Table by the weighting factor.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 1 to many relationship

Post  ngalemmo on Wed Feb 17, 2010 5:03 pm

I assume by group you mean employer group. Your first choice is the more traditional enrollment model. You would still have effective dates and weighting factors as you mentioned for you second choice as you still have a member-group relationship. The first structure will perform better than the second. I don't see the need to go beyond that.
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