Composite business keys or dependent child keys

Data Vault 2.0
,
1

Hi, I have a scenario where I am trying to model claims data. A claim has a claim id as it’s unique identifier so we have a claim hub, the claim can have multiple versions if it needs to be modified after it has been finalised. The claim version number always starts as 0 and will increment each time the claim is adjusted. Each version of a claim has an associated claim invoice (years ago we used to group invoices under a claim but these days any invoice generates a new claim id so by default the claim invoice number is just a 1.). For each claim invoice the claim can have multiple lines or items so there is a claim invoice line number that starts as 1 and increments for each item on the invoice.

The latest version of our conceptual model has Claim, Claim Invoice and Claim Item as separate concepts so the business key for Claim Invoice would be Claim Id + Claim Version + Claim Invoice Number and the business key for Claim Item would be Claim Id + Claim Version + Claim Invoice Number + Claim Invoice Line Number e.g. 123456789||0||1||1.

Is this valid for a composite business key? Initially we modelled the concepts as Links (linking a claim to something else e.g. the healthcare provider that billed us) with the version, invoice number and invoice line number as dependent child keys as those components have no meaning on their own.

2

Hi Martin @mlord1979,
Both approaches are potentially auditable, so open for refactoring. Following the training’s example on order, I’d tend to a dependent child link, indeed.
Your idea with a Hub defining a combined key for a Claim Invoice Line is more following the community saying “No Sats on Links”, which is a proper approach, but not exactly Data Vault 2.
Q: Why did you change your initial concept?

1 Like
3

A little confused. You say “any invoice generates a new claim id”, did you mean an invoice generates a new version of a claim ID or does it really generate a new claim ID (but it is associated with another original claim ID)? If the former, then an invoice links to a claim version and not to a claim. So a claim version (key: claim id + version #) needs to be a core concept, but you would still have a core concept of claim. But if the latter, then an invoice links to a second claim, but that second claim also links to the original claim.

4

Yes, so back when we used to receive paper invoices we would group related invoices under one claim id/version and then increment the invoice number. Now that we have digitalised this, when a provider submits an invoice it will generate it’s own claim id (does make it harder to link invoices from hospital & consultant for the same encounter though).

5

Thanks, we haven’t as yet changed anything but it was being discussed so I was pre-empting it being a reality.

6

How about:

-- ============================================================================
-- HUBS
-- ============================================================================

CREATE TABLE Hub_Claim (
    claim_hk        BINARY(16) PRIMARY KEY,
    claim_id        VARCHAR(50) NOT NULL,
    load_date       TIMESTAMP NOT NULL,
    record_source   VARCHAR(50) NOT NULL
);

CREATE TABLE Hub_Claim_Invoice (
    claim_invoice_hk    BINARY(16) PRIMARY KEY,
    claim_id            VARCHAR(50) NOT NULL,      -- Parent BK part 1
    claim_version       INT NOT NULL,              -- Parent BK part 2
    invoice_id          VARCHAR(50) NOT NULL,      -- Child BK
    load_date           TIMESTAMP NOT NULL,
    record_source       VARCHAR(50) NOT NULL
    -- Hash computed as: HASH(claim_id || claim_version || invoice_id)
);

-- ============================================================================
-- LINK
-- ============================================================================

CREATE TABLE Link_Claim_Invoice (
    link_hk             BINARY(16) PRIMARY KEY,
    claim_hk            BINARY(16) NOT NULL,
    claim_invoice_hk    BINARY(16) NOT NULL,
    load_date           TIMESTAMP NOT NULL,
    record_source       VARCHAR(50) NOT NULL
);

-- ============================================================================
-- SATELLITES
-- ============================================================================

CREATE TABLE Sat_Claim (
    claim_hk            BINARY(16) NOT NULL,
    load_date           TIMESTAMP NOT NULL,
    claim_status        VARCHAR(20),
    claim_amount        DECIMAL(10,2),
    claim_description   VARCHAR(500),
    load_end_date       TIMESTAMP,
    record_source       VARCHAR(50) NOT NULL,
    hash_diff           BINARY(16),
    PRIMARY KEY (claim_hk, load_date)
);

CREATE TABLE Sat_Claim_Invoice_Header (
    claim_invoice_hk    BINARY(16) NOT NULL,
    load_date           TIMESTAMP NOT NULL,
    invoice_date        DATE,
    invoice_total       DECIMAL(10,2),
    invoice_status      VARCHAR(20),
    invoice_due_date    DATE,
    payment_terms       VARCHAR(50),
    load_end_date       TIMESTAMP,
    record_source       VARCHAR(50) NOT NULL,
    hash_diff           BINARY(16),
    PRIMARY KEY (claim_invoice_hk, load_date)
);

CREATE TABLE Sat_Claim_Invoice_Line (
    claim_invoice_hk    BINARY(16) NOT NULL,
    line_number         INT NOT NULL,              -- Dependent key
    load_date           TIMESTAMP NOT NULL,
    line_amount         DECIMAL(10,2),
    line_description    VARCHAR(200),
    line_quantity       DECIMAL(10,2),
    line_unit_price     DECIMAL(10,2),
    line_tax            DECIMAL(10,2),
    line_item_code      VARCHAR(50),
    load_end_date       TIMESTAMP,
    record_source       VARCHAR(50) NOT NULL,
    hash_diff           BINARY(16),
    PRIMARY KEY (claim_invoice_hk, line_number, load_date)
);
1 Like