many-to-many with Identifier

atorres · **Posted:** Fri Nov 18, 2005 1:34 pm

Is it possible, or will it be someday, to define a many-to-many relationship that has a unique independent identifier, instead of composite PK.
That way I can evolve my model to a two many-to-one relationship with an extra class without breaking the database model , and I can refactor my collection get/set methods.

I'm not very experienced with many-to-many becouse I never use composite PKs.

Thanks.

RichMacDonald · **Posted:** Fri Nov 18, 2005 11:51 pm

Its not possible now. But I'm not sure what you're trying to do. Are you talking about replacing "A holds a collection of B and B holds a collection of A" with "A holds a collection of C and B holds a collection of A and C points to A and B"?

christian · **Posted:** Sat Nov 19, 2005 5:07 am

Of course its possible, use idbag.

atorres · **Posted:** Sat Nov 19, 2005 5:57 am

of course! but them I can't use Set right?
but... can I enforce that 2 objects relate only once ?
I will play with idBag a little.
Thanks Christian

christian · **Posted:** Sat Nov 19, 2005 7:35 am

If you have a set you don't want duplicate elements. If you have a surrogate primary key column on the collection table, you can put in duplicate elements. Pick one.

atorres · **Posted:** Sat Nov 19, 2005 9:35 am

I think it's a limitation, If I could, I would pick the right thing that is none of the two above options.
I want to design db to last but classes may evolve (I think it's an old concept from Relational models that is still known).
Ok, it's not prioritary.

Quote:

That's why I don't like many to many relations, only monogamic ones :o)

Well, for the sake of not having composite keys that my dba forbids, I will try to live with bags and some kind of unique check that I may fix.
I'm getting problems now with inverse relationship btw Bag and IdBag. First, I can't find an example with inverse relationship of idbags. On forum, I found a post that says that Idbag must use bag as inverse. So I did it and
them the DDl generator goes crazy with my mapping:

Person to role

Code:

<bag inverse="true" name="RolesOfPerson" >
         <key column="idPerson"/>
         <many-to-many column="idRole" class="test.Role"/>
</bag>

Role to Person

Code:

<idbag name="RolesOfPerson" table="RolesOfPerson">
         <collection-id column="ID" type="java.lang.Integer">
            <generator class="increment"/>
         </collection-id>
         <key column="idRole"/>
         <many-to-many column="idPerson" class="test.Role"/>
</idbag>

ddl output (!)

Quote:

create table RolesOfPerson (idRolesOfPerson int4 not null, persons int4, roles int4, idPerson int4 not null, idRole int4 not null, ID int4 not null, primary key (ID));

LOT's of columns!! what is the trick to make it work with 3 columns?

christian · **Posted:** Sat Nov 19, 2005 9:43 am

"Well, for the sake of not having composite keys that my dba forbids"

Your DBA is incompetent.

atorres · **Posted:** Sat Nov 19, 2005 11:21 am

is he incopetent becouse he wants to have all tables with unique identifier ?
interesting point of view :)
hey, relax, you guys rock. Take the day off man ... you're stressed.
I will discover how to use idBag (frodo baggins may know something about it)
Thanks !

christian · **Posted:** Sat Nov 19, 2005 12:33 pm

This (idPerson, idRole) is clearly the primary key of that table. Anything else would need a very good reason, which I doubt is included in "forbid". I'm not stressed at all, just trying to get you (or the "DBA") thinking again before you do something you might regret later.

If you have Hibernate in Action the mapping you want, if you are sure that your "forbid" policy is reasonable, is on page 229 with an idbag instead of a set.