1

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.

| improve this question | |
  • 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? – Aaron Bertrand♦ Mar 25 '13 at 16:21
  • Which tables has the actual user records? You may need to explain your schema a little more here. – JNK Mar 25 '13 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 '13 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 '13 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. – Aaron Bertrand♦ Mar 25 '13 at 17:02
3

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.

| improve this answer | |
  • Agree; our schema needs to be updated if we want to go down the FK route. Thanks to all. – Joe Mar 25 '13 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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