How to build a table for a private messaging system that supports replies?

Question

I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply to and to what was that a reply to and so on and so on.

I'm trying to find a right database structure that would avoid redundancy, so I'm wondering how can I link a single message to all other messages that it is replying to?

I thought about basically having a field called reply_id which would be a serialized array holding the IDs of all messages to be presented as replies

Does anyone have a suggestion on how to do this efficiently? Is my thought a good practice?

Answer 1

Hierarchic Self-Referential Data

Syntax shown using the free and open source database, PostgreSQL

You need a self-referential table, which creates hierarchy in the database. This is how it looks.

CREATE TABLE messages (
  ts_entered  timestamp DEFAULT now(),
  reply_to    int       REFERENCES messages,
  id          int       PRIMARY KEY
                        GENERATED BY DEFAULT AS IDENTITY,
  content     text,
  CHECK (reply_to <> id)
);
CREATE INDEX ON messages (reply_to, id);

Notice that reply_id reference an id on the very table: we can make it exclude the same id with a CHECK constraint. You can also write a CHECK constraint to be sure reply_to is less than id this will stop circular structures from emerging in the database.

Then we enter test data like this.

INSERT INTO messages ( reply_to, id, content )
VALUES
  ( null, 1, 'SYN'),
  ( 1,    2, 'SYN-ACK'),
  ( 2,    3, 'ACK'),
  ( null, 4, 'We should give free advertising to PLAN EXPLORER'),
  ( 4,    5, 'Admins should disclose their affiliations with products'),
  ( 5,    6, 'BANNNN'),
  ( 4,    7, 'Plan Explorer only works on one database'),
  ( 7,    8, 'BANNNN'),
  ( 4,    9, 'Plan Explorer does not support Linux'),
  ( 9,   10, 'BANNNN'),
  ( 4,   11, 'Plan Explorer only does what decent databases already do'),
  ( 11,  12, 'BANNNN')
; 

Now in order to query this, we need a RECURSIVE CTE.

WITH RECURSIVE t(reply_to, id, content, root, level)
AS (
  SELECT reply_to, id, content, ARRAY[id], 0
  FROM messages
  WHERE reply_to IS NULL
  UNION ALL
    SELECT messages.reply_to, messages.id, messages.content, root + ARRAY[messages.id], t.level+1
    FROM t
    JOIN messages
      ON (messages.reply_to = t.id)
)
SELECT * FROM t
ORDER BY root;

And you're done..

 reply_to | id |                         content                          |   root    | level 
----------+----+----------------------------------------------------------+-----------+-------
          |  1 | SYN                                                      | {1}       |     0
        1 |  2 | SYN-ACK                                                  | {1,2}     |     1
        2 |  3 | ACK                                                      | {1,2,3}   |     2
          |  4 | We should give free advertising to PLAN EXPLORER         | {4}       |     0
        4 |  5 | Admins should disclose their affiliations with products  | {4,5}     |     1
        5 |  6 | BANNNN                                                   | {4,5,6}   |     2
        4 |  7 | Plan Explorer only works on one database                 | {4,7}     |     1
        7 |  8 | BANNNN                                                   | {4,7,8}   |     2
        4 |  9 | Plan Explorer does not support Linux                     | {4,9}     |     1
        9 | 10 | BANNNN                                                   | {4,9,10}  |     2
        4 | 11 | Plan Explorer only does what decent databases already do | {4,11}    |     1
       11 | 12 | BANNNN                                                   | {4,11,12} |     2
(12 rows)

Answer 2

I would suggest against using a serialized array, but instead encourage you to build the functionality into your database so that it can be more easily expanded and managed.

Given that you're considering implementing a serialized array, I assume that messages can be a reply to zero or more other messages.

In this case, a linking table for replies would be a great solution:

In addition to this being a more database friendly design, it makes inserting or deleting replies much simpler than it would be in another case. Writing queries from this solution would also be much, much easier than in the serialized array design.

For example, if I want to find all of the replies to a message, it's very simple:

SELECT ReplyingMessageID
FROM Reply r
WHERE ReplyTargetMessageID = <OurMessageID>

And getting the text of all messages to which a message with a particular text has replied is relatively simple as well:

SELECT m1.MessageInfo
FROM Message m1
INNER JOIN Reply r
    ON r.ReplyTargetMessageID = m1.MessageID
INNER JOIN Message m2
    ON m2.MessageID = r.ReplyingMessageID
    AND m2.MessageInfo = 'This is the text of the message that replied.'

Getting either of these datasets using the serialized array would be more problematic from a database perspective.

Answer 3

I think the answer depends upon how your app is intended to work.

Do you envisage users being able to reply to many messages? Or only to a single message?

Personally id keep all messages in a single table with a self join. (Like a employee heirachy where each employee has a manager). To speed up searching and selection you could keep all related replies together with a "conversation id".