I'm using NHibernate 2.0 beta, but I have a sort-of "how on earth do I.." problem trying to map from my database design ... and I'm hoping someone with more experience can tell me if what I'm trying to do is even possible, or if I need to consider redesigning the database before I can use NHibernate.

As background, I have a basic stats application with "Models" which contain "Factors" which contain and "Levels" ... in the database, the Levels table has a foreign key to the Factors table, but they have a many:many relationship to the Models table, so there is a "ModelContent" table which shows which Levels are in each Model.

The problem is that at the object level, I need Models to contain Factors, but in the persistence layer, they're mapped many-to-many to the Levels instead, and I can't see a way to map that in NHibernate.

I've explained this with diagrams on my website ( http://huddledmasses.org/a-cry-for-help ... hibernate/ ) but essentially, Factors and Levels are reused by many Models, and any given Model may contain only a subset of the possible Levels which belong to a Factor that it contains. Rather than have two tables indicating which Factors, and then which Levels are in the Model (which NHibernate seems to require) the database has just one, for the Levels. Since the Levels belong to specific Factors, that relationship is implied (and can be retrieved with a simple join query).

Is it possible to do this in NHibernate without creating additional constructs in the database?

I mean, I'm using SQL Server 9, so if I have to I could probably create an updatable view of "ModelFactors" and even "ModelFactorLevels" and tinker with the "INSTEAD OF" triggers, but I'd rather not force that much of the OO layer logic into the server ... especially since it would create a dependency on SQL Server specifically.

--
Joel "Jaykul" Bennett

just stumbled over "ternary associations" in the documentation and that reminded me of your problem. Maybe that's a possible solution for you:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/collections.html#collections-ternary
[code]

I haven't used them yet, but for me it looks like a possible solution.