MySQL here. I’m adding a super-simple chat feature to an app and am worried my data model my be a little awry.

Users can send Messages to 1+ other Users. This creates a Conversation, which is really just a container of 1+ Messages. If the Conversation is only between 2 Users, it’s considered (by the app) to be a Direct Message (DM). Otherwise its considered to be a Group Chat.

My tables:

[users] ======= id PRIMARY KEY AUTO_INC INT NOT NULL, username VARCHAR(255) NOT NULL  [conversations] =============== id PRIMARY KEY AUTO_INC INT NOT NULL, created_on DATETIME NOT NULL  [messages] ========== id PRIMARY KEY AUTO_INC INT NOT NULL, conversation_id FOREIGN KEY INT NOT NULL, # on conversations table sender_id FOREIGN KEY INT NOT NULL, # on users table text VARCHAR(2000) NOT NULL, sent_at DATETIME  [users_x_conversations] ======================= id PRIMARY KEY AUTO_INC INT NOT NULL, conversation_id FOREIGN KEY INT NOT NULL, # on conversations table user_id FOREIGN KEY INT NOT NULL, # on users table 

So in my design above, you can see I’m really just using the [conversations] table as a placeholder and as a way of grouping messages to a single conversation_id, and then [users_x_conversations] is crosswalk (many-to-many) table where I’m actually storing who is a “member of” which conversation.

Is this the right approach to take or is there a better way to relate the tables here?