1

Can a reference table be collegiated to an HUB ?

In all books the reference tables are collegiated only to the Satellite....but why it's not collegiated to a HUB table?

Thanks a lot

jeppoo1
  • 650
  • 1
  • 10
  • 23
Francesco Bocchino
  • 11
  • 2

1 Answers1

1

If your reference data is categorising a business key already stored within a hub then yes it would make sense to store it as a satellite to that hub.

Reference tables however are for situations where there are no hubs because you need more information on a piece of data that exists within a satellite but that item doesn't justify being a business key in itself. Remember there's a strict rule of no satellite to satellite joins in Data Vault and so if you find yourself in this situation normally then you need not only a new satellite but a new link table to connect the data your reference code.

A common example would be dates. It's a regular need to have some sort of reference table for dates, effectively the equivalent of a date dimension that allows you to quickly categorise dates into years, quarters, financial periods and so on. Rather than having to create a link table for every satellite that holds a date and shift date keys in to it you can instead use a reference table for direct joins.

A reference table might also lean towards storage of meta data that doesn't fit into the normal data vault format at all, to give an example if you have a source system in your hubs you may want a reference table giving a more descriptive name to those source systems.

BarneyL
  • 1,332
  • 8
  • 15
  • Thanks, but in my case i have a code in one hub and the description about that code in a reference table, it is possibile to collegate the reference table to hub table ? – Francesco Bocchino Dec 04 '19 at 05:50
  • Can you clarify what you mean by collegate? Sample data in the initial question might help understand your situation better. – BarneyL Dec 04 '19 at 08:38
  • For example : create a foreign key from a code in a HUB to the same code in a REFERENCE (where exist the description about that code) – Francesco Bocchino Dec 04 '19 at 09:00
  • Why would you do that? Just hash the business key in the reference table and you effectively have a satellite linking to the hub key. – BarneyL Dec 04 '19 at 17:05
  • Ok, so i can insert one term of business key in the reference table like : HB (key_HK, first_cd, second_cd ...) and RF (first_cd, first_ds)......it's right? – Francesco Bocchino Dec 05 '19 at 05:59
  • I think you need to share sample data, I'm struggling to understand your need from text names of columns you've not described. Reference tables join to satellites not hubs, you join to hubs through a link or a satellite with the primary key of the hub. Never on the business key columns in the hub and never on part of the business key columns – BarneyL Dec 05 '19 at 08:11
  • I try to explain with this example: HB (hash_key_pk, code_category, code_type ...) Reference/lookup (code_category, description_category)....It is possibile? – Francesco Bocchino Dec 05 '19 at 21:52
  • It's possible but you're not building a data vault. The specification states you can't have a foreign key relationship on part of a hub's business key. If part of the business key has an independent meaning then it's a business key in its own right and should therefore have its own hub. Alternatively you could de-normalise this reference data and place it in a satellite that joins to the hub (probably adding to an existing satellite that gives descriptions at the granular level). – BarneyL Dec 06 '19 at 12:45