# How to Design Graph-Based Data Models for Player-Game Relationships: A SQL Implementation Guide > Design flexible graph-based data models for player-game relationships with SQL. Implement property graphs for efficient player analysis, career tracking, and co-play statistics. - Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook) - Tags: how-to-guide - Published: 2026-08-12 --- **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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`. ```sql 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. ```sql 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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. ```sql 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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. ```sql 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. ```sql 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/player_game_edges.sql) and [`player_player_edges.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.