title: Database schema for a live chat project with rooms
body: <p>For my university project, I'm developing a dynamic live chat website with rooms, user registration, etc. I've got the entire system planned out bar one aspect. The rooms. I'm confused as to how to design the database for rooms.</p>
  
  <p>To put it in perspective, a room is created by a user who is then an operator of that room. Users can join the room and talk within it. The system has to be scalable, accounting for hundreds of thousands if not millions of messages being sent a day.</p>
  
  <p>Originally, I was going to create on table in my database called <code>messages</code>, and have fields like this:</p>
  
  <p><code>|    r_id    |    u_id    |    message    |    timestamp    |</code></p>
  
  <p><code>r_id</code> and <code>u_id</code> would be foreign keys to the room ID and user ID respectively. Doing it this way means I would need to insert a new record whenever a user sends a message, and periodically run a SELECT statement for every client (say every 3 seconds or so) to get the recent messages. My worry with this is because the table will be huge, running these statements might create a lot of overhead and take a long time.</p>
  
  <p>The other way I thought of implementing this would be to create a new database table for every room. Say a user creates 3 rooms called <code>General</code>, <code>Programming</code> and <code>Gaming</code>, the database tables would look like: <code>room_general</code>, <code>room_programming</code>, <code>room_gaming</code>, each with fields like:</p>
  
  <p><code>|    u_id    |    message    |    timestamp    |</code></p>
  
  <p>This would drastically cut down on the amount of queries for each table, but may introduce problems when I come to program it.</p>
  
  <p>So, I'm stuck on what the best way to do this is. If it makes a difference, the technology I'm using will be MySQL with PHP, and a whole lotta AJAX.</p>
  
  <p>Thanks for any help!</p>