Many-To-Many or two One-To-Many's?

sagimann · **Joined:** Sun Jun 11, 2006 10:41 am **Posts:** 164

Hibernate version:
3.2.6
Name and version of the database you are using:
MySQL5

Hi,
Could someone pls suggest how to annotate the following? This is my case:
I have a Domain entity, and an Identity entity. Each Identity may be associated to one or more domains via an ALIAS, which is unique within a domain. For example, if I have 2 identities: Alice and Bob, and 2 domains: CarAgency and DrugStore, I would like to be able to "map" Bob and Alice to both domains, using different aliases. Let's say that Bob has the alias "b1" in the CarAgency domain, and the alias "b2" in the DrugStore domain. Alice has the alias "a1" in CarAgency, and "a2" in DrugStore.

This effectively creates a map such that:
map(CarAgency, a1) --> Alice
map(CarAgency, b1) --> Bob
map(DrugStore, a2) --> Alice
map(DrugStore, b2) --> Bob
map(DrugStore, b2) --> Alice : not allowed since alias 'b2' is already used for the DrugStore domain by the previous line.

In order to represent this map in native SQL, I would probably create a table that looks like this:
(domain_id, alias, identity_id)
where domain_id, alias make a composite PK.

How do I do this in hibernate? At first, I was thinking about Many-To-Many between Domain and Identity, which would somehow accomodate the Alias; or to define a 3rd entity, e.g. DomainIdentity such that:
Domain --> 1:N --> DomainIdentity <-- N:1 <-- Identity

any ideas on what's best and how to do it?
thanks.

hlfsousa · **Posted:** Wed Jun 18, 2008 3:36 pm

I think you would be better off with two many-to-one relationships. If you do a single many-to-many you will not have a nice place to set the alias. Plus you may want or need more attributes to the identity, such as authorization rules.

So, with EntityManager+Annotations you would end up with:

Code:

@Entity
class Domain {
  name;
  @OneToMany(fetch = FetchType.LAZY, mappedBy="domain")
  Set<DomainIdentity> domainIdentities;
}

@Entity
class DomainIdentity {
  @ManyToOne Domain domain;
  @ManyToOne Identity identity;
  String alias;
}

@Entity
class Identity {
  @OneToMany(fetch = FetchType.LAZY, mappedBy="identity";
  Set<DomainIdentity> domainIdentities;
  String name;
  // ... other properties
}

I removed the private/public modifiers and getters/setters to make it more readable.

If you want a shortcut from the identity to the domains, you could make a read-only many-to-many property using the table that maps DomainIdentity as the join-table. However, I personally do not recommend this.

Hope it helps. Regards,

Henrique