Why do I need the third table for many to many mapping ? Why can't I just use two tables?

Question

I have been trying many to many relationship between two classes Person and Address. I don't know but somewhere I am not thinking along the correct lines. For example for many to many mapping, I made two tables

CREATE TABLE person(p_id INTEGER,p_name TEXT,PRIMARY KEY(p_id));
CREATE TABLE address(a_id INTEGER,address TEXT);

and then I tried something in the mapping xml. After some unsuccessful attempts I read that you need three tables for many to many mapping, just as an answer to one of my question says.

Please explain me the reason for this ? Why do I need the third table ? Why cannot I make an association just with the two tables ?

Answer 1

Im using ManyToMany with JPA annotation, I need your valuable suggestions. (Assume Person and Address. Same Address is referred to more person (living at same address)). I have to delete a person from that address.

 Person p1 = new Person();
  Person p2 = new Person();
    Address add1 = new Address();

  p1.add(add1);
  p2.add(add1);

Using same add ref to both persons. As well doing

add1.add(p1)  ;
  add1.add(p2)  ;

THen on merge or persist iit mapped appropriately.

p1 - add1 p2 - add1

I have to delete p2 alone , when i did

p2.removeAddress(add1)
    removeAddress(add1) { coll.remove(add1) }

What happens is it deleted the entry for address and again by Hibernate jpa provider again tries to persist at Address entity and says "deleted entity passed to persist " and henc transaction roll back happens. I was ving maping as

@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "XXXX", joinColumns = { @JoinColumn(name = "X1_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "X2_ID", nullable = false, updatable = false) })
    private Collection<Parser> parsers;


    Please share your ideas.

Answer 2

Hmmm I'm not sure but I think he's talking about creating a "third class" in his mapping and not really "third table" (otherwise he wouldn't even talk about hibernate mapping). So I will assume that he's just struggling with his mapping for my answer (and it will still be useful later for his hibernate mapping) :

Thanks to the @JoinTable annotation you don't have to create an entity for the third table. If you have a many-to-many relationship between your tables, you just have to create 2 entities "Person" and "Address", and the @JoinTable annotation on each side will apply the many-to-many magic without having to create an entity for the third table between them.

EXCEPT if your third table have some extra columns, and then in that case you don't have a choice you will have to create a specific entity for this third table (or otherwise you can't get that extra column with just "Person" and "Address" entities).

Some useful links for your mapping :

http://www.mkyong.com/hibernate/hibernate-many-to-many-relationship-example-annotation/ http://www.mkyong.com/hibernate/hibernate-many-to-many-example-join-table-extra-column-annotation/

Answer 3

The third table serves as a junction table that defines the many to many relationship. In your example I assume that a Person can have multiple addresses and an address can belong to multiple People. This relationship cannot be modeled using two tables.

You may attempt to simply include a foreign key to the Address in the Person table or a foreign key to Person in the Address table. Each of these mappings would have a one side, meaning one of the particular entities corresponds with many of the other. None of these options achieve the many to many relationship and the use of a third table is required to map the more complex relationship.

In order to map as a many to many you need to be able to associate multiple instances of both entities with each other. This is traditionally done via the following:

Table A
ID_A

Table B
ID_B

Table C
ID_A
ID_B

Answer 4

Because of the nature of the relation.

If the mapping was one-to-one, than you could add a person_id column to the Address table and each Address tuple would point to just one Person.

 Address
+---------------------+
|id|p_id|address      |
+---------------------+
| 1|  1 |some street 1| //one address uniquely points to one person
+---------------------+
| 2|  2 |new street 5 | 
+---------------------+

Same goes for one-to-many: if a Person can have multiple Addresses, then there would be multiple tuples in Address table with the same person_id.

 Address
+---------------------+
|id|p_id|address      |
+---------------------+
| 1|  1 |some street 1| //two addresses point to one person
+---------------------+
| 2|  1 |new street 5 | 
+---------------------+

But what if one Person can have mutliple Addresses, but also, one Address can belong to multiple Persons? Then one column person_id in the Address table would not be enough because one Address can relate to many Persons! So you need a third table to associate all pairs of Persons and Addresses.

 Assoc table
+---------+
|a_id|p_id|    
+---------+
| 1  |  1 | //one address for two persons
+---------+
| 1  |  2 | 
+---------+
| 2  |  3 | //two addresses for the same person
+---------+
| 3  |  3 | 
+---------+