How to enforce a 1 to n relationship

Question

I'm working on an online muiltiplayer board game & have a SQL server question.

Lets assume the game allows two players. When the game is created, the creator is added as the first user.

At that point, two users could try to join the game simultaneously. One of these users should be blocked.

My database schema is as follows:

tbGame - contains a list of all games. PrimaryKey is GameId
tbPlayers - contains a list of all registered users. PrimaryKey is PlayerId
tbPlayersInGame - contains a list of all players in each game. Foreign key 
                  relations to tbGame and tbPlayers.

I feel like I need two things:

1. A way to lock tbPlayersInGame based on the GameId. This would be used while adding a player to a game. From what I've read it sounds like a key-range lock (on GameId) would be suitable.
2. A way to enforce a 1-to-2 relationship so that attempts to add a 3rd player would fail.

Answer 1

A couple suggestions:

1) When you try to write to the tbPlayersInGame table first do a SELECT to make sure the game isn't full, then INSERT INTO the table. Wrap this SELECT and the INSERT INTO in a transaction with the transaction isolation level set to serializable.

2) Don't have a separate tbPlayersInGame table, instead have 2 fields in tbGame: Player1Id, Player2Id

Answer 2

Instead of a 1-to-n relationship, you could set up your table to have to columns - probably labelled host_player and visitor_player, or the necessary equivalent (like in chess - black and white). This has the advantage of keeping funny things to a minimum, and showing the natural state of the relationship.

This of course only works if there is supposed to be a permanently limited number of players - for most instances of boardgames, this will likely work fine....

If you are attempting to make a game where the upper limit is mutable (for whatever reason), you can 'detect' free slots available with the following statement:

INSERT INTO game_players (game_id, player_id) 
SELECT VALUES (:GAME_ID, :PLAYER_ID)
WHERE :MAX_PLAYER_COUNT > (SELECT COUNT(*)
                           FROM game_players
                           WHERE game_id = :GAME_ID)

If you get back an error-code of 100 ('No rows selected/updated), the player list is full. You may need to add other conditions (to prevent players from joining twice), but the concept should still work.