Many To Many Relationship

User.java

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "username", nullable = false, unique = true)
    private String username;

    @Column(name = "password", nullable = false)
    private String password;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(
            name = "user_roles",
            joinColumns = { @JoinColumn(name = "user_id") },
            inverseJoinColumns = { @JoinColumn(name = "role_id") }
    )
    private List<Role> roles;

}

Role.java

@Entity
@Table(name = "roles")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToMany(mappedBy = "roles")
    private List<User> userId;

    @Column(name = "role_name", nullable = false)
    private String roleName;
}

One To Many Relationship

User.java

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "username", nullable = false, unique = true)
    private String username;

    @Column(name = "password", nullable = false)
    private String password;

    @OneToMany(mappedBy = "userId", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Role> roles;

}

Role.java

@Entity
@Table(name = "roles")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)
    private User userId;

    @Column(name = "role_name", nullable = false)
    private String roleName;

}

In the case of @OneToMany I would have to create unique Role entities for each User even if identical Role entities exist. However, I could easily delete any Role entities using orphanRemoval = true.

But if I use @ManyToMany I would not have many identical Role entities for each user (such as: USER, USER, USER, ADMIN, ADMIN). However, I couldn't use orphanRemoval.

What is the most common type of relationship between Users and their Roles (permissions)?

I understand your concern, but you can easily delete a role in a many-to-many relationship by removing all rows from the junction table where roleId = {erasedRoleId}.

These entities are commonly mapped as a many-to-many relationship for the following reasons:

1. Data optimization and scalability: as you said, you would avoid identical Role entities. Imagine if the Role has hundreds of attributes, the efficiency would be terrible. In comparison, a many-to-many relationship would maintain the same number of Role entries, each with just a few shared attributes, optimizing storage and efficiency.

2. Mutual awareness of Users and Roles: This is more of a business decision. It's essential for a User to know their Roles, but it often makes sense for a Role to also track which Users have its permissions. This bidirectional relationship allows better control and management of permissions within the system.