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?