title: Need database structure for private, group and public chat - MySQL
body: <p>My basic idea is to build a web-based chat application. I have checked a lot of database structures over the internet for my requirements and the conclusion is that there are so many solutions for that. </p>
  
  <p>So here is the database structure I thought (But I'm pretty sure that it is not 100% correct or at least it can be improved)</p>
  
  <hr>
  
  <p><strong>Table users:</strong></p>
  
  <p>id | username | email | created_at | updated_at </p>
  
  <p><strong>Table chat_rooms</strong></p>
  
  <p>id | room_type | created_at | updated_at</p>
  
  <p><strong>Table: room_members</strong></p>
  
  <p>id | room_id | user_ids (in serialized form)</p>
  
  <p><strong>Table: messages</strong></p>
  
  <p>id | room_id | sender_id | message | created_at | updated_at</p>
  
  <p><strong>Table: receivers</strong></p>
  
  <p>id | message_id | receiver_id | read_at</p>
  
  <hr>
  
  <p><strong>There are 3 types of chats:</strong></p>
  
  <p>i) Private chat - a chat between two users</p>
  
  <ul>
  <li>A user will select another user to start to chat with him/her</li>
  </ul>
  
  <p>ii) Group chat - a chat between group of users</p>
  
  <ul>
  <li>A user will add other users into the room to start a group chat</li>
  </ul>
  
  <p>iii) Public chat - a chat between all the users</p>
  
  <ul>
  <li>An open chat room to which anyone can join and send messages</li>
  </ul>
  
  <p><strong>So here is how it will work:</strong></p>
  
  <p>I logged into the site, redirected to the dashboard page. On that page, I have 3 options to start a chat as described above.</p>
  
  <p><strong>Notes:</strong></p>
  
  <p>1) If there is a previous chat between the users it should be shown as soon as I start a chat (pretty similar to Facebook) - This applies to all the three types of chats</p>
  
  <p>2) I want to have a feature which says that when a specific user read a message.</p>
  
  <p>3) I want to keep it scalable as much as possible for the future enhancement </p>
  
  <p>Thanks</p>