Databases Reference
In-Depth Information
In this section, we move on from completeness to verisimili-
tude. Having used a simplified set of health insurance data
throughout this topic, we continue by developing a query about
claims issued against policies held by clients.
Claims data has not been used, so far, and so we begin with
the SQL definition of a simplified Adjudicated Claims table.
CREATE TABLE dbo.Adjud_Claim (
claim_rowid
int
identity,
policy_oid
int
null,
claim_amt
money
null,
service_dt
datetime
default getdate()
null,
adjud_dt
datetime
null,
row_crt_dt
datetime
default getdate()
not null)
This Adjudicated Claims table is not an asserted version table.
It is an event table, not a persistent object table, and each of its
rows represent an event on which an insurance claim was
adjudicated. As an event, each claims adjudication has no per-
sistence. It happens, and then it's over.
In the Adjudicated Claims table, policy_oid is not a foreign
key, because there is no table for which it is the primary key.
The Policy table is an asserted version table, and its primary
key is the combination of policy_oid with assertion begin date
and effective begin date. Nor is policy_oid a temporal foreign
key, because Asserted Versioning does not recognize and manage
referential relationships between non-temporal tables and
asserted version tables. For example, the AVF may temporally
delete a policy after several claims have been adjudicated that
reference that policy, and it may temporally delete those policies
effective at some point in time prior to the adjudication of those
claims. It is the responsibility of the application which manages
the Adjudicated Claims table to make sure it is not adjudicating
claims against terminated policies.
Returning to our query, for each adjudicated claim, it will
show the client number and name, the policy number, type
and copay amount, and the claim service date, amount, and
adjudication date.
For the policy associated with the claim, this query picks out
the version of the policy that was in effect on the service date.
Clearly, we are not interested in any other version of the policy. In
particular, we are not interested in the version of the policy that
is in effect when the query is run, or in the version of the policy
that was in effect on the adjudication date. Those versions may
in fact be correct, but we can't count on it. We want to see the ver-
sion that was in effect at the time themedical service was rendered.
