Data Vault模型中交易数据设计咨询：交易数据是否为链接表？

Hey there! Let's break down how to handle your transaction data in Data Vault, based on the hubs you've already built.

First: Is your transaction data a Link Table? #

Yes, absolutely—your transaction data is exactly what a Link Table is designed for. Links exist to connect multiple Hub tables (in your case, Service, Status, and Time) and represent the business relationship between those entities. The catch is: links only store the hash keys of the connected hubs plus standard Data Vault metadata (load time, record source). Any descriptive attributes like service_duration_minutes belong in a Satellite Table, not the Link itself.

Step-by-Step Design Approach #

1. Build the Transaction Link Table

This table will tie together your three hubs. Here's a typical structure:

• transaction_link_hashkey: Primary key for the link, generated by hashing the concatenated hash keys of Service Hub, Status Hub, and Time Hub.
• service_hub_hashkey: Foreign key linking to your existing Service Hub.
• status_hub_hashkey: Foreign key linking to your existing Status Hub.
• time_hub_hashkey: Foreign key linking to your existing Time Hub.
• load_datetime: Timestamp when this record was loaded into the vault.
• record_source: Where the data came from (e.g., "CRM_API", "billing_system_table").

Quick note on your Time Hub: If your current Time Hub isn't at the minute granularity (say it's daily/hourly), you have two options:

• Extend the Time Hub: Add a minute-level business key (like a string in yyyyMMddHHmm format) and generate a corresponding hash key. This is the strict Data Vault-compliant approach, as it keeps all time-related entities in the hub.
• Store precise time in the Satellite: If extending the Time Hub isn't feasible right now, you can add a transaction_timestamp field to the satellite table. This is a pragmatic workaround, though it deviates slightly from pure Data Vault norms.

2. Create a Transaction Satellite Table for Descriptive Attributes

This is where you'll store the service_duration_minutes and any other transaction-specific details. Structure example:

• transaction_link_hashkey: Foreign key linking back to the Transaction Link Table.
• service_duration_minutes: The numeric value of your service length in minutes.
• load_datetime: Timestamp when this attribute set was loaded.
• record_source: Source of the data.
• is_current: Optional boolean flag to mark the latest active record (useful if attributes change over time).

3. Handle Status Changes (If Applicable)

If a transaction's status can update (e.g., from "in_progress" to "completed"), you'll want to track that history. You can either:

• Add status change history directly to the Transaction Satellite (each status update gets a new row with a new load_datetime).
• Or create a separate Multi-Link Satellite that specifically tracks changes to the Service-Status-Time relationship. The first option is simpler for most cases.

Example SQL Schema (Simplified) #

-- Transaction Link Table
CREATE TABLE transaction_link (
    transaction_link_hashkey VARCHAR(64) PRIMARY KEY,
    service_hub_hashkey VARCHAR(64) NOT NULL,
    status_hub_hashkey VARCHAR(64) NOT NULL,
    time_hub_hashkey VARCHAR(64) NOT NULL,
    load_datetime TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL,
    FOREIGN KEY (service_hub_hashkey) REFERENCES service_hub(hashkey),
    FOREIGN KEY (status_hub_hashkey) REFERENCES status_hub(hashkey),
    FOREIGN KEY (time_hub_hashkey) REFERENCES time_hub(hashkey)
);

-- Transaction Satellite Table
CREATE TABLE transaction_satellite (
    transaction_link_hashkey VARCHAR(64) NOT NULL,
    service_duration_minutes INT NOT NULL,
    transaction_timestamp TIMESTAMP, -- Optional: if you can't extend Time Hub to minute level
    load_datetime TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL,
    is_current BOOLEAN DEFAULT TRUE,
    PRIMARY KEY (transaction_link_hashkey, load_datetime),
    FOREIGN KEY (transaction_link_hashkey) REFERENCES transaction_link(transaction_link_hashkey)
);

Quick Best Practices #

• Always generate hub hash keys from business natural keys (e.g., service_id for Service Hub), not auto-incrementing IDs. This ensures consistency across systems.
• Stick to the Link/Satellite separation as much as possible—it keeps your vault flexible and scalable for future changes.

内容的提问来源于stack exchange，提问作者Befo