Is better to create a table for each room or make a single table that handles all the rooms in my case?

Question

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?

Thanks

Answer 1

Here's one approach:

create table users
( username ... not null primary key
, <additional attributes>
) engine = innodb;

create table rooms
( room_name ... not null primary key
, creator ... not null
, <additional attributes>
, constraint ... foreign key (creator) references users (username)
) engine = innodb;

create table conversations
( room_name ... not null
, username ... not null
, <additional attributes>
, constraint ... primary key (room_name, username)
, constraint ... foreign key (room_name) references rooms (room_name)
, constraint ... foreign key (username) references users (username)
) engine = innodb;

Assuming users are already created, a user can create a room. A conversation is a room with one or more users. To find out who is having conversation in a room:

select username from conversations where room_name = ?

Whether this is a good approach or not depends on your requirements. Personally I like to write down facts about my business and extract a logical model from that.

I would definitely stay away from changing the model as a consequence of a user action (example create a table for a new room).

Answer 2

I don't know PHP or MySql but the answer is definitely not creating new tables for each room. I'd have one table for rooms and one for messages and have the messages refer to the rooms (via foreign key).
You probably want to consider archiving and/or purging as the data will probably grow quickly.

Look into MySql's ability to partition tables and if there is an ability to SWITCH (this is possible in Sql Server) data in a partition from one table to another VERY quickly - it's just a meta data change. This could move Gbs in seconds vs having to physically copy and delete data from one table to the other.