I am having problems creating a many-to-one relationship between two tables in a scenario with a discriminator column determining which foreign table to reference.

Consider the following schema:



So, the UserOrGroupID references either the USER or GROUP table depending on the status of the AssigneeType flag.

In the Permission java object I have a field named 'assignee' that I want to point to either a group or user. It is of type Assignee which is just an interface implemented by both User and Group objects.

I do not know how to create the hbm file for the Permission object specifically the assignee field. I really want something like the following:

If UserOrGroup = 'U' use this xml block:

<many-to-one name="assignee" class"eg.user"
column="UserOrGroupID" />

else if UserOrGroup = 'G' use this xml block:

<many-to-one name="assignee" class"eg.group"
column="UserOrGroupID" />


Any help would be greatly appreciated.

Thanks,
Thad

Hibernate: v2.1

I don't think you will be able to use the discriminator this way.

This table layout seems wierd to me. Maybe you could just have both the UserID and the GroupID in the Permissions and map both objects, but that would still be pretty awkward. Sorry I'm not much help for you. If you are not stuck to the table layout (legacy), you might want to reconsider this design.

Thanks for the response. I considered doing exactly what you proposed regarding adding both the UserID and the GroupID, but not only is it awkward as you mentioned, but not scalable at all. Imagine if the discriminator could be 6 or 7 values instead of 2.

Unfortunately, I am locked into this schema although I cant really think of a better one. Merging the USER and GROUP tables seems equally clunky to me.

Have you considered using the <join-subclass>? Maybe something like..



Then is the Permission object you would map to an Assignee. So you would always get back an AssigneeImpl, but if you did an instance of on it, you would get a User or a Group.

This would require an additional table (ASSIGNEE), which may be out of the question for you. Anyway, just an idea. Good Luck!!!!