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?

  AccessId (for user access) 
  OwnerCd (1 for owner, 0 for read-only access)



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.

  • Can you show the structure of the AccessControl table, maybe a couple of sample rows, and which columns point to which tables? Also are you guarding against the case where an AccessControl points to a UserItems row that gets deleted (and what is the frequency of deletes on either side) but still points to a SystemFiles row that exists? Mar 25, 2013 at 16:21
  • Which tables has the actual user records? You may need to explain your schema a little more here.
    – JNK
    Mar 25, 2013 at 16:55
  • @AaronBertrand Sure, original question edited. There should never be a CompanyItems entry w/o a corresponding AccessControl entry, and the same for SystemFiles/AccessControl. Each AccessControl.ItemId rec would only point to either CompanyItems.ItemId or SystemFiles.FileId. Basically, I want to ensure that there's never an AccessControl rec that doesn't point to either CompanyItems or SystemFiles.
    – Joe
    Mar 25, 2013 at 16:58
  • @JNK CompanyItems and SystemFiles hold the actual recs, and AccessControl determines which user owns the items and which have read-only access. E.g. CompanyItems.ItemId = 1 could have AccessControl.ItemId = 1, a userId, ItemType and 1 for the owner, then another rec w/ OwnerCd = 0 for a read-only access for a different user. If a user or an item is deleted, then referenced AccessControl items should also be deleted.
    – Joe
    Mar 25, 2013 at 17:02
  • 1
    @Joe please show sample data in the question. Trying to parse a comment to understand your data model is going to be frustrating. Mar 25, 2013 at 17:02

1 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.


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.

  • Agree; our schema needs to be updated if we want to go down the FK route. Thanks to all.
    – Joe
    Mar 25, 2013 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.