Designing Link tables
A Link table, or just Link, can be one of the following two things:
- A relationship between two or more Hub tables
- A (business) transaction
Before we go into these two cases, have a look at Figure 9.6. Figure 9.6 shows the column structure of a Link table:
Figure 9.6 – Link table
A Link consists of two or more foreign keys. These can be hash keys from Hubs or from other Links. Like Hub tables, Link tables have a LoadDate column and a Source column. The primary key of a Link table is the hash value calculated over all the foreign keys together with the load date. The foreign keys are, of course, hash values themselves because they reference the hash keys of the Hub tables. The columns shown in Figure 9.6 are mandatory and should always be created in the order shown. This makes automatically generating Link tables easier, the same argument as used for Hub tables.
You can choose to add some optional columns, such...
