NHibernate Mapping for User Roles and Privileges

The Scenario

I've been banging my head against the wall trying to figure out the correct mapping for 3 entities: User, Role, and Privilege. In my application, Users can have Privileges, which just give a user additional permissions. Users can also have Roles which are essentially privileges that require additional properties.

For instance, a user might have a role of "Application Administrator" in which case the ApplicationAdministratorRole.cs would need a property to contain the list of applications that a user can manage. A user could also have a privilege of "Event Administrator" in which case Privilege.cs would NOT contain any additional properties for events because in our application an event administrator can manage all events. I hope this example makes sense. If not, I can elaborate a little more.

Table Structure

[Table Name] TBL_USERS

[Columns] UserId (PK), FirstName, LastName, CompanyId, etc...

[Table Name] TBL_ROLEREF (just defines the roles within the system)

[Columns] RoleId (PK), RoleName

[Table Name] TBL_USERROLES (table to cross reference users to roles)

[Columns] UserRoleId (PK), UserId, RoleId, ActiveDate, DeactiveDate

[Table Name] TBL_APPLICATIONADMINISTRATORS

[Columns] ApplicationAdministratorId (PK), ApplicationId, UserId, RoleId, ActiveDate, DeactiveDate

[Table Name] TBL_PRIVILEGEREF

[Columns] PrivilegeId (PK), PrivilegeName

[Table Name] TBL_USERPRIVILEGES

[Columns] UserPrivilegeId (PK), UserId, PrivilegeId, ActiveDate, DeactiveDate

The table structure is pretty straight forward, all privileges and roles have an ActiveDate and DeactiveDate so that we can maintain a history of a users previous roles and privileges. One thing to note is that any role requires an additional table to store any additional information that goes along with this role, in this case, TBL_APPLICATIONADMINISTRATORS will tie a user's Application Administrator role to different applications from TBL_APPLICATIONREF. Again, please let me know if I need to reword this to make better sense.

Mapping Files

[User.hbm.xml]

User objects should have a collection of Privileges and Roles. These bags should probably be sets, but for the sake of this example I don't think it should matter.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core">
    <class name="Core.Entities.User, Core" table="TBL_USERS">
        <id name="UserId" column="USERID" type="Int32" unsaved-value="0">
            <generator class="sequence">
                <param name="sequence">SEQ_TBL_USERS</param>
            </generator>
        </id>
        <property name="Title" column="USERTITLE" type="string" length="50" not-null="false" />
        <property name="FirstName" column="USERFIRSTNAME" type="string" length="50" not-null="true" />
        <property name="LastName" column="USERLASTNAME" type="string" length="50" not-null="true" />
        <bag name="Privileges" generic="true" table="TBL_USERPRIVILEGES">
            <key column="USERID" />
            <many-to-many column="PRIVILEGEID" class="Core.Entities.Privilege, Core" />
        </bag>
        <bag name="Roles" generic="true" table="TBL_USERROLES" >
            <key column="USERID" />
            <many-to-many column="ROLEID" class="Core.Entities.Role, Core" />
        </bag>
    </class>
</hibernate-mapping>

[Privilege.hbm.xml]

Privilege objects should have a PrivilegeId, PrivilegeName, a collection of users associated with the privilege, and ActiveDate/DeactiveDate. I have commented out my failed attempts to map this.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core">
    <class name="Core.Entities.Privilege, Core" table="TBL_PRIVILEGEREF">
        <id name="PrivilegeId" column="PRIVILEGEID" type="Int32" unsaved-value="0">
            <generator class="sequence">
                <param name="sequence">SEQ_TBL_USERPRIVILEGES</param>
            </generator>
        </id>
        <property name="Name" column="PRIVILEGENAME" type="string" length="128" not-null="false" />
        <!-- 
        This does not work. NHibernate is complaining about the repeated Column "USERID"
        I have made several attempts to get this to work with no luck... where am I going wrong?
        <bag name="Users" generic="true" table="TBL_USERPRIVILEGES" inverse="true">
            <key column="USERID" />
            <many-to-many column="USERID" class="Core.Entities.User, Core" />
        </bag>
        -->
        <!-- 
        This also does not work. This was my attempt to join the ActiveDate and DeactiveDate into Privilege.cs 
        The join NHibernate creates with this setup is completely wrong... 
        <join table="TBL_USERPRIVILEGES">
            <key column="USERPRIVILEGEID" />
            <property name="ActiveDate" column="ACTIVEDATE" type="DateTime" not-null="false" />
            <property name="DeactiveDate" column="DEACTIVEDATE" type="DateTime" not-null="false" />
        </join>
        -->
    </class>
</hibernate-mapping>

[Role.hbm.xml]

I would like to have a Role base class that has properties for ActiveDate and DeactiveDate that each role (like ApplicationAdministratorRole) can inherit from so that every role is forced to have these properties. I suppose I could use an interface to enforce this as well, but this is my first shot at mapping something semi-complicated in NHibernate, so please give me some direction on this.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core">
    <class name="Core.Entities.Role, Core" table="TBL_ROLEREF">
        <id name="RoleId" column="ROLEID" type="Int32" unsaved-value="0">
            <generator class="sequence">
                <param name="sequence">SEQ_TBL_USERROLES</param>
            </generator>
        </id>
        <property name="Name" column="ROLENAME" type="string" length="128" not-null="false" />
        <bag name="Users" generic="true" table="TBL_USERROLES">
            <key column="ROLEID" />
            <many-to-many column="USERID" class="Core.Entities.User, Core" />
        </bag>
        <joined-subclass name="Core.Entities.ApplicationAdministratorRole, Core" table="TBL_APPLICATIONADMINISTRATORS" extends="Core.Entities.Role, Core">
            <key column="ROLEID" />
            <property name="ApplicationAdministratorId" column="APPLICATIONADMINISTRATORID" type="Int32" />
            <bag name="Applications" generic="true" table="TBL_APPLICATIONREF">
                <key column="APPLICATIONID" />
                <one-to-many class="Core.Entities.Application, Core" />
            </bag>
        </joined-subclass>
        <!-- Do I need to use <join> here to set the ActiveDate and DeactiveDate in the Role base class? -->
    </class>
</hibernate-mapping>

I've done a lot of reading on this stuff, but I'm obviously missing something. As you may have gathered, I am implementing a table-per-subclass strategy for Roles.

These mapping files work as they are, but they do not return the correct results. Any and all help is greatly appreciated.

Thanks guys,

Josh

You are trying to map the Users when using USERID twice, both as the key and as the reference. You probably mean:

I don't understand this, what is the relation between TBL_PRIVILEGEREF and TBL_USERPRIVILEGES, there doesn't seems to be any:

You join-subclass mapping seems fine