@
@
@Twitt2018-03-15 12:50:03
Database
@Twitt, 2018-03-15 12:50:03

The correct structure of the database for storing messages, and where is it best to store them?

What is the best way to store user messages? There are a couple of formats in my head:
1 format:
Table dialogs
id | from_user_id | to_user_id
table messages
id | dialog_id | date
Table content_message:
id | message_id | message_content
table attachment
id | message_id | path_to_file
Format 2:
Message table:
id | from_user_id | to_user_id | date | message | attachment
What is correct? As a result, a normal structure is immediately needed so that when the project grows, it does not have to be rewritten into the correct format.
Also, the question is, at what point (how many messages should there be, load) to think about such concepts as sharding? And do you always need to keep it in the database, maybe then you need to transfer it to full-text pomsk systems like elastic, solra, sphinx? Where do large social networks like fb, vk, ok keep drugs?
Thanks

Answer the question

In order to leave comments, you need to log in

1 answer(s)
T
ThunderCat, 2018-03-15
@ThunderCat

format 2 is suitable, the message is 1 entity, no need to break it into subentities, this will speed up the work with the table and slightly simplify migration and replication. Perhaps the attachment should be placed in a separate table, since it is not a fact that tomorrow there will be no more 1. There is not enough status read and the status has been deleted from some user.
In terms of quantity - the table is considered large from several million records, the sampling rate depends on a bunch of parameters, both iron and the structure of indexes / data.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question