How to model many-to-many relationship

Question

How do I implement the following relationship using JPA?

table person (
  id int,
  name text
)

table person_home (
    person_id int,
    home_id int,
    type char(1)       -- 'p' = primary, 's' = secondary
)

table home (
    id int,
    address text
)

A person can have many homes, and a home can have many persons living in it (i.e. ManyToMany relationship). Furthermore, a home can be a primary residence for one person, but a secondary residence for another person at the same time.

I'm not sure how to model this relationship, even though the database schema is clear.

I have thought of splitting mapping table person_home into person_primary_home and person_secondary_home, however I would prefer to retain the schema if possible.

Answer 1

This question is pretty much asked and answered here:
How to create a composite primary key which contains a @ManyToOne attribute as an @EmbeddedId in JPA?

You need four classes:

1. Person.java
2. Home.java
3. PersonHome.java
4. PersonHomePk.java

The Person.java and Home.java files you create with one to many relations to the PersonHome.java. They'll have @Id fields to identify the primary keys. Each will have a @OneToMany relation defined with at least a mappedBy attribute mapping to their respective fields in the PersonHome entity. i.e. in the Person.java you could have something like

@OneToMany(cascade = CascadeType.ALL, mappedBy = "Person")
private Collection<PersonHome> personHome;

The PersonHome.java will have an @EmbeddedId field to identify the PersonHomePk instance declaration which is its primary key (that is, instead of an @Id column you will have an @EmbeddedId annotating a declaration of a class representing the primary key of the join table PersonHome). Any other fields are declared as normal columns. The PersonHome.java will also declare two ManyToOne relations one each to person and home. These will use @JoinColumn annotation (make sure they have the attributes insertable=false and updatable=false). The datatypes will be the Person and Home classes. i.e.

@EmbeddedId
protected PersonHomePk personHomePk;
@Column (name = "type")
private String type; 
@JoinColumn(name = "person_id", referencedColumnName = "person_id", insertable = false, updatable = false)
@ManyToOne(optional = false)
private Person person;

You'll need the same for the "Home" declaration too.

Why are you using only a char for "type". I'd recommend a varchar so people who maintain the thing once you're gone will understand the code and database better when you aren't around. 'detached' is easier to understand the 'd'.

Answer 2

I believe if you're going to have metadata besides the relationship on the person_home table, you need to use three objects with two one-to-many relationships in order to be able to access all of the data.

You could eliminate this need by having two many-to-one relationships from the person table to the home table, by having primary_home_id and secondary_home_id -- unless I'm missing a requirement here and a person can have more than one primary or secondary home.