many-to-many relationship table with additional columns

velmurugan22k · **Joined:** Tue Oct 07, 2008 12:17 pm **Posts:** 13

Hi,
I've a problem with mapping many-to-many relation.

My requirement is to find the menus with all their roles and menu_role properties.

I found some examples for normal many-to-many relation with 3 tables, in which the 3rd table contains only the foreign keys.

But in this scenario, in addition to the menu_id and role_id there are 2 more fields READ_FLAG and WRITE_FLAG in the MenuRole table.

There is a table called MENU which defines the menu properties.
The other table called ROLE which defines the role properties.
And the 3rd table called MENU_ROLE which defines the role properties (read, write, delete) on Menus. And the table descriptions are:

CREATE TABLE CS_MENU (
CS_MENU_ID NUMBER(10 , 0) NOT NULL,
NAME VARCHAR2(100) NOT NULL,
DISPLAY_NAME VARCHAR2(100)
);
-------------------------------------------------
CREATE TABLE CS_ROLE (
CS_ROLE_ID NUMBER(10 , 0) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
);
------------------------------------------------
CREATE TABLE CS_MENU_ROLE (
CS_ROLE_ID NUMBER(10 , 0) NOT NULL,
CS_MENU_ID NUMBER(10 , 0) NOT NULL,
READ_ONLY_FLAG VARCHAR2(1) NOT NULL,
UPDATE_ONLY_FLAG VARCHAR2(1) NOT NULL,
DELETE_ONLY_FLAG VARCHAR2(1) NOT NULL
);
-------------------------------------------------

Now, I've written hibernate mapping files for the above entities as below.

Role.hm.xml:
--------------

Code:

<hibernate-mapping>
   <class name="com.cvc.authenticateUser.cssecurity.hibernate.CsRole" table="CS_ROLE">
      <id name="csRoleId" column="CS_ROLE_ID">
            <generator class="increment"/>
        </id>
      <property name="name" column="NAME"/>

     [b]    <!-- How to map here with MenuRole table -->[/b]

   </class>
</hibernate-mapping>

Menu.hbm.xml:
-----------------

Code:

<hibernate-mapping>
   <class name="com.cvc.authenticateUser.cssecurity.hibernate.CsMenu" table="CS_MENU">
      <id name="csMenuId" column="CS_MENU_ID">
            <generator class="increment"/>
        </id>
      <property name="displayName" column="DISPLAY_NAME"/>
      <property name="name" column="NAME"/>
[b]
         <!-- How to map here with MenuRole table -->[/b]

   </class>
</hibernate-mapping>

MenuRole.hbm.xml:
------------------------

Code:

<hibernate-mapping>
   <class name="com.cvc.authenticateUser.cssecurity.hibernate.CsMenuRole" table="CS_MENU_ROLE">
   
       [b]  <!-- How to map here with Menu table -->
         <!-- How to map here with Role table -->[/b]
      <property name="readFlag" column="READ_FLAG"/>
      <property name="writeFlag" column="WRITE_FLAG"/>
   
   </class>
</hibernate-mapping>

----------------------------------------

My requirement is to find the menus with all their roles and menu_role properties.

I found some examples for normal many-to-many relation with 3 tables, in which the 3rd table contains only the foreign keys.

But in this scenario, in addition to the menu_id and role_id there are 2 more fields READ_FLAG and WRITE_FLAG in the MenuRole table.

I'm trying to find the proper mappings for the last 1 week. But nothing helps me to resolve this. I would appreciate if some one please let me know how to map this relation with many-to-many requirement.

Sorry for the bit lengthy post.

Thanks in advance,
Vel.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Your problem is very common.

The solution is to decompose that many-to-many relationship into 2 one-to-many relationships: one from menu to menu-role, the other from role to menu-role.

velmurugan22k · **Joined:** Tue Oct 07, 2008 12:17 pm **Posts:** 13

Thanks for your reply Diaz..
I've created a primary key in the menu_role table and as you said, I decomposed the many-to-many relation into two one-to-many relations.
Its working perfectly now.

--Velu

velmurugan22k · **Joined:** Tue Oct 07, 2008 12:17 pm **Posts:** 13

Hi,
I've almost same kind of problem with the relationships with another 2 tables.
I would appreciate if you let me know how to resolve this relationship trap.

Following are the 2 tables called TOKEN which is having token properties and TOKEN_USER which is actually a relationship table with the user.
But USER doesn't represent any separate table. Its a varchar2 field and the relations to be mapped are,
1. 'A UserName can be assigned to any number of tokens'.
2. 'A Token can be assigned to any number of UserNames'.

Code:

CREATE TABLE CS_TOKEN (
      CS_TOKEN_ID NUMBER(10 , 0) NOT NULL,
      CS_APPLICATION_ID NUMBER(10 , 0) NOT NULL,
      NAME VARCHAR2(30) NOT NULL,
   );

CREATE TABLE CS_TOKEN_USER (
      CS_TOKEN_ID NUMBER(10 , 0) NOT NULL,
      USERNAME VARCHAR2(30) NOT NULL
   );

I've created two java objects, one represents TOKEN entity and the other is User.java. I created User.java having properties like username, (email, network_id,etc., fields will be populated later from the peoplesoft table view). Only UserName property of User.java has to be mapped with TOKEN_USER table.

Below is the mapping file for Token table.

Code:

<hibernate-mapping><class name="com.cvc.authenticateUser.cssecurity.hibernate.CsToken" table="CS_TOKEN">
   <id name="csTokenId" column="CS_TOKEN_ID">
            <generator class="increment"/>
        </id>
   <many-to-one name="csApplication" column="CS_APPLICATION_ID"/>
   <property name="name" column="NAME"/>
<!--
        <set name="csUsers" table="CS_TOKEN_USER">
       <key column="CS_TOKEN_ID"/>
       <many-to-many column="USER_NAME" class="com.cvc.authenticateUser.cssecurity.hibernate.CsUser"/>
       -->
</class>
</hibernate-mapping>

I'm really confused as I don't have a separate USER table which can be possibly mapped with User.java.

Please let me know, do I need to create separate mapping file for TOKEN_USER table and what are the possible relationships can be mapped in this situation.

Thanks in advance,
Velu