The Bridge Table with Two Passports

Part 1 ended on a single un-enforced integer: EmployeeSales.ExternalSalesId, commented “references Sales.Id from main database.” That comment is the load-bearing fiction of V5 — the pretence that the sales world and the org world live in different databases, when in fact they share one PostgreSQL instance. This post is about the table that spans the pretended gap, CrossDatabaseFact, why it carries two reference keys at once, and the eight windowed measures that value a single sale at every level of the org chart.

Why pretend at all

It is worth being honest about the conceit up front. There is no second database. Sales and EmployeeSales are tables in the same schema; a foreign key from one to the other would work fine. I deliberately did not create one, and modelled the link as a bare integer with a comment instead, because the design problem I wanted to explore is the one you hit when the sales system and the org system genuinely are separate — separate services, separate databases, separate teams — and you have to stitch their facts together without a foreign key to lean on.

That is an extremely common real situation. The CRM owns sales; the HR system owns the org chart; analytics has to join them on an agreed-upon id that no database constraint enforces. V5 rehearses that situation in miniature. The un-enforced ExternalSalesId is the point, not an oversight — it is what a cross-database join actually looks like when neither side can reference the other.

Two passports on one row

The table that makes the cross-database join durable is CrossDatabaseFact, and its defining feature is that it carries two reference keys:

CREATE TABLE CrossDatabaseFact (
    CrossDatabaseFactId SERIAL PRIMARY KEY,
    SalesReferenceId INT,        -- points into the sales "database"
    OrgSalesReferenceId INT,     -- points into the org "database" (EmployeeSales)
    HierarchyLevel VARCHAR(50),
    MeasureTypeId SMALLINT,
    FactValue DECIMAL(18,4),
    DataQualityScore DECIMAL(3,2),
    ValidFrom TIMESTAMP,
    ValidTo TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    IsCurrent BOOLEAN DEFAULT TRUE
);

SalesReferenceId is the passport into the sales world; OrgSalesReferenceId is the passport into the org world. A single row here is a materialised join record — a physical statement that this sale corresponds to that employee-sale, at this hierarchy level, producing this measure value. Where the core sales fact needed one reference key because it described one sale, a cross-database fact needs two because it describes a correspondence between two records that live in (notionally) different places.

Carrying both keys is what lets the cross-database join be stored rather than recomputed. Instead of re-deriving “which employee-sale matches this sale” on every query — across the un-enforced boundary, with all the fragility that implies — V5 computes the correspondence once and pins it as a fact, complete with its own SCD2 window and quality score. The bridge is a table, not a join.

Eight measures, valued at every altitude

The reason to bridge the two worlds is to value a sale in organizational terms. A sale is not just worth 1200.00; it is worth 1200.00 to an employee, whose office it rolls up into, whose region that rolls up into, whose organisation that rolls up into. V5 adds measures 11 through 18 to the sales MeasureTypeLookup, each a windowed aggregate partitioned by an org level:

-- MeasureTypeId 11: OrgLevelTotalSale
SUM(TotalSale) OVER (PARTITION BY OrgId)
-- MeasureTypeId 13: RegionLevelTotalSale
SUM(TotalSale) OVER (PARTITION BY RegionId)
-- MeasureTypeId 15: OfficeLevelTotalSale
SUM(TotalSale) OVER (PARTITION BY OfficeId)
-- ...down to MeasureTypeId 18: EmployeeLevelNetRevenue

Four levels of the hierarchy, two measures each (a total and a net revenue), eight measures in all. The same underlying TotalSale appears at four different resolutions simultaneously — the sale's contribution to its employee's numbers, its office's, its region's, its organisation's. That is the “value at every altitude” the whole version is built to deliver, and it is only possible because the bridge connects a sale to a place in the org chart in the first place.

Querying across the boundary

With the bridge stored, a cross-database question is an ordinary query. Every sale, rolled up at every org level it belongs to:

SELECT cdf.SalesReferenceId, cdf.OrgSalesReferenceId, cdf.HierarchyLevel,
       mtl.MeasureName, cdf.FactValue, cdf.DataQualityScore
FROM CrossDatabaseFact cdf
JOIN MeasureTypeLookup mtl ON cdf.MeasureTypeId = mtl.MeasureTypeId
WHERE cdf.IsCurrent = TRUE
ORDER BY cdf.SalesReferenceId, cdf.MeasureTypeId;

Both passports are right there in the output, so a row tells you the whole story: this sale, this employee-sale, this level, this measure, this value, this much trust. The quality score carried through from V4 matters especially here — a fact assembled across an un-enforced boundary is exactly the kind of fact whose trustworthiness you want stamped on it, because there is no foreign key vouching for the join.

The honest fragility

I will name the risk the design accepts. An un-enforced ExternalSalesId means nothing stops it pointing at a sale that does not exist, or going stale when a sale is deleted. In a real two-database deployment that is a genuine hazard, and you would manage it with reconciliation jobs and quality scoring — which is precisely why V5 leans so hard on the governance features V4 introduced. The DataQualityScore on CrossDatabaseFact is not decoration; it is the mechanism by which a bridge built on an unenforceable link stays honest about how much you should believe it.

That is the trade of cross-database analytics in one sentence: you give up the database's referential guarantees, and you buy back confidence with explicit provenance and quality tracking. V5 makes the trade visible by storing both keys and scoring every bridged fact.

The bridge connects a sale to a level. The next post is about walking those levels — rollups at every altitude, where MultiLevelRollup climbs from employee to organisation in a single view.