Multiplayer game participant matching for 3 or more users

Question

I am building a small hobby website, primarily in Python and SQL on the back end, and I am not sure how to approach a problem conceptually.

What is an efficient way to get three or more players matched in a multiplayer game? I originally planned to do most of this through SQL statements, where each active player gets entered into an SQL table, let's call this, the active games table, upon them indicating that they want to play a game. Following that, I was going to have the SQL active games table perform a trigger function, where, when a specified number of players are entered into that table, they are all assigned some game_ID that specifies that they are to be assigned to a specific game. Then, this game_id is used to ensure all actions take place uniquely among those members. On top of that there would need to be time out triggers for active players if the queue got too long.

Is this an efficient way to approach this task? I am worried that this is a high number of SQL database commands. I have also recently heard that using SQL triggers for frequently used commands is generally frowned upon. If this is not a smart way of approaching this, what are some more efficient/scalable ways of approaching user matching for multiplayer matches? Please be as detailed on the algorithm steps as possible. It seems obvious that this has been done pretty frequently, given how many multiplayer games exist, but I have really struggled to find material or any other source of guidance on how to approach it.

Edit: The relevant parameters are primarily to group people into games of their choosing. The others that are of interest and important to matching users, are perhaps skill level, where one can weight how likely three users are to be assigned to the same match based upon their win/loss record.

For now, I would like to know how to handle assigning exactly three players to a game. I am ok, and would still find it tremendously useful, if an algorithm were described even without taking into account user skill level however, so either type of description would be very much appreciated.

You need a lot more parameters. For instance, is there a minimum and a maximum number of players in a game? Are there any criteria by which players get matched to one another (e.g. skill level, geographic proximity)? Are all games matched through a lobby or can someone create a private game? Etc etc. Any way you slice it, I don't think triggers are the way to go. Having some process that wakes up periodically and matches players seems like a better approach. But that's just a guess based on the limited info I have now. — Ben Thul, Oct 17 '15 at 04:22
I have edited the above to better explain the relevant parameters. Alright. I had sort of considered something similar to what I think you mentioned. You mean something like a stored procedure? Is there a way, and would you recommend, a way to tie this to user generated events? I.E 10 players all of a sudden decide to play the same type of game, how would a stored procedure that occurs at timed intervals sort these players into groups of three? I assume this could be done with an SQL group by statement added to a timed, stored procedure? Thanks for the suggestions so far by the way. — JStewy, Oct 17 '15 at 16:39

score 0 · Accepted Answer · answered Oct 18 '15 at 18:16

Turning our above conversation into an answer, a stored procedure is exactly what I had in mind. In the implementation that I have in my head, looks something like this:

create procedure dbo.matchPlayers
as
begin
    declare @gameID int;
    declare @players table (
       PlayerID int
    );
    while ((select count(*) from dbo.waitingPlayers) > 3)
    begin
        begin transaction;

        exec @gameID = dbo.createGame;

        update top(3) p
        set gameID = @gameID
        output inserted.PlayerID into @players
        from dbo.Player as p
        join dbo.waitingPlayers as w
           on p.PlayerID = w.PlayerID
        order by p.SkillLevel;

        delete w
        from dbo.waitingPlayers as w
        join @players as p
           on w.PlayerID = p.PlayerID;

        delete @players;
        commit transaction;
    end
end

Of course, this is a very simple implementation that assumes certain things about your schema. Chances are your schema differs. You may want to change the implementation based on such things as:

What if there are fewer than 3 waiting?
You could have more complex matching criteria. Here I'm matching the nearest three. But there's no guarantee that you won't have one high skill player matched with two with much lower skill.
Much, much more that you'll know about your requirements.

Multiplayer game participant matching for 3 or more users

1 Answers1