Many-to-many one way mapping and use only two tables

Question

I have two entities: Project, Employee

• Employee has primary key {employeeId} + some other attributes
• Project has primary key {projectId}

Code:

public class Employee {
  Long employeeId;
  String name;     
}
public class Project {
  Long projectId;
  Collection<Employee> employees;
}

Employee and Project is a one way many-to-many relationship. The general approach is to have three tables: Employee, Project, EmployeesAssignedToProjects.

Employee
----------
employeeId (PK)
name

Project
----------
projectId (PK)

EmployeesAssignedToProjects
----------------------------
projectId (FK)
employeeId (FK)
{projectId,employeeId} (PK)

Since Project doesn't have other attributes other than its id, the Project table is not really necessary. This brings to the question of how should this many-to-many relationship be mapped now that Project essentially maps to EmployeesAssignedToProjects.

Note that Employee doesn't have a pointer back to Project. The typical mappedBy construct can't be used here.

[-- Update --]

The problem is a little more complicated: Both Project and Employee have composite keys.

• Project's key is {companyId,projectId}
• Employee's key is {companyId,employeeId}

I adopted the 3 tables set up. Table PROJECT_EMPLOYEE has 3 columns: companyId, employeeId, projectId. And I mapped in xml:

<many-to-many name="PROJECT" >
 <join-table name="PROJECT_EMPLOYEE">
  <join-column name="companyId" referenced-column-name="companyId"/>
  <join-column name="employeeId" referenced-column-name="employeeId" />
  <inverse-join-column name="companyId" referenced-column-name="companyId" />
  <inverse-join-column name="projectId" referenced-column-name="projectId" />
 </join-table>
</many-to-many>     

I received a error complaining companyId appearing multiple times: Repeated column in mapping for collection: Project.employees column: companyId

Answer 1

Given the class files you provided you probably are trying to change n - m relationship to n - 0..1 relationship.

This can be done by removing the EmployeesAssignedToProjects and denormalizing the Employee table by adding projectId (FK) to it:

Employee
----------
employeeId (PK)
projectId (FK)
name

Project
----------
projectId (PK)

Is this what you really want? I dont recommend it.

Answer 2

Note that Employee doesn't have a pointer back to Project. The typical mappedBy construct can't be used here.

This is irrelevant anyway.

And the fact is that all ManyToMany relationships require a JoinTable. The JoinTable is defined using the @JoinTable either implicitly or explicitly.

@Entity
public class Project {
    @Id
    @Column(name="PROJECTID")
    private Long projectId;

    @ManyToMany
    @JoinTable(
        name="PROJECT_EMPLOYEE",
        joinColumns={@JoinColumn(name="PROJECT_PROJECTID", referencedColumnName="PROJECTID")},
        inverseJoinColumns={@JoinColumn(name="EMPLOYEE_EMPLOYEEID", referencedColumnName="EMPLOYEEID")})
    private Collection<Employee> employees;
    ...
}

You can try to define the PROJECT table itself as the JoinTable if you only need reads support (and I'm not even sure it will work correctly) but this won't work for writes.

In other words, I would stick to the regular construct to represent many-to-many relations in a database, i.e. with a join table.