How to store messages with multiple recipients in PostgreSQL?

I am coding my first web app and I use Postgres to store data.

I would like to implement the following feature: messages with multiple recipients. Optimally, it would great to have threading support as well. Basically, I want something like email with threads, but within my own app.

I am new to RDBMS, so I haven't figured the best way to organize the tables. How do I store such many-to-many relationship?

Solution

You need:

a table for the users of your app, with the usual columns (unique id, name, etc.),
a table for messages, with also a unique id, and a column to indicate which message it replies to; this will let you build threading
a third table which constitutes the many to many relationship, with a foreign key on the user table and a foreign key on the message table,

Getting all the recipients for a given message, or all the messages for a given recipient is just doing a couple of inner joins between all three tables and the proper where clause.

For threading, you will need a recursive common table expression, which let you follow up the links between rows in the message table.

Something like:

WITH RECURSIVE threads AS (
    SELECT id, parent_id, id AS root_id, body
    FROM messages
    WHERE parent_id IS NULL
    UNION ALL
    SELECT msg.id AS id , msg.parent_id AS parent_id, msgp.root_id AS root_id, msg.body AS body
    FROM messages AS msg
    INNER JOIN threads AS msgp
    ON (msg.parent_id = msgp.id)
)
SELECT *
FROM threads
WHERE root_id = :root;

Where the column root_id contains the row id at the origin of the thread of the current row, will let you select a single thread whose root_id is set by the parameter :root.

With multiple recipients, you need to do the inner joins on threads:

WITH ...
)
SELECT *
FROM threads
INNER JOIN threads_users tu
ON threads.id = tu.msg_id
INNER JOIN users
ON users.id = tu.user_id
WHERE root_id=:root