title: Database schema for chat: private and group
body: <p>I'm trying to design the database schema with the ability to both private chat and group chat.  Here's what I've got so far:</p>
  
  <p><a href="https://i.stack.imgur.com/qnLFa.png" rel="noreferrer"><img src="https://i.stack.imgur.com/qnLFa.png" alt="enter image description here"></a></p>
  
  <p>So - the theory is that even if the user is just in a one on one private chat, they are still assigned a 'roomID', and each message they send is to that <code>room</code>.<br>
  To find out all the rooms they are involved in, I can SELECT a list from the table <code>participants</code> to find out.</p>
  
  <p>This is okay, However it feels to me that the <code>room</code> table is slightly redundant, in that I don't really need a room name, and I could leave it out and simply use the <code>participants</code> table and <code>SELECT DISTINCT roomID FROM particpants</code> to find out the individual rooms.</p>
  
  <p>Can anyone explain to me a better structure or why I should keep the room table at all?</p>