- Org ( Id, OrgCode, Name) - Org Master table
- Item ( Id, ItemCode, Name) - Item Master table
- OrgItem ( ItemId, OrgId, ItemCode) - Some Org specific ItemCodes
Only some Items have an Org-specific ItemCode, so by default I fetch the ItemCode from Item, but if for a particular Item-Org combination if its record exists in the OrgItem table then I have to fetch the ItemCode from the OrgItem table.
Creating a View_OrgItem_Item might be a solution but it would mean a view which consists of n-fold the size of the original Item table (where n = number of Orgs).
Another option is to create a GetItemCode(ItemId, OrgId) which does the lookup and return either Item.ItemCode or ORgITem.ItemCode
So, my question is - should I go for a 'View_OrgItem_Item' or simply create a function GetItemCode(ItemId, OrgId)?
Which one is best in terms of performance? Are there any other factors to consider?
*Note: It is NOT necessary that ALL the Orgs have records in OrgItem