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 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.