Theo's Typesafe Cult•3y ago

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. 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 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 …

JSON | Supabase

Using the JSON data type in PostgreSQL.

6 Replies

Unknown User•3y ago

Message Not Public

stoyko•3y ago

This is what is called normalization of a database. Having the many-to-many relation improves the data integrity and ensures that all games have only players that exist in the DB or records of past players that were added in the db. And all players have records of only existing games, etc. I strongly suggest reading more on normalization and what its tradeoffs are.

DILJAM•3y ago

Yes I will! Do you have any resources (videos, articles) on the topic that you’d especially recommend?

stoyko•3y ago

Not really. Just reading a bunch on db normalization practices. I’ve watched a bunch of random videos

DILJAM•3y ago

So I was considering object columns mainly for the convenience of in the client being able to fetch games (select * from games through PostgREST) and receiving objects where players is an array of player ids. Now if I want to get objects shaped like that in the client, I’d have to built an endpoint which makes many underlying queries.

cje•3y ago

id only use json blobs in db if its absolutely unavoidable cant you just do like

SELECT (player.id, player.name, etc) WHERE player_games.gameid = <id goes here> FROM player_games JOIN players ON player_games.playerid = player.id

Gaming

Programming

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