Help design a database for posting messages.

Here's what I got so far: Scheme

  • It should be possible to store messages in a hierarchy, i.e. the user can create folders and move messages there. For storage of hierarchical data selected Nested Set .

Question: How to associate messages with folders to store them. Now I have created the MessageCatalogs table. It turns out something like the following:

 +-------------+----------------------+-----+-----+----------------------+ | id | name | lft | rgt | messageId | +-------------+----------------------+-----+-----+----------------------+ | 1 | Входящие | 1 | 10| NULL | | 2 | NULL | 2 | 3 | 1 | | 3 | NULL | 4 | 5 | 2 | | 4 | Исходящие | 10| 15| NULL | | 5 | NULL | 11| 12| 5 | | 6 | NULL | 13| 14| 8 | | 4 | Тех(внутри Входящ.) | 6 | 9 | NULL | | 5 | NULL | 7 | 8 | 10 |

Maybe you need to combine MessageCatalogs and UserMessage into 1 table?

UPD

  • 1 message can be sent to multiple users at once.
  • Each user has their own set of folders
  • one
    Excess User-Message connection. For storage of hierarchical data selected Nested Set. - why? or rather, why ... would it really be that messages and subfolders will be constantly being dragged by sub-syuds? - Akina
  • one
    Well, in general, some strange connection between UserMessage and MessageCatalog. There is a link to the catalog id, but the link is also implicitly present on the message id - there is a potential mismatch. - Akina
  • one
    If the messages are in folders, then there may be many messages in the folder. If at the same time one message cannot be in more than one folder, then it would be logical to keep the message id of the folder in which it lies (and not vice versa). - Mike
  • one
    Nested Set is a specialized structure for storing hierarchical data and treatments specific to this type of data . And you have such treatments until the emphasis can be seen ... - Akina
  • one
    @ romka.pm Merging into one table makes sense only if the message can lie not only in the folder but also in another message. I think it is not required so it should not be merged - Mike

1 answer 1

There are a few comments:

  1. The message always has a recipient and a sender - this is not visible in your structure
  2. Often, messages are usually stored / processed in the form of thread - the thread of the conversation , that is, messages with one topic are usually grouped together, it would be good to support this in your structure.
  3. It is not clear how you are going to store the message sent to several recipients - or is it as one message or as several messages to different recipients? This is also not visible from your structure.
  4. Hierarchy apparently you are going to make through directories. If so, then it is enough to follow the principles of building file directories - that is, the directory always has the 1st parent (link to the directory ID), if the link is null - then this is the root directory. It’s not enough to bother with left/right links like yours - these are redundant entities.
  5. And where is the body of the message itself?
  • 2
    1. it is perfectly visible, the sender in message, the receiver in userMassage. 3. judging by the above structure, if there are several recipients, then several entries in userMessage. 5. The message body is visible in the text field in message - Mike