How to Design Graph-Based Data Models for Player-Game Relationships: A SQL Implementation Guide

Model player-game interactions as a property graph using separate vertices and edges tables, enabling flexible traversal queries for opponent analysis, career tracking, and co-play statistics without schema migrations.

Graph-based data modeling transforms complex player-game interactions into traversable relationships, capturing many-to-many connections that traditional relational schemas struggle to express. The DataExpert-io/data-engineer-handbook repository demonstrates this approach using PostgreSQL ENUM types and composite primary keys to build extensible graph structures for sports analytics. By treating players, teams, and games as vertices and their interactions as typed edges, you enable powerful queries such as tracing career progression across seasons or computing head-to-head statistics.

Core Schema Architecture

Defining Vertex and Edge Types

The foundation of the graph model resides in intermediate-bootcamp/materials/1-dimensional-data-modeling/lecture-lab/graph_ddls.sql, which establishes strict type safety through PostgreSQL ENUMs. The vertex_type ENUM categorizes entities as player, team, or game, while the edge_type ENUM classifies relationships including plays_in, plays_against, shares_team, and plays_on.

CREATE TYPE vertex_type AS ENUM('player', 'team', 'game');

CREATE TYPE edge_type AS ENUM (
    'plays_against',
    'shares_team',
    'plays_in',
    'plays_on'
);

Creating the Vertices and Edges Tables

The schema separates static entity attributes from dynamic relationships. The vertices table stores unique entities with a composite primary key on (identifier, type) and a JSON properties column for flexible attributes like player names or team logos. The edges table implements the graph structure using subject-object terminology, where each relationship references both endpoint identifiers and their respective types.

CREATE TABLE vertices (
    identifier TEXT,
    type vertex_type,
    properties JSON,
    PRIMARY KEY (identifier, type)
);

CREATE TABLE edges (
    subject_identifier TEXT,
    subject_type vertex_type,
    object_identifier TEXT,
    object_type vertex_type,
    edge_type edge_type,
    properties JSON,
    PRIMARY KEY (subject_identifier, subject_type,
                 object_identifier, object_type,
                 edge_type)
);

Populating Player-Game Relationships

Inserting Player-Game Edges

The intermediate-bootcamp/materials/1-dimensional-data-modeling/lecture-lab/player_game_edges.sql script materializes participation records as plays_in edges. It first deduplicates source data using row_number() OVER (PARTITION BY player_id, game_id) to ensure idempotent inserts, then stores game-specific metrics—such as start position, points scored, and team affiliation—within the JSON properties column.

INSERT INTO edges
WITH deduped AS (
    SELECT *, row_number() OVER (PARTITION BY player_id, game_id) AS row_num
    FROM game_details
)
SELECT
    player_id            AS subject_identifier,
    'player'::vertex_type AS subject_type,
    game_id              AS object_identifier,
    'game'::vertex_type   AS object_type,
    'plays_in'::edge_type AS edge_type,
    json_build_object(
        'start_position', start_position,
        'pts',            pts,
        'team_id',        team_id,
        'team_abbreviation', team_abbreviation
    ) AS properties
FROM deduped
WHERE row_num = 1;

Deriving Player-Player Connections

Once player-game edges exist, the intermediate-bootcamp/materials/1-dimensional-data-modeling/lecture-lab/player_player_edges.sql script computes implicit relationships through self-joins. By joining the deduplicated game details on game_id while enforcing player_name <> player_name and f1.player_id > f2.player_id to eliminate mirror duplicates, the query generates both plays_against and shares_team edges. A CASE statement distinguishes relationship types based on team affiliation, aggregating statistics such as games played and cumulative points across matchups.

WITH deduped AS (
    SELECT *, row_number() OVER (PARTITION BY player_id, game_id) AS row_num
    FROM game_details
), filtered AS (
    SELECT * FROM deduped WHERE row_num = 1
), aggregated AS (
    SELECT
        f1.player_id   AS player_a,
        f1.player_name AS name_a,
        f2.player_id   AS player_b,
        f2.player_name AS name_b,
        CASE
            WHEN f1.team_abbreviation = f2.team_abbreviation
                THEN 'shares_team'::edge_type
            ELSE 'plays_against'::edge_type
        END AS edge_type,
        COUNT(*) AS num_games,
        SUM(f1.pts) AS left_points,
        SUM(f2.pts) AS right_points
    FROM filtered f1
    JOIN filtered f2
      ON f1.game_id = f2.game_id
     AND f1.player_name <> f2.player_name
    WHERE f1.player_id > f2.player_id
    GROUP BY
        f1.player_id, f1.player_name,
        f2.player_id, f2.player_name,
        CASE
            WHEN f1.team_abbreviation = f2.team_abbreviation
                THEN 'shares_team'::edge_type
            ELSE 'plays_against'::edge_type
        END
)
INSERT INTO edges
SELECT
    player_a      AS subject_identifier,
    'player'::vertex_type AS subject_type,
    player_b      AS object_identifier,
    'player'::vertex_type AS object_type,
    edge_type,
    json_build_object(
        'num_games',    num_games,
        'left_points',  left_points,
        'right_points', right_points
    ) AS properties
FROM aggregated;

Querying the Graph Model

With the graph materialized, analytical questions become straightforward traversals. To identify all opponents a specific player has faced, filter the edges table for edge_type = 'plays_against' and join to vertices for human-readable names. Advanced use cases leverage recursive CTEs to trace multi-hop relationships, such as finding all teammates-of-teammates or mapping six-degrees-of-separation paths between players.

SELECT e.object_identifier AS opponent_id,
       v.properties->>'name' AS opponent_name,
       e.properties->>'num_games' AS games_played
FROM edges e
JOIN vertices v
  ON v.identifier = e.object_identifier
 AND v.type = 'player'
WHERE e.subject_identifier = '123'
  AND e.edge_type = 'plays_against';

Summary

• Separate static entities from dynamic relationships using distinct vertices and edges tables to maintain schema flexibility when adding new entity types like tournaments or coaches.
• Enforce data integrity through PostgreSQL ENUM types for vertex and edge classifications, preventing invalid relationship categories at the database level.
• Store variable attributes in JSON properties to capture evolving metrics (points, positions, team affiliations) without requiring ALTER TABLE operations.
• Derive implicit edges via self-joins on game participation data, using row_number() deduplication and inequality joins to prevent duplicate relationships.
• Optimize traversals using composite primary keys on (subject_identifier, subject_type, object_identifier, object_type, edge_type) for fast graph lookups.

Frequently Asked Questions

Why use a graph model instead of traditional relational tables for player-game data?

Traditional relational schemas require complex many-to-many join tables that become rigid when adding new relationship types. The graph model stores all relationships uniformly in the edges table, allowing you to introduce new interaction types (such as wins_against or coaches) by simply inserting rows or extending the edge_type ENUM, without restructuring existing tables or rewriting application logic.

How do you prevent duplicate edges when multiple players participate in the same game?

The insertion scripts in player_game_edges.sql and player_player_edges.sql use row_number() OVER (PARTITION BY player_id, game_id) to deduplicate source records before insertion. For player-player relationships, the additional predicate f1.player_id > f2.player_id ensures only one directional edge exists between any pair, eliminating mirror duplicates while preserving undirected relationship semantics.

Can this schema handle additional entity types like tournaments or coaches?

Yes, the schema is designed for extensibility. You can add tournament or coach values to the vertex_type ENUM and create corresponding entries in the vertices table. New relationship types such as wins_against or coaches require only additions to the edge_type ENUM, making the model adaptable to evolving analytical requirements without breaking existing queries.

What performance considerations apply when querying large graph tables?

The composite primary key on (subject_identifier, subject_type, object_identifier, object_type, edge_type) provides intrinsic indexing for exact-match lookups on specific relationship types. For traversal-heavy workloads, create additional B-tree indexes on object_identifier and object_type to accelerate reverse-direction queries (finding all subjects pointing to a specific object), and consider GIN indexes on the JSON properties column if filtering frequently on nested attributes like team abbreviations or season years.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}