I cannot figure this one out.

I have an Employees table in SQL Server

EmployeeId
LastName
FirstName
SubsidiaryId (maps to Subsidiary table and class)
IsAdministrativeManager
IsProjectManager

Where IsAdministrativeManager and IsProjectManager are bit fields.
There are four possbilities. An employee can be a plain employee. Can be an AdministrativeManager. Can be a Project Manager. Or can be both an administrative manager and a project manager

I want to have an Employee class and two subclasses: Administrative Manager class and Project Manager class.

Adminstrative Manager has AssignedEmployees that is supported by a administrators_employees table as a many to many association table. A employee can have multiple administrativce managers.

AdministratorId
EmployeeId

Both AdministratorId and EmployeeId are foriegn key relationships back to the EmployeeId of the Employees table.

Project Manger Class is has AssignedProjects, supported by a projects_employees many to many assoication table.

ProjectId
EmployeeId

ProjectId is a foriegn key relationship back to a projects table which is maps to a projects class.

I've explored the docs on inheritance mapping, blogs, this forum, my NHibernate in Action book and cannot figure out how to map this structure.

Table per class hierarchy doesn't seem to work since I have two discrimators. And that the employees table have no fields for the sub-classes.

Table per subclass doesn't seem to work due to the many-to-many relationship in the administrative_employees mapping

Any enlightenment on an approach to mapping these classes and tables will be appreciated?



Thanks,

ERDoll