3

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. – Michael Green Apr 16 '15 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 '15 at 14:32
6

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
);
CREATE INDEX ON messages (reply_to, id);

Notice that reply_id reference an id on the very table. In a more enterprisey example, we'd probably make it EXCLUDE the same id.

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)
| improve this answer | |
  • How would you design to include a "read" for each chat participant? I am assuming a message would has_many reads? – Strawberry Jun 11 '18 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 '18 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. – Evan Carroll Jun 12 '18 at 7:52
1

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

| improve this answer | |
  • 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. – Evan Carroll Mar 19 '17 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?. – Thomas Cleberg Mar 20 '17 at 17:36
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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