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?

  • 1
    You could use a graph database for this. That technology's all about holding links between entities. Apr 16, 2015 at 3:01
  • Would you allow multiple replies? Do you consider having conversation or thread concept? Have you considered a conversation with messages ordered by time?
    – Stoleg
    Nov 13, 2015 at 14:32

3 Answers 3


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 )
  ( 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
    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)
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)
  • How would you design to include a "read" for each chat participant? I am assuming a message would has_many reads?
    – Strawberry
    Jun 11, 2018 at 10:28
  • Does this type of query imply that your UI would not show the most recent? Because the replies are grouped, you could have a most recent message in reply to a really old message.
    – Strawberry
    Jun 11, 2018 at 10:51
  • @Strawberry wouldn't that still make it a "most recent message" regardless if you want to show the most recent "thread" you need only order by whatever a thread is, so for example depth=0. Yes. Because anyone can read a message you'd have 1-to-many with a read table that linked the messages to the users which read/viewed them. Jun 12, 2018 at 7:52
  • You have reply_to int REFERENCES messages but no FOREIGN KEY or field definitions - how come? How would you implement the EXCLUDE constraint you mention? I'm a firm believer in doing as much work as possible in DDL.
    – Vérace
    Oct 31, 2021 at 8:08
  • @Vérace-getVACCINATEDNOW PostgreSQL creates a foreign key with REFERENCES messages (it'll link that column to the primary key of the table). I believe the EXCLUDE Is a typo, I can fix that. Oct 31, 2021 at 8:20

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:

enter image description here

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

  • You absolutely do not need a many-to-many table. It's the wrong way to model this, as in using such a table a message can become a reply to multiple messages. That's usually not what you want. It doesn't make sense to use an intermediary table to model a 1:1 relationship: each message has at most one parent. And, this solution only provides a finite level of responses. Mar 19, 2017 at 21:16
  • I see where you're coming from and I agree that in general you wouldn't want to use an intermediate table, but in this case OP implies that one message can be in reply to several others, as : how can I link a single message to all other messages that it is replying to?. Mar 20, 2017 at 17:36

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.