How to map two entities through an associate link entity

Question

I need to keep record of various businesses their cities and their branches in each city. Each business might be in different cities and in each city might have different branches.

Take a bank as an example. It might be in cities A and B and in city A might have two branches and in city B only one.

I suppose the branch table should have branchid and foreign keys of both primary keys of the join table. In this way I can make sure no branch will be associate to more than one combination of city and business.

 Business       City
     \          /
  BusinessID CityID    <<join table has primary keys of Business and City tables
          |
        Branch
BranchID BusinessID CityID

Sample data

Business Table          
1         
2       
3        

City Table
A
B
C

Join Table
Business_City
1         A
1         B
2         A
3         C

Branch Table
Business City   Branch
1          A      I1
1          A      I2
1          B      I6
2          A      I5
3          C      I3

As you can see businesses 1 and 2 are both in city A. Business 1 has two branches in city A whereas business 2 has just one branch etc.

How should I map the Branch to a Business and a City?

Answer 1

This is how your mappings should look like:

@Entity
public class Business {
    @Id
    private Long id;

    @OneToMany(mappedBy = "business")
    private Set<Branch> branches = new HashSet<>();
}

@Entity
public class City {
    @Id
    private Long id;

    @OneToMany(mappedBy = "city")
    private Set<Branch> branches = new HashSet<>();
}

@Entity
public class Branch {

    @Id
    private Long id;

    @ManyToOne
    @JoinColumn(name = "business_id")
    private Business business;

    @ManyToOne
    @JoinColumn(name = "city_id")
    private City city;
}

This intermediate Entity mapping is better than using a @ManyToMany association which might generate some less efficient SQL queries and would not allow you to add some additional columns to the link table.