Forum Discussion
How not to relate two data tables
You only have one fact-table here: Table 2 with the details. Table 1 is a lookup-table (for the transaction details, with unique transaction key). So not only no reason at all not to link them together, but also no alternative :-)
Regard it as a formerly long and wide fact-table where someone was wise enough to split it up already into an optimized form (this process is called "Normalization")
I also don't understand what benefits Greg_Deckler intermediate table (1) should bring?
"....On the other hand it would be so simple to just relate #1 and two #2 to each other, since #1 contains already unique values for [transactionKey]. But then wouldn't I just treat #2 as the fact table and let #1 the lookup table? Maybe technically this makes sense...?..."
Exactly!
If your transaction table has been split up into 2 tables in your source system, that doesn't make it 2 different transaction tables that shouldn't been joined together. The 2 different transaction tables the authors had in mind have nothing in common with your example here.
Your table structure can be compared to invoices that have been split up into header (1) and lines (n). Just the fact that in finance-& ERP-systems they are normally stored in different tables (to be joined by invoice ID) doesn't make them different transaction tables. Just the opposite: It's one transaction table that has been split up into 2 due to technical reasons.