Eloquent design for a game tracker app...

I am trying to build a simple application to track games of Connect 4 (AKA 4 in a row) between users.

I am using latest Laravel Breeze and Splade. I've been running into some issues and I think it's bugs relating from my eloquent design.

I've got a couple of tables: users and games. Here's the design of each below.

Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

Schema::create('games', function (Blueprint $table) {
            $table->id();

            $table->unsignedBigInteger('player_id');
            $table->foreign('player_id')->references('id')->on('users');

            $table->unsignedBigInteger('opponent_id');
            $table->foreign('opponent_id')->references('id')->on('users');

            $table->unsignedInteger('playerScore');
            $table->unsignedInteger('opponentScore');

            $table->date('date');

            $table->text('description')->nullable();
            $table->timestamps();
        });

I think I'm causing myself problems by this design, but I can't put my finger on it. It just seems overly complex, especially when I'm trying to extract statistics etc from the games table and I'm certain there must be a quicker/easier/more reliable way to store game data. I'd be interested to see anyone's thoughts on this subject.

if possible I'd like to keep the ability for user A to add a game they played against user B and vice versa.

I've Googled it and searched in these forums but I can't really see anything similar popup. Thanks in Advance.

LaryAI

2 years ago

Level 58

Based on the provided information, it seems like the current design of the tables is sufficient for tracking games between users. However, if you're experiencing issues or find the design overly complex, there are a few suggestions you can consider:

Simplify the table structure: If you find the current table structure too complex, you can simplify it by removing unnecessary columns or splitting the tables into smaller ones. For example, you could have a separate table for game statistics instead of storing them directly in the games table.
Use a pivot table for player relationships: Instead of having separate columns for player_id and opponent_id, you can create a pivot table to store the relationships between players and games. This way, you can easily retrieve games played by a specific user or games where two users played against each other.

Here's an example of how you can modify the table structure:

Schema::create('games', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('winner_id')->nullable();
    $table->date('date');
    $table->text('description')->nullable();
    $table->timestamps();
});

Schema::create('game_user', function (Blueprint $table) {
    $table->unsignedBigInteger('game_id');
    $table->foreign('game_id')->references('id')->on('games');
    $table->unsignedBigInteger('user_id');
    $table->foreign('user_id')->references('id')->on('users');
    $table->boolean('is_winner')->default(false);
});

In this modified structure, the games table only contains the necessary information about a game, such as the date and description. The winner_id column can be used to store the ID of the winning player, or it can be nullable if you don't need to track the winner.

The game_user table acts as a pivot table to establish the relationship between users and games. It also includes an is_winner column to indicate if a user won the game.

With this structure, you can easily retrieve games played by a specific user or games where two users played against each other using Laravel's Eloquent relationships.

I hope this helps! Let me know if you have any further questions.

Please or to participate in this conversation.