8 replies

Many-to-many relationship in SQL as table or JSONB array?

Hi! I'm new to SQL and I'm wondering which way to model data makes more sense. I want queries to be performant, yet I don't want to over-engineer.

My database is PostgreSQL in Supabase.

I'm modeling players and games where each player can be in many games and each game can have many players — so a many-to-many relationship. I've enabled Row Level Security so a player only has access to the games that they participate in.

Currently I have it set up so that the players-games relationship is stored in a table

players_games

players_games

. Something like this:

create table players (
  id uuid primary key
);

create table games (
  id uuid primary key
);

create table players_games (
  id uuid primary key,
  player uuid references players,
  game uuid references games,

  unique(player, game)
);

create table players (
  id uuid primary key
);

create table games (
  id uuid primary key
);

create table players_games (
  id uuid primary key,
  player uuid references players,
  game uuid references games,

  unique(player, game)
);

The security policy uses the following check:

exists(
  select 1
    from players_games
    where player = player_id
      and game = game_id
);

exists(
  select 1
    from players_games
    where player = player_id
      and game = game_id
);

At the time when I implemented this I didn't know about JSON functions and operators in PostgreSQL. See https://www.postgresql.org/docs/current/functions-json.html and https://supabase.com/docs/guides/database/json#query-the-jsonb-data.

So the same could be implemented like this as well:

create table players (
  id uuid primary key
);

create table games (
  id uuid primary key,
  players jsonb
);

create table players (
  id uuid primary key
);

create table games (
  id uuid primary key,
  players jsonb
);

games.players

games.players

would be an array of player UUIDs, e.g.

[
  "cf7...9f5",
  "de0...91d",
  ...
]

[
  "cf7...9f5",
  "de0...91d",
  ...
]

The check would then be implemented like this:

exists(
  select 1
    from games
    where id = game_id
      and players @> player_id
);

exists(
  select 1
    from games
    where id = game_id
      and players @> player_id
);

Which approach would you suggest as a more experienced SQL developer? What are the pros and cons for each approach and the things worth considering?

PostgreSQL Documentation

9.16. JSON Functions and Operators

9.16. JSON Functions and Operators 9.16.1. Processing and Creating JSON Data 9.16.2. The SQL/JSON Path Language This section describes: functions and …