May 12, 2003 at 1:47 pm
Hi,
I have the following tables which describe an organization of clinics, which can be members of particular networks, and the physicians that work at those clinics.
NETWORK_TBL
networkID PK
NETWORKAFFIL_TBL
affiliationID PK
networkID FK
clinicID FK
CLINIC_TBL
clinicID PK
PHYSLKP_TBL
physLkpID PK
clinicID FK
physicianID FK
PHYSICIAN_TBL
physicianID PK
My problem lies with trying to represent the insurance contracts that can be held by the networks or the clinics or the individual providers. The following tables are my attempt to represent these contracts:
INSURANCEPLAN_TBL
insplanID PK
NETWORKCONTRACTS_TBL
ncID PK
networkID FK
insplanID FK
CLINICCONTRACTS_TBL
ccID PK
clinicID FK
insplanID FK
PHYSCONTRACTS_TBL
pcID PK
physicianID FK
insplanID FK
Here is what I am struggling with: physician's effective dates with insurance contracts (whether they're held by the network or clinic or by individual physician) will vary as they come onto the contract and are credentialed. I am at a loss for how to integrate this into the model above. It's almost like I need a
CONTRACTEFFECTIVE_TBL
effectiveID PK
insplanID FK
physicianID FK
clinicID FK - because physicians can be on different contracts if they work at more than one clinic effective date DATETIME
I need to be able to list the insurance plans accepted at each clinic (which can be based on what plans the network accepts, the clinic itself and the physicians that work there) and also the effective status of the physicians for these contracts. I am sorry if this question is a little long but I want to present my problem clearly so maybe someone can help me. I would really appreciate any advice, whether it's to completely alter my above table architecture or to point out that I'm an idiot and this is how simple it is to resolve. At any rate, any advice would be much appreciated.
Thank you for your time,
John
May 15, 2003 at 9:51 am
It's kind of complicated what you are describing and a little confusing. A diagram would help, /images/networkimg.jpg. I changed a few names and for the linking tables, you don't need a separate PK, the two FKs should make a PK.
I think you are basically on the right track, but it could be problematic what you are doing in that you will have lots of data to manage. I do have a question, is the effective date for a plan truly different for a particular physician at a particular clinic for a particular network? In other words, do all of these form some type of PK for the effective date. If so, then you might need all three in there, but that is a ton of data to enter and maintain for users. Or is the effective date between the phys and the plan? If so, then add it to the PhysPlan table.
Hope this helps or there are some other ideas out there.
Steve Jones
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply