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?