Steve I'm not trying to criticize either you or Sean. I get your points, and I get that my explanation is not clear. But I'm just not sure how to clarify it any better. I've tried re-stating the problem in multiple different ways, without success.
Maybe a picture?
My goal is to figure out the best structure for that box TBD in the middle, and then to find the best way of writing queries to access the data contained within.
The design I concluded was best used is the following:
And a query to access the data, to answer the following question:
"For each Group, give me all of the Transaction and File records that People can access, and for each, give me the Name property"
Based on that structure, was:
SELECT #GroupPersonItem.*, ItemName
JOIN #ItemType ON #ItemType.ID = #GroupPersonItem.ItemTypeID
SELECT #Transaction.[Name] AS [ItemName]
WHERE #Transaction.ID = ItemID AND #ItemType.[Name] = 'Transaction'
SELECT #File.[Name] AS [ItemName]
WHERE #File.ID = ItemID AND #ItemType.[Name] = 'File'