Postgres: How to express Many-tables to Many-tables relationship?

Question

What I have

I have a schema of listeners defined like so:

create table listeners.ethereum (
  id serial primary key not null
);

create table listeners.solana (
  id serial primary key not null
);

I also have a schema of responders defined like so:

create table responders.http (
  id serial primary key not null
);

create table responders.grpc (
  id serial primary key not null
);

What I'm expecting

Each listener will trigger an arbitrary amount of responders but I'm not quite sure how to write this relationship. For example, a listener.ethereum could trigger two responder.https and one responder.grpc. Or a listener.solana could trigger only one responder.grpc.

What I've tried

The only approach I've come up with is using loose types in join tables:

-- join table
create table listeners_responders (
  listener_type string,   -- `ethereum` or `solana`
  listener_id integer,
  responder_type string,  -- `http` or `grpc`
  responder_id integer
);

-- join query
select responders.http.id as http_id, responders.grpc.id as grpc_id
from listeners.ethereum
where id = 1

inner join listeners_responders
  on listeners_responders.type = 'ethereum'
  and listener_responders.id = 1

left join responders.http
  on responders.http.id = listeners_responders
  and listeners_responders.type = 'http'
left join responders.grpc
  on responders.grpc.id = listeners_responders
  and listeners_responders.type = 'grpc';

My question

Using my approach means the listener_id and responder_id are not real foreign keys. It also means I have to add a new inner join query for every new responder I add in the future. Is there a better way to approach this problem where I can have real foreign keys to join many-tables to many-tables?

Answer 1

I'm sorry if I didn't understand correctly your question, but from what I understand, the correct data model is:

create table listeners (
  id serial primary key not null,
  type string not null
);

create table responders (
  id serial primary key not null,
  type string not null
);

-- join table
create table listeners_responders (
  listener_id integer,
  responder_id integer
);


-- join query
select responders.id,
  responders.type
from listeners
inner join listeners_responders
  on listener_responders.id = 1
left join responders 
  on responders.id = listeners_responders
where listeners.id = 1;

If you really want 2 columns instead of 2 rows, you can use a case statement.