If it's a true hierarchy then it's your design decision how you want to organize the data. In one table it could be: {Item_RK, ItemID, groupID, itemtypeID,....} When loading using SCD2 then Item_Id would be the business key, all the other columns would be under change tracking. With 3 tables: - ItemType_Ref {ItemType_RK, ItemTypeID,....} - Group_Ref {Group_RK, GroupID,...} - Item {Item_RK, ItemID, Group_RK, ItemType_RK....} If you go for a 3 table approach then you would first load ItemType_Ref and Group_Ref and then look up the RK's via the ID's before loading table Item. If you're having full loads then I would go for a 1 table approach, if it's delta then I would eventually go for a 3 table approach. The reason for 3 tables in case of delta loads: If something like the Group_Description changes then in a 3 table approach this would simply cause a new current record and all is set, in a one table approach you would have to update all records in the table with the new description (with a full load this would happen "automatically"). As a database purist one would say that you always need to generate keys. From a more pragmatic perspective it might well be o.k. to use the ID's as provided from source and not generate RK's at all. In such a case you wouldn't need to look up the RK's but you would simply use the ID's in table "Item".
... View more