Foreign Key - One Child Table to Multiple Parent Tables

Question

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.

Answer 1

I'm not sure I've seen any DBMS support multiple parents for foreign keys like this, it is probably assumed that something needed this is not in good form (the linking field is explicitly overloaded as it can refer to several distinct types of thing, which means it dos not fit an accepted "normal form").

You could instead have a general "AccessableThings" table that contained just the IDs, and then have the UserItems/SystemItems/OtherItems/etc refer to that via FK (to ensure everything in those tables also appears in the IDs table) as well as AccessControl doing so. If you have other generic fields that apply to all types ("display name" for instance) then you might include them in the AccessibleThings table to so you can avoid joining in all the other tables when all you need are generic details.

CompanyItems            AccessibleThings          AccessControl
============            ================          =============
CompanyID PK FK ------> ThingID PK      <--.      AccessRuleID PK
<companyfields>   |     <genericfields>    |----- ThingID FK
                  |                               <AccessRuleFields>
SystemFiles       |
===========       |
FileID PK FK -----|
<filefields>      |
                  |
SomethingElse     |
=============     |
SomethingID ------|

This will enforce things being correct as entered, though can not delete AccessControl on the delete of an Item, just on delete of the AccessibleThings row (which can also cascade to the Items tables of course) so this table becomes the central object in that regard. Obviously your data access layer (or business logic, but the data layer is more likely the correct place) will need to know about the new structure at each relevant point.

Alternative:

You could also enforce your more complex data integrity relationship entirely via triggers, though you need to be very careful with both complexity and efficiency here otherwise you could create a veritable zoo of nightmares for later.