A provider naturally has more than 1 member
I think you have that covered already. But can a member have more than one provider?
Based on what you say you have, you should be able to retrieve any member claim record along with the member and provider details if your data would look something like this
tblClaims |
|
|
|
claimId |
claim date |
provider_number |
member number |
1 |
|
1 |
1 |
2 |
|
1 |
1 |
3 |
|
2 |
1 |
4 |
|
2 |
1 |
5 |
|
2 |
1 |
6 |
|
5 |
1 |
The sample data suggests member #1 has several claims with 3 different providers, but does not consider the relationship between provider and claim number. Thus it could possibly require a composite index so that the same claim number could be entered with different providers to allow for the possibility that 2 separate providers could create the same claim reference number (assuming you would not want to repeat a claim/provider combination).
What I think you might be missing is a table for claim details, assuming one claim can deal with more than one type of service (such as dental, drug, orthopedics, etc.).
Hopefully, I haven't misunderstood the parameters of the db requirements.
Last edited by Micron; 04-11-2017 at 02:52 PM.
Reason: clarification
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.