How do I work out the entity relationships in my game collection database?

I have a game collection application where I want to create seperate lists of games. I am using Spring JPA and Hibernate in the backend and this is what the Entity classes look like right now.

@Entity
public class Game {

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

    private String name;
    
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "game_genre",
        joinColumns = @JoinColumn(name = "game_id"),
        inverseJoinColumns = @JoinColumn(name = "genre_id"))
    private Set<Genre> genres;
    
    private int rating;

    @ElementCollection
    private List<String> platforms;

    private String publisher;
    
    private LocalDate releaseDate;

    @ManyToOne
    private GameShelf gameShelf;
}

@Entity
public class GameShelf {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    
    @OneToMany(mappedBy = "gameShelf")
    private Set<Game> games;
}

@Entity
public class Genre {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    
    @ManyToMany(mappedBy = "genres")
    private List<Game> games;

}

I am not sure if this is even the right approach… In the end, I want to create a Spring Boot application where users can log in (not implemented yet) and manage their own lists (zero or more lists for every user).

In my case, won’t I end up with a lot of duplicate games in my game table? Lets say I create multiple lists and add the same game to each, in my case, I will end up with multiple rows of the same game mapping to different lists.

Is there a better approach here? And is there maybe a generalized approach to solving database relationship problems like this that I can apply to future problems?

Answer

The relationship between your gameshelf and games needs to be ManyToMany. You want games in many shelves and shelves with many games. Using that relationship creates a third table that will manage the connections between the two and you won’t get duplicated games or shelves in any table.