If your queries know beforehand which type of game you're refering to, and you are only having one game per query, the cleanest way is most probably to have different surrogate-tables for different games, and always JOIN the main table with the one specific for your type of game.
For instance, you would have a table for all the different types of games:
CREATE TABLE games
(
game_id integer PRIMARY KEY,
game_name varchar(100) NOT NULL
) ;
INSERT INTO games
(game_id, game_name)
VALUES
(1, 'straight pool'),
(2, 'one pocket'),
(3, '9 Ball'),
(4, '8 Ball') ;
A table for players (and probably for leagues, seaons, etc.)
CREATE TABLE players
(
player_id integer PRIMARY KEY, -- probably autoincrement
player_name varchar(100)
) ;
INSERT INTO players
(player_id, player_name)
VALUES
(1, 'Player 1'),
(2, 'Player 2'),
(3, 'Player 3'),
(4, 'Player 4') ;
Your matches table:
CREATE TABLE matches
(
match_id integer PRIMARY KEY, -- probably autoincrement
game_id integer NOT NULL REFERENCES games (game_id),
player_1_id integer NOT NULL REFERENCES players (player_id),
player_2_id integer NOT NULL REFERENCES players (player_id),
match_date date NOT NULL,
-- probably a league_id
-- probably a season_id
-- some other data common to all matches
UNIQUE (game_id, player_1_id, player_2_id, match_date) -- Alternate key
) ;
And then, for each specific game type, a table with all the columns that makes sense:
CREATE TABLE straight_pool_matches
(
match_id integer REFERENCES matches(match_id),
-- Don't know enough to know what to put here
info_about_straight_pool_matches varchar(255)
) ;
So, let's image there is a match of "straight pool" on 1st Jan 2017, between players 1 and 2. You would add data to two tables:
INSERT INTO matches
(match_id, game_id, player_1_id, player_2_id, match_date)
VALUES
(1, 1, 1, 2, '2017-01-01') ;
INSERT INTO straight_pool_matches
(match_id, info_about_straight_pool_matches)
VALUES
(1, 'Something that makes sense about a straing pool match') ;
And the way you would perform your queries would most probably be:
SELECT
m.match_id, p1.player_name AS player_1, p2.player_name AS player_2, m.match_date,
s.info_about_straight_pool_matches
FROM
matches m
JOIN players p1 ON p1.player_id = m.player_1_id
JOIN players p2 ON p2.player_id = m.player_2_id
JOIN straight_pool_matches s ON s.match_id = m.match_id
WHERE
m.game_id = 1
ORDER BY
m.match_date, m.match_id ;
match_id | player_1 | player_2 | match_date | info_about_straight_pool_matches
-------: | :------- | :------- | :--------- | :----------------------------------------------------
1 | Player 1 | Player 2 | 2017-01-01 | Something that makes sense about a straing pool match
dbfiddle here
