Hi @bailey,
I’d like to offer you an idea I had about your issue, but please note that I am not volunteering to implement the solution I propose. I’m already working on at least two other modifications to the CDM and the payer_plan_period table works well enough for me as it currently stands.
However, while I was crafting a proposal for a unified cost table, I kept your issue in the back of my mind and I would be remiss if I didn’t share my thoughts on how I would approach your problem. If you like my suggestions, please feel free to adopt them and implement them.
Even though I’m already on three topics (now four) requesting CDM and vocabulary changes I prefer to make as few changes to the CDM as possible. I’m leery of adding a payer or visit_payer table. However, I’m also not happy with how payers are stored in the CDM. Specifically, I don’t like that we have payer_source_value and plan_source_value columns, but no standard vocabulary to represent payers and plans. There actually is a standard vocabulary for representing payers in the US. Those Payer IDs in the first column are used nationally to uniquely identify a payer. I’d love to see this list become a new Payer vocabulary in the concept table for the v5 vocabularies. My concern is that without a standard vocabulary for payers, each ETL will use different names in the payer_source_value field and we won’t be able to do analysis of payers across different CDMs.
I think creating the Payer vocabulary would remove your need for a payers table. Instead, the payer_plan_period table would have a payer_concept_id column which would store the concept_id of the payer found in the concept table.
Now, here’s where we take it to the next level. One cool thing about vocabularies is you can build hierarchies. So now that we have each payer stored in the Payer vocabulary, we can build a hierarchy using the concept_relationship table to represent the information you were proposing to capture in the plan_class field of your visit_payer table. I.e., payers like “aetna”, “anthem”, “blue shield” would all have a parent concept of “Private/Commercial Payer”. Likewise “Medicare - California” and “Medicare Railroad” would both have a parent concept of “Medicare Payer”. Then, when you want to find all payer_plan_periods that are associated with commercial payers, you’d first find the “Private/Commercial Payer” concept, find the concept_ids of all of its descendants, and look for those concept_ids in the payer_plan_period.payer_concept_id column.
But wait, there’s more!
The unified cost table I’m proposing allows a row in the cost table to be assigned to visit_occurrences, procedure_occurrences, etc. The cost table contains a payer_plan_period_id column. So to associate a visit to a plan, you could use the cost table as a join table between visit_occurrence and the appropriate payer_plan_period. You pretty much already suggested that yourself. But, if you found you needed to associate a specific service to a plan, the unified cost table would allow for that join too. No need for the fact_relationship table.
As for your proposed payer_type column, I’d love to see this too. I imagine we could add a few concepts to the concept table to capture HMO, PPO, etc and then store a reference to those concepts in a payer_plan_period.plan_concept_id column.
As for payer_plan_period_start_date and payer_plan_period_end_date, I’d just store some bogus dates in there or maybe synthesize coverage periods. It doesn’t sound like your analysis cares about those dates anyway.
So ultimately what I’m proposing is that we add payer_concept_id and plan_concept_id to the payer_plan_period table along with some vocabularies to support them. Then we add in a few concepts to let us build a hierarchy around the Payer vocabulary to lump payers into Commercial, Medicare, Medicaid, etc. Lastly, the unified cost table becomes a join table between plans and practically any other domain in the CDM.
