What is the best way to map a child objects that can have one of many possible parents? Is it best to use many-to-many mappings with a joining table between the child and each parent table? Will it work to provide a foreign key column for each parent table on the child table where only one foreign key is set at any one time and the others null? Is there any other approach anyone has used successfully?

If it's simple one child, one-of-many parents, then you want many-to-one (with unique="true", if NHibernate supports that, but I don't think that it does). The parent table gets a column referring to the child table.

If it's one child, any number of parents, and each parent may have any number of children, that's when you need many-to-many with a join table. Neither table refers to the other: instead you have a new ParentChild table with the parentId and the childId. That way you have have many rows with the same parentId (different childIds), and many rows with the same childId (different parentIds).

Each parent can have many children, but each child has only one out of many possible parents types at any one time. So we can't place a column on the parent referring to the child.

So for example:

We have a child class "Child" and two parent classes "ParentType1" and "ParentType2".

A "Child" object belongs to either a "ParentType1" instance or a "ParentType2" instance at any one time.

Each parent can have many "Child" objects at any given time.

So the only 2 solutions I can think of is a many-to-many join table between PARENTTYPE1 and CHILD and another between PARENTTYPE2 and CHILD, or two foreign key columns in the CHILD table pointing to PARENTTYPE1 and PARENTTYPE2 respectively, where only one foreign key column is set at any one time.

Is either solution ideal? Is there a better solution?

I think that the solution is to put a ParentId column in the Child table, but I'm not sure how the Type1/Type2 thing fits into your schema. Are the different ParentTypes stored in different tables? If they are, then you'll have to have columns in child for each parent type, as you suggest. Even if you go many-to-many, you'll need one link table for each ParentType table: A ParentType1ChildLink table (with ParentType1Id and ChildId), and the equivalent for ParentType2.

You could eliminate that, if you used the same table for all parents.

The different parent types are indeed stored in different tables. This is necessary as the two tables unfortunately require entirely different structures.

The different DB structures don't necessarily mean that the parent items shouldn't share a table. You could have a ParentAllTypes table, which is just an id column, then as many tables as you like join to that. Your java base class has nothing but the id, and a member for each "real" ParentType table. Then you use delegation to expose the correct information to the pojo parent. Your ParentType1, ParentType2 interfaces all extend a common ParentAllTypes interface, but the implementing classes don't use inheritance.

This isn't a particularly straightforward approach, and there are hibernate complexitiies involved in it, but if you prefer API elegance, this is the way to get it.. at the expense of more complex DB and mapping code.

True. Providing another table containing only IDs is certainly another alternative.

Thanks for the advice!