How to set many to many relation where for different relations status of entity might vary

Question

I have two entities Player and Game that are in many to many relation. I am developing my applicaton with EF core 2.1 so i had to set intermediate class and configure it using fluent api. I would like to have options that for every game, every player can decide if he will play, won't play or he not decided yet. In the end i would like to display information about every game with three lists consisting of players based on their "are you playing?" choice. and i am not sure how i should store this information in database

Player class

public class Player
{
    public Player()
    {
        this.PlayerGames = new HashSet<PlayerGame>();
    }

    public long Id { get; set; }
    public virtual ICollection<PlayerGame> PlayerGames { get; set; }
    .....
}

Game class

public class Game
{
    public Game()
    {
        this.PlayerGame = new HashSet<PlayerGame>();
    }
    public long Id { get; set; }
    public virtual ICollection<PlayerGame> PlayerGames { get; set; }
}

PlayerGame class

public class PlayerGame
{
    public long PlayerId { get; set; }
    public Player Player { get; set; }
    public long GameId { get; set; }
    public Game Game { get; set; }
}

I came up with idea that i could add one column to PlayerGame intermediate table and store this information there. For example player John will play in Game A and B, but he won't play in game C.

So the intermediate table would look like:

_____________________________________
|PlayerId |GameId  | PlayerStatus    |
-------------------------------------
|JohnId   |GameAId | 1               |         1 - will play
|JohnId   |GameBId | 1               |         2 - won't play
|JohnId   |GameCId | 2               |         3 - not decided

Does this idea seem alright or do you have better alternatives?

Answer 1

For what you're looking for, and for simplicity's sake, your choice of PlayerStatus will work just fine. Depending on what database you are using you can leverage a different data type for that table column to work more seamlessly with EF.

For example, in SQL Server you can use the bit data type and make it nullable so that null in your EF model you can simply use a bool?:

public class PlayerGame
{
    public bool? PlayerStatus { get; set; }
}

The potential values will look like this: 1 - will play, 0 - won't play, null - not decided

HTH