Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: FK constraint -vs- custom constraint for role reference?

    I have a database design question involving a role implementation.

    There are different ways to enforce user-role associations in a business table.

    Consider this example:
    ------------------------
    John is an auditor for a property management company and has an "auditor" role in the property management system.

    The property management system has a "PropertyAudit" table that stores audit information for a property.

    One column in the "PropertyAudit" table is an "AuditorId" column. Referential integrity should ensure that "AuditorId" links to a user ID for a valid auditor.

    In this scenario no more information needs to be stored about an auditor user type than is stored for any other user type.

    There are at least 2 ways to manage this. When a user role association is made for an auditor a trigger can insert a row for the user into an Auditor table to associate the user with an AuditorId. Then the PropertyAudit table can reference Auditor.AuditorId as a FK.

    Another approach would be to create a constraint on the PropertyAudit table something like this:

    select userid from userrole
    where userid = AuditorId and
    roleid = 3 -- Auditor role ID
    return @@rowcount = 1

    From a design perspective I'm leaning towards a constraint since only user ID would be stored in a specialized Auditor table which seems like a waste.

    Is implementing a constraint for this scenario a reasonable approach? Do you see any issues with this approach or do you have a different preferred approach for handling this type of scenario?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a separate auditor table is not necessary

    let the propertyaudit table have a FK to the user table, and in the user table, identify the user as an auditor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    6
    Quote Originally Posted by r937 View Post
    a separate auditor table is not necessary

    let the propertyaudit table have a FK to the user table, and in the user table, identify the user as an auditor
    Ok - so it sounds like you're suggesting something like an "IsAuditor" column in the user table.

    If I go that way it looks like the PropertyAudit table will still need a check constraint to ensure that the user reference has IsAuditor = 1.

    Otherwise any user could be added as an auditor to the PropertyAudit table even a user with IsAuditor = 0 (non-auditor user).

    Is this correct?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are many ways to skin a cat

    but yeah, that's how i'd do it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Whatever road to Rome you take, make sure adding history is easy or already implemented.

    Someone who is an auditor today may not be one tomorrow, but in time he may become one again.
    PropertyAudit table will still need a check constraint to ensure that the user reference has IsAuditor = 1.
    How will are you planning to enforce this constraint? Will it allow future changes in the value of IsAuditor?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •