Storing all conversations in a table

Question

I'm trying to build something like a message board, just for learning PHP and MySql. I've been watching some tutorials about conversation systems and in one of them the guy was storing ALL of the conversations in the same table. All the messages stored together. Is that a good idea? Wouldn't it get slow with the site growing? I was thinking of storing the conversations in files and then having a table to keep track of those files.

(I see a couple of similar questions were already asked but I can't find exactly what I need)

Answer 1

The proper way of doing this task is indeed having all your conversations in the same "tables" (normalised set) but after they are older than a few days (hours,minutes depending on your preferences or needs) they are moved into archived tables (normalised set), which will make sure performance never gets too slow. When retrieving messages if ones from the archived table are needed then a view or union of the two tables (or more if this scenario is using normalisation) is used.

Answer 2

Storing each message in a new table will not scale well, there is a limit to the number of tables you can have. Each table also requires 2 filehandles which can consume a significant amount of memory. If you have a many messages to one conversations to one topic you might break up the data to a table per topic, or possible consider a consistent hash ring with a certain number of tables. You can also partition the messages on hash as well, giving you further capacity.

Answer 3

Your question is a little vague, but in general, I strongly recommend you worry about performance and scalability when you can prove you have a problem.

All other things being equal, modern databases on modern hardware can store hundreds of millions of records without noticable performance problems.

And, again, in general terms, the thing that slows down a database is not the size of the record, but the access query. So if you're building "a table to keep track of files", you are likely to have the same access problems. So the expensive part is likely to be "find all conversations in descending date order, with the number of participants and the date of the last entry". Grabbing the actual conversation threads should be pretty quick.

My strong recommendation is to use the denormalized, relational model until you can prove you have a performance or scalability problem; then buy a bigger server. Then consider denormalizing. Once you've done that, you're probably at the size of Facebook.