NHibernate - Best way to map to a secondary key

I am trying to map a new domain model to a fixed-schema legacy database and am stuck on how to do a certain type of mapping. Here's a distilation of my problem.

There are types of Workers. For example an HourlyWorker and a SalariedWorker.

The data for an HourlyWorker is stored in the WORKERS table and the HOURLY_WORKERS table with a standard foreign key relation between the two of WORKERS.PK_WORKERS = HOURLY_WORKERS.FK_WORKERS.

The "Id" of the HourlyWorker from NHibernate's point of view is mapped to WORKERS.PK_WORKERS, which is as it should be.

So far so good.

Now suppose there is an object that applies only to hourly workers.

class Timesheet
  HourlyWorker Owner { get; private set; }

In the legacy database this maps to the TIMESHEETS table in a fairly straightforward manner but with one catch: The foreign-key link to the owner is expressed not through a link to the WORKER table but via a link to the HOURLY_WORKER table. In other words, the foreign key FK_HOURLY_WORKER is a unique identifier but is not pointing at the value that is in NHibernate's view the primary key.

I cannot simply change the Id HourlyWorker to map to the HOURLY_WORKER table because 70% of the database referencing houry workers uses the primary key from WORKER as the foreign key Id.

What are my options for mapping this relationship?

Something that might make this easier: the Owner property is read only - the Timesheet cannot have it's owner changed. However, when an HourlyWorker is deleted, deletes should cascade to all of their开发者_如何学Go Timesheets.

Finally, please don't respond with recommendations that I change the database. I know. If you want my client's phone number so that you can try to convince them that their schema is not the most perfect thing in existence then I can supply it. I have been fighting this fight for months. Also please don't respond that this indicates that the problem is managerial and not just NHibernate mappings. I know that too. For right now, I just want advice on how to solve the technical problem.

I believe you are looking for the property-ref attribute of the many-to-one element: NHibernate docs

By the way, mapping to a legacy schema is hard - be prepared for some serious pain! On the plus side, you'll be an NHibernate expert by the time you come out on the other side.