Database design for User, UserRole - many to many relationship

Question

In my project, I have a User entity and a UserRole entity. According to my database design, a user can play multiple roles and a role can be associated with more than one user. The user roles I have in my system is USER and ADMIN.

In need user roles to be stored in the table and they need to be referred when a user is persisted in its relevant table.

However, according to the code I have implemented, records get inserted to the user role table too every time I insert a user to the database. What I need is to insert data into the User table and to the join table.

These are my entities.

User:

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, updatable= false)
    private Long id;

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

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

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

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

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "user_with_role",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")})
    private List<UserRole> roles;

}

UserRole:

@Entity
@Table(name = "userrole")
public class UserRole {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, updatable= false)
    private long id;

    @Column(name = "role_name")
    @NotNull
    @Enumerated(EnumType.STRING)
    private Role roleName;

    @ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
    private List<User> users;
}

This is how I persist a user to the database.

List<UserRole> roles = new ArrayList<>();

UserRole ur_user = new UserRole();
ur_user.setRoleName(Role.USER);

UserRole ur_admin = new UserRole();
ur_user.setRoleName(Role.ADMIN);

roles.add(ur_user);
roles.add(ur_admin);

newUser.setRoles(roles);
entityManager.persist(newUser);

entityManager.flush();

This mechanism is fine for the situations where the associated should be entered to the database together with the parent entity. (Eg: Contact Number of employees)

In my situation, I really do not understand the way I should write the code in order to not insert to the UserRole table when a user is persisted.

How Should I write the code in my situation. In addition, I would like to know what CascadeType I should use in User entity. I have put CascadeType.ALL, but I know that is not the best type in that place according to the context.

Maciej Kowalski · Accepted Answer · 2017-05-08T14:41:09.633

Well regarding @ManyToMany you should definately be very caucios about CascadeType.REMOVE so i would define that as CascadeType.PERSIST, CascadeType.MERGE.

Now, you can keep the rest of the mappings as they are fine. In order to not have the roles saved you must query for them first and not create them by hand like you do. For persistence provide that are just some new unmanaged entities and it tries to persist them on commit.

The following example takes advantage of already existing UserRole's and thanks to that only the User is persisted (using the already existing dependencies):

UserRole ur_user = entityManager.getRoleByType(Role.USER);
UserRole ur_admin = entityManager.getRoleByType(Role.ADMIN);

roles.add(ur_user);
roles.add(ur_admin);

newUser.setRoles(roles);
entityManager.persist(newUser);

Database design for User, UserRole - many to many relationship

1 Answers1