Maybe I'm thinking of this problem in the wrong way, but we maintain an AccessControl table that is a child to several parent tables (e.g. CompanyItems, SystemFiles, etc.). I'm trying to make sure we don't create orphaned AccessControl recs, but I'm not sure how to properly set up this relationship. Any tips?
AccessControl
ItemId
AccessId (for user access)
ItemType
OwnerCd (1 for owner, 0 for read-only access)
CompanyItems
CompanyId
ItemId
ItemType
ItemName
SystemFiles
FileId
FileName
FileType
Sample Data:
AccessControl: ItemId=1, AccessId=1 (UserId), ItemType=0(for a CompanyItems rec), OwnerCd=1 (owner) AccessControl: ItemId=1, AccessId=2 (different UserId), ItemType=0, OwnerCd=0 (read-only access) AccessControl: ItemId=10, AccessId=1, ItemType=1 (for SystemFiles entry), OwnerCd=0 CompanyItems: CompanyId={whatever company}, ItemId=0, ItemType=0, ItemName='Test Item' SystemFiles: FileId=10, FileId='Test File', FileType='pdf', etc.
If my CompanyItems is deleted, the two corresponding AccessControl recs should also be deleted. If my SystemFiles rec is deleted, then its one corresponding AccessControl rec should be deleted.