I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie. The process is that 2 users join a room and do some stuff like chatting (calculated an avergate of 50-80 messages per room) and after all users disconnect from room, remove it.
I have done it this way:
When user creates a room, I'm creating a table with
roomName and users start doing things and when all disconnect, remove the table.
After a chat with a friend, he told me that is ugly to have a database for each room and it will become very slow when many users are connected in the same time and that I should use a single table and indexes to make it faster and salable and that I also must keep a history of conversations to be able to offer to let's say police if there are asked or stuff like that.
The idea seems ok but don't know if it worth modifying code and what I've done until now. Each room user also must make a request each 2-3-4 seconds and ask for new messages(based on a timestamp sent by the user) and I don't know what to do.
My question is: Is it better to have a single table to manage all the rooms or it's okay to make a table for each room and after disconnecting to move in a single history table?