Many-to-one with non unique columns

Tables look as follows:

User
+ID
+PERSON_ID
+PRIMARY_USER

DeputyRole
+PERSON_ID

Disclaimer: Yes I know the db schema is broken, but we can not do anything about it.

Why didn’t we use standard propery-ref and column mapping but formula instead?

1. USER.PERSON_ID is not unique in USER table.
2. USER.PERSON_ID AND USER.PRIMARY_USER = ‘Y’ is unique in USER table.
3. We are using formula to get single record from USER (ID) with specified PERSON_ID and PRIMARY_USER = ‘Y’

In DeputyRole.hbm.xml we have:

<many-to-one name="User"
        class="DomainModel.Users.Entities.User"
        lazy="false"
        formula = "(SELECT u.ID FROM USER u WHERE
                                u.PRIMARYUSERID = 'Y'
                                AND u.PERSON_ID = PERSON_ID)"
/>

Can this be done in better way?

Tags: NHibernate