You are a small database consulting company
specializing in developing databases for the medical industry. You have just
been awarded the contract to develop a data model for a database application
system for a mid-size health insurance company to keep track of health claims
including patient information, provider (doctor) information, and information
about patient visits to their doctor as well as prescription drugs prescribed
to patients.
Information
such as patient name, address, phone, email etc. are needed as well as who each
patient’s primary care doctor is, their insurance ID number and insurance
company name. We also want information on each doctor such as their specialty
and what hospitals they are affiliated with as well as their phone, address etc.
Regarding the hospitals themselves we will need to know where they are located
and how to contact them.
The
prescriptions given to each patient by a health-care provider also need to be
tracked in this particular database at this time to determine claim eligibility
including some basic information on the drug being prescribed to make sure
there are no conflicts with a patient’s other prescriptions. We need to know
each drug’s name, purpose/use and possible side effects.
https://drive.google.com/open?id=1DRCaFVvwh431AiA9_LHo4bVgz6jxXu-CEfht9P4qoyo
General Comments:
1)
In Health Insurance domain, there are health services
organizations e.g. Cigna. And they provide plans through organizations e.g. starmark, unitedhealth and corbenser [one of
these could be virtual client for me, say starmark].
2)
“INSURANCE
PROVIDER” here is organization like ‘Cigna’. It may offer “MEDICAL PLAN” like “Cigna PPO S”.
3)
Generally, companies like starmark, for whom I am
designing this db, talk to Pharmacy Services (“INSURANCE PROVIDER”) e.g. ‘LDI Integrated’ to back up their
offering with a “DRUG PL AN” for
drug prescriptions. It doesn’t mean “MEMBER”
can buy medicine only from ‘LDI’.
There are “PROVIDER”
of “PROVIDER TYPE” pharmacy e.g. ‘CVS’,
which are bounded under “COVERAGE TYPE”
in-network to “INSURANCE PROVIDER” e.g.
‘LDI’. Such a relation is stored in “SERVICE
AVL COVERAGE CATEGORY”.
4)
Sometimes “MEDICAL PLAN” e.g. ‘Cigna PPO S’ does not cover vision. Companies
like starmark, for whom I am designing this db, talk to “INSURANCE PROVIDER” e.g. ‘VSP’ to back up their members with a separate
vision plan.
5)
Sometimes “MEDICAL
PLAN” e.g. ‘Cigna PPO S’ does not cover dental. Companies like starmark,
for whom I am designing this db, talk to “INSURANCE
PROVIDER” e.g. ‘UNUM Concorida’ to
back up their members with a separate dental plan.
6)
There are some “MEMBER”
who take multiple “MEDICAL PLAN” to backup unfortunate events, they are afraid
of copay’s, deductibles, out of pocket etc. so much that, to cover such things
they take one more “MEDICAL PLAN” !!
So,
a single “MEMBER” may end up having multiple
“INSURANCE PLAN” which may be “MEDICAL PLAN”, another “MEDICAL PLAN”, a “DRUG PLAN”, a “VISION PLAN”
and a “DENTAL PLAN”. The
structure of entity “MEDICAL PLAN”,
“DENTAL PLAN” and “VISION PLAN” is exactly same. So, I
need not identify them separately – those may be identified by “SERVICE COVERAGE BY PLAN”. Only “DRUG PLAN” behaves differently. So, I create separate entity for “DRUG PLAN”.
7)
In the discussion above, “SERVICE COVERAGE BY PLAN” is an open end till now. Let’s take it up
further. A “MEDICAL PLAN” e.g.
‘Cigna PPO S’ may cover a “SERVICE”
like ‘knee rod installation’ 100% in network, but only 70% out of network. The same plan may not cover “SERVICE” like ‘lasik eye surgery’ at
all, so no instance in “SERVICE COVERAGE
BY PLAN” for “SERVICE” ‘lasik eye surgery’ and “MEDICAL PLAN” ‘Cigna PPO S’. This signifies importance of
entity “SERVICE COVERAGE BY PLAN”.
8)
“SERVICE”
contains master data of all services all over the globe. [Even a slight
difference in how service is provided, creates a new instance, yes that is how
health insurance domain works, same treatment, one “INSURANCE PROVIDER” does under one code, but another “INSURANCE PROVIDER” uses another code.
They may also use same code. I myself experienced this with a dental claim for
my dependent, which was fixed later on and I got reimbursements. This is dirty
and we keep this information out of our so called ideal ERD world here.]
9)
“INSURANCE PROVIDER” offer “PLAN” grouped together under “INSURANCE GROUP”. This “INSURANCE GROUP” has
different significance in different scenarios, for example in one case it could
be used to group together all the offerings done to a company say AT&T, in
other case it may be used to give some discount to a backward community.
10) Let’s come back to “MEMBER”
entity now. It stores all the information related to plan user, multiple
instances of “MEMBER” may have been
availing insurance under a main insured “MEMBER”.
11) A “MEMBER” may be availing multiple “PLAN” by virtue of main insured “MEMBER”. The “PLAN”
instances used by a primary “MEMBER”
instance are stored in “MEMBER PLAN MAP”
entity.
12) “PROVIDER”
may be a hospital, laboratory, doctor, Emergency care Unit and so on. This is identified
by “PROVIDER TYPE”. So, it is quite
possible that, under a ‘hospital’[ “PROVIDER”
of “PROVIDER
TYPE” hospital ] , multiple ‘doctor’[ “PROVIDER”
of “PROVIDER
TYPE” doctor] are servicing.
13) “PROVIDER” gives “SERVICE” is tracked by “SERVICE
AVAILABLE”. This also tracks if “PROVIDER”
has specialty in that “SERVICE”.
14) For a “SERVICE”, an instance of “INSURANCE
PROVIDER” does not go above allowedlimt in any case; rest extra must be
taken care by “MEMBER”. This data
stored in “INSURANCE SERVICE LIMIT”.
This is irrespective of what “PLAN”
member has.
15) “TRACK PRIMARY” is used to record “PROVIDER” of “PROVIDER TYPE” ‘doctor’ for
a “MEMBER” with time tracking.
16)
“DRUG
LABEL” is generated from “PRESCRIPTION”.
To disburse the medicine to the “MEMBER”.
A “PRESCRIPTION”
may generate multiple “DRUG LABEL”.
17) A “SERVICE”
is provided to “MEMBER” either with
a “VISIT” or without it. Say, I got
a “DRUG LABEL”, without a need of “VISIT” the “PROVIDER” of “PROVIDER TYPE”
pharmacy. “SERVICE” availed by a “MEMBER” is recorded in “SERVICE PROVIDED”. Getting “DRUG” is also a service and has a “DRUG
LABEL” generated. One “VISIT”
involves giving one or more “SERVICE”
to “MEMBER”. “SERVICE PROVIDED” track all the “SERVICE” provided.
18) When a “MEMBER” “VISIT” a “PROVIDER” of “PROVIDER TYPE”
‘doctor’, he may get one instance of “PRESCRIPTION”
or multiple. To get a “PRESCRIPTION”, “VISIT” is not mandatory.
19)
“PRESCRIPTION”
is given for a “MEMBER”, includes a
specific “DRUG” given by a specific
“PROVIDER” of type doctor and these
mentioned relationships are non-transferable. Subtype “RE-FILLABLE” of suptertype “PRESCRIPTION” will have PillsCountPerRefill,
NoOfRefillsAvailable as extra attributes. Rest “PRESCRIPTION” fall under “NON-REFILLABLE”.
But, problem specifically mentions to use arc for this instead of super type-subtype.
20) Some “MEMBER”’s “VISIT” are of subtype “NEW ILLNESS”, some are subtype “FOLLOWUP”, some are of subtype “ROUTINECHECKUP”. There is a subtype “OTHER” to track other trackable visits.
We would like to be able to track which type of visit each instance is so we
can keep specific information regarding the visit.
21) A single “CLAIM” must have one or more “SERVICE CLAIMED”. A “CLAIM”
must have all the “SERVICE CLAIMED”
by same “PROVIDER”. Say I visited a
hospital. Three doctors worked on my surgery giving tons of “SERVICE PROVIDED”. No one stops “SERVICE PROVIDED” being claimed under
separate “CLAIM” instances, even if
they are by same “PROVIDER”. Every “CLAIM”
has one “CHECK” issued which can be
given only to one “PROVIDER”. It could be a doctor name, a hospital name or
else. In practice getting check issued at the end is mandatory, it includes a
check no with zero amount if nothing is paid to “PROVIDER”, but ERD will keep CLAIM side of this relationship
optional, because during the central program engine is processing the claim
which takes sometimes many weeks, there is no check available.
SERVICE AVL COVERAGE CATEGORY (SAY)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
ins comp id
|
|
pk, fk2
|
*
|
service code
|
|
pk, fk2
|
*
|
provider number
|
|
○
|
discount agreed
|
||
fk3
|
*
|
category code
|
|
Description/Assumptions
for SAY:
|
|||
Each services
offered/available by Provider needs to be categorized as in-network or
out-network [ this is at insurance provider level e.g cigna]. I once went to
an Urgent Care, all the services treated as primary care were covered by my
insurance provider as in-network but the urgent care services were out of network.
It gives an idea that, for a PROVIDER, each services should be listed as
in-network or out-network in DB.
|
|||
SERVICE AVAILABLE (SAE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
provider number
|
|
pk, fk2
|
*
|
service code
|
|
*
|
is speciality
|
||
Description/Assumptions
for SAE:
|
|||
Out of many services in
the world, some services a provider gives, other he won't, weather it is
covered by my insurance or not that is another story.
|
|||
COVERAGE TYPE (CTE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
category code
|
|
*
|
name
|
||
Description/Assumptions
for CTE:
|
|||
This entity has instances
of 'out of network' and 'in network'
|
|||
SERVICE CLAIMED (SCD)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
scd number
|
|
fk1
|
○
|
ineligibility code
|
|
fk2
|
*
|
memeber id
|
|
fk3
|
*
|
claim number
|
|
fk4, uk
|
*
|
spd id
|
|
Description/Assumptions for
SCD:
|
|||
1. Each SERVICE PROVIDED
is finally claimed as one SERVICE CLAIMED, and CLAIM must contain one or more
SERVICE CLAIMED. a provided service once claimed under a SERVICE CLAIMED is
non transferable.
2. If a SERVICE CLAIMED instance is calculated as an invalid/not covered, a proper ineligibility code is used here for the reason. 3. Instance must be created for a MEMBER. It is non-transferable. 4. It must be raised under a CLAIM and is non-transferable. 5. A reference to SERVICE PROVIDED is mandatory to approve/reject the claim. Some of the validation applied using this reference in programming : a) if it is a DRUG LABEL, was the drug covered under drug plan, is the user being given this medicine too often to alarm that MEMBER is selling this medicine to black market, what kind of medicine it is - generic, preferred or non-preferred and so on. b) if it is a service given during a doctor visit, is the service start date and end date matches with the visit/admission dates. is it covered under any of the plans available to the member, to which extent and so on 6. One SERVICE CLAIMED may use one or more insurance plans to the member. For example, if no plan covers this service provided , plan applied will be nothing. for same service one plan is treated as major and other plan covers members responsibilities under first plan (COB). And so on 7. Calculated by program, based on plans applied and what member has to pay, member's ACCUMULATOR for current year change. Note: it doesn't matter MEMBER pay the PROVIDER his due part or not, that is added to ACCUMULATOR. It's PROVIDER responsibility to collect his dues, not of INSURANCE PROVIDER |
|||
CLAIM (CLM)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
claim number
|
|
○
|
adjustment
|
||
○
|
adjustment description
|
||
*
|
claim date
|
||
Description/Assumptions
for CLM:
|
|||
A CLAIM instance must be
generated for one or more SERVICES CLAIMED to be be provided and a SERVICE
once claimed under a claim, it will continue till end - non
transferable. Since one claim
generates only one claim, programmatically it is ensured that, all SERVICE
CLAIMED will pay to same provider. claim date is re cored since insurance
companies are bound to SLA's to give decision. Sometimes a claim is so
special that it needs special adjustments, for that amount is recorded with
clear description from agent.
|
|||
CHECK (CHK)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
check id
|
|
*
|
issue date
|
||
*
|
amount
|
||
*
|
receiver name
|
||
*
|
cancelled
|
||
fk
|
*
|
claim number
|
|
Description/Assumptions
for CHK:
|
|||
1. A check is generally
identified by issuer bank, account no and check no, but I am identifying here
with checkid only
2. 3. Check issued is nontransferrable. 4. A second check for a claim is issued only if earlier check is cancelled or lost for some reason. Default value of cancelled is false |
|||
PLAN APPLIED (PAD)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
scd number
|
|
pk, fk2
|
*
|
ins comp id
|
|
pk,fk2
|
*
|
memeber id
|
|
pk, fk2
|
*
|
plan code
|
|
pk, fk2
|
*
|
groupid
|
|
*
|
cob
|
||
Description/Assumptions
for PAD:
|
|||
1. For this entity cob is
true if the plan used is covering member's responsibilities of a claim where
primary insurance applied didn't covered it fully. This is calculated
programmatically.
2. Here the member id is coming from MEMBER MEDICAL PLAN. This must also checked to match with member id in SERVICE CLAIMED 3. Plan applied is the best options identified programmatically based on relation b/w provider and insurances involved. The best options identified here means the ACCUMLATORS will be increamented minimum due to a SERVICE CLAIMED for that MEMBER. 4. It is quite possible that SERVICE CLAIMED gets ineligible in absence of any MEMBER PLAN MAP qualified to be applied. 5. The relations are kept transferrable here, because MEMBER is allowed to challenge a SERVICE CLAIMED to reduce his responsibility part. |
|||
MEMBER PLAN MAP (MPP)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
memeber id
|
|
*
|
premium
|
||
*
|
member since
|
||
○
|
termination date
|
||
pk, fk2
|
*
|
plan code
|
|
pk, fk2
|
*
|
groupid
|
|
pk, fk2
|
*
|
ins comp id
|
|
Description/Assumptions
for MPP:
|
|||
frequency of premium is
in $ per month for all plans, it is some of both employer share and member
share- no matter who pays it.
termination date is optional here to add currently active plan applied. |
|||
ACCUMULATOR (ACR)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk
|
*
|
plan code
|
|
pk, fk
|
*
|
memeber id
|
|
pk, fk
|
*
|
groupid
|
|
pk, fk
|
*
|
ins comp id
|
|
pk
|
*
|
year
|
|
*
|
type
|
||
○
|
in ntwrk deduct max indvidual
|
||
○
|
in ntwrk out of pckt max indv
|
||
○
|
out ntwrk deduct max indvidual
|
||
○
|
out ntwrk out f pckt max indv
|
||
○
|
deductible
|
||
Description/Assumptions
for ACR:
|
|||
1. type column
defines if this amount is accumulated as DRUG PLAN ACCUMULATOR or MEDICAL
PLAN ACCUMULATOR.
2. There is no need to save below mentioned coming from MEDICAL PLAN ACCUMULATOR: .out ntwrk out f pckt max fam .out ntwrk deduct max family .in ntwrk out of pckt max fam .in ntwrk deduct max family If family configuration changes during an year, these figures will change. Like divorces, getting new job etc. 3. deductible is coming from DRUG PLAN ACCUMULATOR 4. check constraints are used to verify that each instance belong to one and only one sub type. The values in this ACCUMULATOR don't have a tracking done i.e which SERVICE CLAIM changed the entry. If that kind of tracking is needed, better is save the amounts in SERVICE CLAIM table and drop ACCUMULATOR all together. And calculate ACCUMULATOR at run time programatically. |
|||
INELIGIBILITY EXPLANATION (IEN)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
ineligibility code
|
|
*
|
title
|
||
○
|
description
|
||
Description/Assumptions
for IEN:
|
|||
Programatically,
ineligibility code for a SERVICE CLAIM is chosen and referenced in SERVICE
CLAIM, if need be. This is master table to store reasons.
|
|||
BILLING CATEGORY (BCY)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
billing cat code
|
|
*
|
name
|
||
Description/Assumptions
for BCY:
|
|||
A visit to same doctor
could be billed as urgent care and I pay elevated copay, same doctor in
emergency room - I give highest copay. So how a visit is being billed must be
tagged. This may also affect the service codes programatically chosen for the
action done in that visit.
|
|||
VISIT (VIT)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
visit id
|
|
uk1
|
*
|
start time
|
|
○
|
end time
|
||
fk1, uk1
|
*
|
memeber id
|
|
fk2, uk1
|
*
|
provider number
|
|
fk3
|
*
|
billing cat code
|
|
*
|
type
|
||
○
|
blood pressure systolic
|
||
○
|
blood pressure diastolic
|
||
○
|
height
|
||
○
|
weight
|
||
○
|
initial diagnosis
|
||
○
|
current patient condition
|
||
Description/Assumptions
for VIT:
|
|||
1. subtype OTHER covers
everything else than specifically identified here.
2. FOLLOWUP , NEW ILLNESS and ROUTINE CHECKUP lost there mandatory feature. 3. A doctor once visited by patient is visited, non-transferable 4. end time is kept open end if I am in hospital when claims are being processed.Visit not yet ended. But if end time is not null, it should be greater than start time of visit. 5. check contraint is used to mandate blood pressure systolic,diastolic , height,weight in case type is routine checkup - in this case initial diagnosis and current patient condition must be null as verified by check. In this check similarly, integrity of other sub type is verified. 6. Every VISIT has at least one SERVICE IN VISIT. In-fact in bills, even if doctor don't even touch you or prescribe anything, he charges big amounts to insurance as visiting fee. |
|||
SERVICE PROVIDED (SPD)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
spd id
|
|
fk1
|
*
|
service code
|
|
fk1
|
*
|
provider number
|
|
*
|
service end date
|
||
*
|
service start date
|
||
*
|
type
|
||
fk2
|
○
|
visit id
|
|
fk3
|
○
|
prescription number
|
|
○
|
label number
|
||
○
|
medicine expiration date
|
||
Description/Assumptions
for SPD:
|
|||
1. Primary key is spd id and used further in SERVICE CLAIMED. It
is possible that SERVICE PROVIDED is not claimed at all. But one SPD goes only
to one instance of SERVICE CLAIMED
2. service code and provider number make one composite foreign key. In case of 3. service must be completed before coming here. start time must be less than end time. 4. Type is mandatory to identify if it is drug label service or service provided in a visit. 5. check constraint is used to verify integrity as a suptype, each row must belong to either SERVICE IN VISIT or DRUG LABEL. a) visit id is mandatory for SERVICE IN VISIT but stored hare as non-mandatory. b)label number is mandatory for a label but stored here as non-mandatory and non-unique. c) prescription number mandatory for LABEL but stored hare as non-mandatory. d) medicine expiration date is mandatory for a label but stored here as non-mandatory 6. Programmatic check on label number is required to verify that it is unique for the LABEL entity. |
|||
PROVIDER TYPE (PTE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
type code
|
|
*
|
name
|
||
Description/Assumptions
for PTE:
|
|||
A provider could be of
type doctor, hospital, pharmacy, lab etc
|
|||
PROVIDER (PVR)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
provider number
|
|
*
|
name
|
||
*
|
payment name
|
||
fk1
|
*
|
ctt id
|
|
fk2
|
*
|
type code
|
|
AFFILIATION (AFE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
hospital
|
|
pk, fk2
|
*
|
doctor
|
|
Description/Assumptions
for AFE:
|
|||
1. A doctor must be
affiliated with at least one hospital, if he wish he may get affiliated from
multiple hospitals. But if Provider is not a doctor, he need not any
affiliation, that is why relation show as optional on PROVIDER side in ERD.
Additional programming required here to handle optionality of a doctor here.
2. Hospital must have at least one doctor affiliated and working there, but doctor won't affiliate another doctor, that is why additional programming here too. |
|||
SERVICE (SVE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
service code
|
|
*
|
name
|
||
*
|
description
|
||
MEMBER (MBR)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
memeber id
|
|
uk
|
*
|
social secuirty number
|
|
*
|
date of birth
|
||
*
|
gender
|
||
*
|
first name
|
||
*
|
last name
|
||
fk1
|
○
|
parent member
|
|
fk2
|
*
|
ctt id
|
|
Description/Assumptions
for MBR:
|
|||
1. parent member column is
optional because, for the head of family himself who bought this insurance,
this is blank
|
|||
TRACK PRIMARY DOCTOR (TPR)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
memeber id
|
|
pk, fk2
|
*
|
provider number
|
|
*
|
start date
|
||
○
|
end date
|
||
Description/Assumptions
for TPR:
|
|||
1. MEMBER who never had
been a patient till now can't have a PRIMARY doctor selected till now.
2. end date can be blank only for one record for a MEMBER and that identify current primary doctor. |
|||
SERVICE COVERAGE BY PLAN (SCN)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
ins comp id
|
|
pk, fk1
|
*
|
groupid
|
|
pk, fk1
|
*
|
plan code
|
|
pk, fk2
|
*
|
service code
|
|
*
|
in network covered share
|
||
*
|
out network covered share
|
||
Description/Assumptions
for SCN:
|
|||
1 SERVICE AVL COVERAGE
CATEGORY was to see if a provider is in-network or out-network for an
INSURANCE PROVIDER for a SERVICE.
2. Now every plan give different % of coverage for a service. It could say this plan will cover 70% of the cost if this particular service is given out of network, if it is given in-network, insurance will cover 90%. for another service in-network could be 80% and out-network could be 20% and so on. 3. It is quite possible that for a service an insurance company has not yet made such a decision for every plan. |
|||
INSURANCE SERVICE LIMIT (IST)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk1
|
*
|
ins comp id
|
|
pk, fk2
|
*
|
service code
|
|
*
|
allowed amount
|
||
Description/Assumptions
for IST:
|
|||
Sometimes a SERVICE is so
much overcharged by PROVIDERs every time that, the INSURANCE PROVIDER as a
whole, come up with allowed amount per SERVICE basis and enforce it on all
PROVIDERS in claim procedure. Anything above allowed amount is considered
patient's responsibility and settled b/w patient and provider personally.
|
|||
INSURANCE PROVIDER (IPR)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
ins comp id
|
|
*
|
name
|
||
fk
|
*
|
ctt id
|
|
Description/Assumptions for
IPR:
|
|||
1. e.g. cigna
2. ctt id defines its address and contact details |
|||
INSURANCE GROUP (IGP)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
ins comp id
|
|
pk
|
*
|
groupid
|
|
○
|
medical group discount
|
||
○
|
drug group discount
|
||
Description/Assumptions
for IGP:
|
|||
1. drug group discount
also contain the universal discount offered on medicines to everyone
everywhere, in addition to special discount on drugs negotiated by the group.
|
|||
PLAN (PLN)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
ins comp id
|
|
pk
|
*
|
groupid
|
|
pk
|
*
|
plan code
|
|
*
|
precondition addendum
|
||
*
|
type
|
||
○
|
max deductible on drug
|
||
○
|
deductible applied on generic
|
||
○
|
deduct applied on preferred
|
||
○
|
deduct on non preferred
|
||
○
|
copay primary
|
||
○
|
copay specialist
|
||
○
|
copay urgent care
|
||
○
|
copay emergency
|
||
○
|
in ntwrk deduct max indvidual
|
||
○
|
in ntwrk out of pckt max indv
|
||
○
|
out ntwrk deduct max indvidual
|
||
○
|
out ntwrk out f pckt max indv
|
||
○
|
in ntwrk deduct max family
|
||
○
|
in ntwrk out of pckt max fam
|
||
○
|
out ntwrk deduct max family
|
||
○
|
out ntwrk out f pckt max fam
|
||
Description/Assumptions
for PLN:
|
|||
1. type
identifies if it is DRUG PLAN or MEDICAL PLAN
2. check constraint is required to implement subtypes here: a) For DRUG PLAN attributes marked in yellow are mandatory b) for MEDICAL PLAN attributes marked in green are mandatory 3. Drug plan do not consider family. they work on individual. 'max deductible on drug' identify maximum money member will pay before medicine getting free for him. rest of the three properties in DRUG PLAN in yellow identify if deductible is applied before making that kind of medicine fully included. generally I have seen 'deductible applied on generic' as false - means generic are fully free since beginning. rest of the two are generally true, means first finish paying the deductible then get it for free. 4. for MEDICAL PLAN attributes in green, attributes/columns ending with fam/family [ four such attributes in MEDICAL PLAN] defining maximum per family, and the one ending with indv/individual [ four such attributes in MEDICAL PLAN] define maximum per family member. 5. Co-payment/copay [ three columns/attributes in MEDICAL PLAN]: A fixed amount (for example, $15) you pay for a covered health care service, usually when you receive the service. The amount can vary by the type of covered health care service. 6. Deductible max: The amount you owe for health care services your health insurance or plan covers before your health insurance or plan begins to pay. For example, if your deductible is $1000, your plan won’t pay anything until you’ve met your $1000 deductible for covered health care services subject to the deductible. The deductible may not apply to all services. [ one such amount in DRUG PLAN] [ eight such attributes in MEDICAL PLAN] 7. out-of-pocket limit: Example [ also see table named ACCUMULATOR to understand this] a)Jane hasn’t reached her $1,500 deductible yet. Her plan doesn’t pay any of the costs. Office visit costs: $125 Jane pays: $125 Her plan pays: $0 b)Jane reaches her $1,500 deductible, co-insurance begins. Jane has seen a doctor several times and paid $1,500 in total. Her plan pays some of the costs for her next visit. Office visit costs: $75 Jane pays: 20% of $75 = $15 Her plan pays: 80% of $75 = $60 c)Jane reaches her $5,000 out-of-pocket limit. Jane has seen the doctor often and paid $5,000 in total. Her plan pays the full cost of her covered health care services for the rest of the year. Office visit costs: $200 Jane pays: $0 Her plan pays: $200 [two such attributes in MEDICAL PLAN above, one for whole family, one for individual] |
|||
DRUG CATEGORY (DCY)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
drug category code
|
|
*
|
category name
|
||
Description/Assumptions
for DCY:
|
|||
e.g. generic , preffered, non-preffered
|
|||
DRUG (DRG)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
drug code
|
|
*
|
drug name
|
||
*
|
cost per unit
|
||
*
|
unit of measurement
|
||
*
|
purpose
|
||
*
|
side effect
|
||
fk
|
*
|
drug category code
|
|
Description/Assumptions
for DRG:
|
|||
generally there are
multiple purposes and side effects of same drug based on salts/content. I
don't go so deep here . Store everything in single column as text, one for
purpose, one for side effect. Under real life implementation, salt and
content of drug is also tracked, with purpose and side effects of each ingredients
with possible reaction with other salts/content.
|
|||
PRESCRIPTION (PSN)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
prescription number
|
|
*
|
date issued
|
||
fk1
|
*
|
drug code
|
|
*
|
dosage description
|
||
○
|
duration in days
|
||
○
|
followup date
|
||
fk2
|
*
|
provider number
|
|
fk3
|
*
|
memeber id
|
|
fk4
|
○
|
visit id
|
|
fk5
|
○
|
non refillable detail id
|
|
fk6
|
○
|
refillable detail id
|
|
Description/Assumptions
for PSN:
|
|||
1. PRESCRIPTION may be
issued during a VISIT. Or over the phone. A VISIT may issue zero or more
PRESCRIPTION. -non transferable - fk4
2. PRESCRIPTION must be issued for one MEMBER and a MEMBER may have sero or more PRESCRIPTION - non transferable - fk3 3. PRESCRIPTION must be issue by one PROVIDER of type doctor [ type check is programmatic] and a doctor may issued zero or more PRESCRIPTION - non transferable - fk2 4. PRESCRIPTION must have one DRUG and a DRUG may be present in zero or more PRESCRIPTION - non transferable - fk1 5. fk5 and fk6 are in arc- exclusive or, so optional here. 6. check constraint is required for arc exclusive or. Both can not be null, one and only one must be non-null of both in a row |
|||
NON REFILLABLE (NRE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
non refillable detail id
|
|
*
|
unit count given
|
||
fk
|
*
|
prescription number
|
|
Description/Assumptions
for NRE:
|
|||
an instance must map to
one valid PRESCRIPTION instance - non transferable
|
|||
REFILLABLE (RFE)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
refillable detail id
|
|
*
|
no of refills available
|
||
*
|
unit count per refill
|
||
fk
|
*
|
prescription number
|
|
Description/Assumptions
for RFE:
|
|||
an instance must map to
one valid PRESCRIPTION instance - non transferable
Programming is required to reduce no of refills available with each LABEL generation and it can not go below zero |
|||
COUNTRY (CTY)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
country id
|
|
*
|
name
|
||
Description/Assumptions
for CTY:
|
|||
e.g. Unite Sates
|
|||
ZIP (ZIP)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk, fk
|
*
|
country id
|
|
pk
|
*
|
zip id
|
|
*
|
zip code
|
||
Description/Assumptions
for ZIP:
|
|||
e.g. 30348
|
|||
CONTACT (CTT)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
ctt id
|
|
fk1
|
*
|
country id
|
|
fk1
|
*
|
zip id
|
|
*
|
communication name
|
||
*
|
address line 1
|
||
○
|
address line 2
|
||
*
|
phone
|
||
○
|
fax
|
||
*
|
email
|
||
fk2
|
○
|
ins comp id
|
|
fk3
|
○
|
provider number
|
|
fk4
|
○
|
memeber id
|
|
fk5
|
○
|
pme id
|
|
Description/Assumptions
for CTT:
|
|||
1. fk2, fk3, fk4 are in
arc, one of these must be non-null,
But not more than one. For this check constraint is used.
2. Hospitals must mention their preferred mode of communication, for other its optional - additional programming required. |
|||
PREFERRED MODE (PME)
|
|||
Key Type (pk, fk, uk)
|
Optionality ( "*", "○")
|
Column Name
|
|
pk
|
*
|
pme id
|
|
*
|
pme name
|
||
Description/Assumptions
for PME:
|
|||
This defines preferred mode of communication for the contact details saved in table CONTACT - which
in turn is used by provider/insurance/member
|
|||
