Should I use a many-to-many relationship or obtain info through one-to-many relationships?

user_6749888 0
Viewed 100

So I have a war-like game with three tables: User, Player, and Game.

  • The User table contains email, username, password hash, and other user-specific info.
  • The Game table contains info about each game and the state of each game world.
  • The Player table contains info about each player in a game: how many units they have, etc.

I have the following relationships:

  • A one-to-many relationship between User and Player, since each User can be in several different Games, and control several different Players simultaneously.
  • A one-to-many relationship between Game and Player, since each Game has multiple Players in it.

So in order to find the Games that a specific User is in, is it better practice to either:

  • Make a many-to-many relationship between User and Game, or
  • Write a function that takes the list of Players from a User, then find the Game IDs from the Player columns?

(I'm using SQLAlchemy with SQLite and Flask, but I don't think that's important to this question.)

The attached image may explain it better: should I have any database connections between User and Game?

I'd be glad to clarify anything, and thanks for any possible help! Sorry if this is a bad question for StackOverflow.

sql database
1 Answers

When a many-to-many relationship exists, say gameUsers, a common implementation is to create an intermediary table, containing the ids of each of the tables, in this case a list of user_id, game_id pairs representing which games each user is in.

But... you already have such a table, the player table. So my approach would be to think of the player table as that resolution table. The player table already contains which games each user is in, so that second many-to-many is not really required.

user_20028170
Related