Mysql – What relationship type to use for this messaging system

database-designMySQLmysql-workbench

I am trying to make a small messaging system. It should have these properties:

One user can send a message to one or many users
Messages can be sent only to registered users (i.e. to an existing 'idUser' record)
There should be tracking of sent messages (who sent the message and to whom)

Here is how the current database design looks like:

enter image description here

Table Private Message stores only the content of the message.

Table User Private Message is used to keep track of sent messages. It has columns "idUser from" and "idUser to" which indicate the user that sent the message and the user (can be more than one) that received the message.

I am having difficulties with choosing the right type of relationship (if the whole concept is correct in the first place) for the tables "User" and "User Private Message".
I assume that there should be two separate relationships for the tables 'User' and 'User Private Message', like this:

One-to-one relationship, that connects User.idUser and User Private Message.idUser from
One-to-many relationship, that connects User.idUser and User Private Message.idUser to

Could you please advise me on what relationship to use here. If you have any suggestions on how to approach this type of problem easier, I would appreciate that even more.

Thank you all for your time.

Best Answer

Assuming you're not trying to keep track of threads of messages, I.E. you send a message to N people, and they can't reply:

1) user needs no references to other tables
2) a message should have an ID (obviously) and a reference to the user who sent it originally
3) you need a table that connects a single message and its recipients (users) that looks like this: message_recipients{user_id, message_id} where the primary key is the combination of the two fields. This ensures that one message can have many recipients, and that users can be the recipients of many different messages.

One user can send a message to one or many users

This is accomplished via the message_recipients table

Messages can be sent only to registered users (i.e. to an existing 'idUser' record)

You'll need to handle this in your application logic as well, but if you add (as you should) a foreign key and (depending on your DBMS) a NOT NULL restriction on each of the columns in message_recipients table, it will be possible to create a record in message_recipients without the recipient existing.

There should be tracking of sent messages (who sent the message and to whom)

This data model supports that. Example:

Find the name of all recipients of messages sent by user Mike Johnson

(edited to give better aliases to the two different uses of the user table)

SELECT recipient.firstname, recipient.lastname
FROM user recipient
JOIN message_recipient mr ON recipient.id = mr.user_id
JOIN message m ON m.id = mr.message_id
JOIN user sender ON m.user_id = sender.id
WHERE sender.firstname = 'Mike' and sender.lastname = 'Johnson'

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)

Google Datastore model User and Messages efficently

Your second approach seem to be the best. Having a Message entity with key relations to sender/reciever.

As for you deleting a user dilema, you should consider not removing the entity when a user is deleted as this would break your db consistency. You should probably just have a logic deletion (eg a boolean value changed when a user is deleted), that way, messages sent to/from that user will stll make sense for other users.

Best Answer

Find the name of all recipients of messages sent by user Mike Johnson

Related Solutions

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

Hierarchic Self-Referential Data

Google Datastore model User and Messages efficently