Modeling a Row vault from an OLTP having composite primary key.

Data Vault 2.0
, , ,
1

I have three primary tables (Customers, Employees, and Shippers) and a child table, Orders, where Orders is the child of all three primary entities.

In the Orders table, OrderID is not unique, so it can’t be made the primary key alone. Instead, there is a composite Primary Key (CustomerID, EmployeeID, and OrderID). The ShipperID is just a foreign key because it can be null for some of the records.

I have multiple approaches to convert the above into a Raw vault. I would like to know which one is better.

Approach1

CustomersHub with Satellite
EmployeeHub with Satellite
ShipperHub with Satellite
OrdersHub (Business key = OrderID) with no satellite
OrdersLink with Satellite

In the above approach, OrdersHub will have distinct (and fewer) records with respect to OrderID. Therefore a link satellite is also created with the OrdersLink, which will have all the Order attributes.

Approach2

CustomersHub with Satellite
EmployeeHub with Satellite
ShipperHub with Satellite
OrdersHub (Business Key = CustomerID, EmployeeID, OrderID) with Satellite
OrdersLink with no Satellite

In this approach, OrdersHub and OrdersLink will have the same number of records. The only difference b/w them is the Shipper foreign key as part of OrdersLink.

Is there a third approach as well (e.g., Transactional link)? Which approach is the best in this scenario?

Thanks in advance.

2

Or have the orders id as a dependent child key in the link between those entities participating in the order (transaction) — no hub for the order if it cannot be uniquely identified and no hub that simply combines all the other keys you find — this is very bad practice.

3

To flesh out what @patrickcuba is saying using the style you presented:

Approach3

CustomersHub with Satellite
EmployeeHub with Satellite
ShipperHub with Satellite
OrdersLink (transactional/non-historized) with optional non-historized Satellite; (Link Key = CustomerId, EmployeeID, ShipperID (with null business key for NULL records), OrderID (as a dependent child key - no hub)

4

Probably not historised link, it’s not a NRT source and therefore could be treated as a link-sat (batch). This promotes idempotence and if you had a transaction/order timestamp could be treated as an intra-day/dependent-child key too