Hi All,

In my application I have User, Group and Role objects. The roles can be associated with either User or Group. I have corresponding classes and tables. So I am maintaining a table called ROLE_MAP. In that table I am storing ENTITY_TYPE, ENTITY_ID and ROLE_ID.

Here, ENTITY_TYPE is either "U" (U for user) or "G" (G for group).

ENTITY_ID is either user_id or group_id and the ROLE_ID is the role id associated.

From "User to Role" it is many-to-many.
Similaly "Group to Role" it is many-to-many.

I created a class for ROLE_MAP called RoleMap
If I want to associate/ dissociate (User and Role) or (Group and role)
I am doing using that RoleMap class. But I do not like this approach.

For some reasons, I do not want to create USER_ROLE_MAP and GROUP_ROLE_MAP. (some more entities may comeup in the future which can be associated with roles!!!!)

But I do not want to lose the advantages that Hibernate provides.ie loading the collection of roles when the user is loaded etc.

Also, is there anyway that I can maintain the foreign key relation between USER, GROUP and ROLE_MAP depending on the value of ENTITY_TYPE?

I think this is a common database design and hibernate certainly supports..can someone give an example. That would help me a lot!!!



Thanks,
SSSS.

I was trying to do this for log entries. I wanted one LOG table, but also to maintian a reference from the log entry to the entity that was modified. The problem is basically that you need to include the table name as part of the database 'address'. This is not something SQL does easily, however Hibernate can help.

I did it by creating a class called LogEntry (in your case Role), which specified the table name and everything. Then made a subclass for each entity I wanted to log (only a few), call it FooLogEntry (in yourcase GroupRole and UserRole), which I ONLY used to define a descriminator column, no extra fields. This way when Hibernate goes to populate the set of LogEntrys associated with Foo, it actually uses the FooLogEntry class. Of course you have to have Foo build the LogEntry for you, as only it should know about FooLogEntry. This seems to work nicely, although it is a bit complex.

Once set up, it allows me to have one-to-may collections, where the 'many' table is pointed to from multiple 'one' tables. I can go into more (better?) detail, if this sounds like what you're looking for, or if I sound crazy because somebody has a better solution. =)

--Ryan

Or you can use the where attribute of the many-to-many collection mapping.

Ooops, yes, heh, my solution is for a one-to-many bidirectional mapping.
--Ryan