How can I organize a comment system in MySQL
An idea would be:
id_post
id_comentario
id_user
comentario
But it would take up a lot of space and it would not be so quick to process 2000 comments from id_post different
I also thought about using array , example:
id_post
comentário
In the comment column you would find:
user:5,comentário:teste;user:6:comentário:teste;
Only you can tell what is the best fit for your case.
The first form can be very interesting and many people think not. Ask a few questions:
Do you need to access the comment in isolation or will it always be accessed through the post ? If it is an aggregate direct association and does not have an identity of its own, normalization is not usually necessary. Anyone who works with NoSQL, which should actually be called NoRel , does this a lot and feels great (in many cases it's even though), what many do not realize is that the RDBMSes relational model is optional.
Will you usually read all the comments together? If the previous answer is yes and here it is also yes, then there is a great chance of being the best option. Are you going to read everything together because you need to separate?
2000 comments in one post? If this has something humanly unmanageable, if it has dozens of comments there must be something wrong. Facebook does not usually have this and when it's the same thing that has no comments, nobody will read. This site was born from the observation that other sites could not manage these things and end up not serving the purpose.
2000 comments to the whole system? This does not "tickle".
I would probably do the second option, just create some pattern to reduce the cost of space:
You can create a character that indicates the breakdown of what the user is and what the comment is. You can not actually use ; unless you escape that character in the text before writing, and you'll have to deal with it. The separation of the user does not matter so much, it can be the comma because it only has numbers, right? Needless to say that comes a comment then after the comma after the user's number is always a comment that will end where that terminator character has. If you do the way you are thinking and someone comments "; user" will melody your data, there will be text injection .
65,teste;66,teste com \; escapado;
You can use a number, maybe in binary form (4 bytes should suffice), at the beginning indicating the size of this whole entry, so you do not need a terminator character. It will take up a bit more space, but it is simpler and more secure. I could do the user number in binary form as well. If you use the binary form you have to be careful if you use it on different platforms. If you use as text you need to have fixed size, which will take up more space, or have a tab, especially if the comment text starts with number.
000♣000♣teste000¶000♠teste com ; escapado
Unintentionally using "NoSQL" the right way , productive and where it's useful.