Question (Database Design): Game <-> Player Relationship

Why did you choose to add a player_x_id and a player_o_id column to the users table?

The definition of Game::players and Player::games suggests that the relationship between the two entities would have been better served if it was a many to many relationship between (belongsToMany in Laravel).

The database would change from: https://share.getcloudapp.com/7KuxOv85 To: https://share.getcloudapp.com/Z4uzgJr2

The following in Game.php would then change from: https://github.com/TobiasLounsbury/EmpireSkillTest/blob/9bebb5ebaa5bbde267e03a2ec544b28af9f9c286/app/Models/Game.php#L111-L135

To something like:

    public function players() : BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }

    public function playerX() : ?User
    {
        return $this->players->where('strike_indicator', 'X')->first();
    }

    public function playerO() : ?User
    {
        return $this->players->where('strike_indicator', 'O')->first();
    }

I prefer to keep as much data in the relevant table(s) as makes sense for a given project. In this case the data for which players belongs to which games remains tied to the game table and as this project is unlikely to ever evolve I didn't have an issue keeping the database design simple. I developed the habit of limiting the use of lookup tables because of two situations that I have encountered many times over the years: Firstly, trying to write reports against databases (without ORMs) where absolutely everything and anything was a lookup and trying to join 6 tables to get a list of games (for instance) would be a bit overboard, and similar situations have caused me a ton of headaches. Secondly: Trying to handle poorly backed up databases where clients have only exported the "relevant" tables and now all of the relationships are lost. This second situation obviously comes about because someone did something monumentally dumb, like not backing up the whole database but I have experienced it enough times that it has had a small influence on my database designs, at least in so far as it doesn't incur any technical debt.

In the context of a proper ORM, with complete database backups lookup tables make sense, and I absolutely could and probably should have used one here.

Using a many:many relationship makes more sense if the complexity of relationships were to evolve over time. For instance the lookup pattern you suggested would be infinitely superior if we wanted to make games private where you had to have other users associated with a game in a "watcher" capacity/role. I chose not to use a lookup because in the context of a finite number of relationships for this test it seemed simpler and I didn't have a good reason at the time to add the complexity.

TobiasLounsbury / EmpireSkillTest

Question (Database Design): Game <-> Player Relationship #8