How to structure table in MySQL for group chat application? I'm making a messaging application, and I need a way to organize and store messages. I was thinking something like a table that had all the group chat IDs and who was in which group chat, but MySQL doesn't have a list functionality. For the actual messages, I was thinking one large table with the group chat IDs and the message ID. To be honest, I really don't have a good way to approach this. I was even considering making a table for every group chat. How should I structure a database for a group messaging application?
With relational databases, or mostly-relational databases as is the case with MySQL, you should think mostly in terms of entities and relationships. 

You might want one table for each of the following entities: 


messages: the actual messages and a reference to the user (author)
users: details such as user name, email address, ...
groups: maybe just the name of the group 
group memberships: one row per user per group. This allows for a many-to-many relationship, so that many users can be in many groups at the same time.  


So then you could have tables maybe like such:

CREATE TABLE users (
  user_id bigint unsigned auto_increment PRIMARY KEY,
  username varchar(60) NOT NULL UNIQUE
  -- other user columns here
);

CREATE TABLE groups (
  group_id bigint unsigned auto_increment PRIMARY KEY,
  groupname varchar(60) NOT NULL UNIQUE
  -- other group columns here
);

CREATE TABLE group_users (
  group_id bigint unsigned,
  user_id bigint unsigned,
  PRIMARY KEY (group_id, user_id),
  CONSTRAINT group_users_fk1 FOREIGN KEY (group_id) REFERENCES groups (group_id),
  CONSTRAINT group_users_fk2 FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE messages (
  message_id bigint unsigned auto_increment PRIMARY KEY,
  user_id bigint unsigned,
  message TEXT,
  -- other columns here, e.g. timestamp
  CONSTRAINT messages_fk1 FOREIGN KEY (user_id) REFERENCES users (user_id)    
);  


Edit

The lines starting with CONSTRAINT specify foreign keys which enforce integrity of data between the tables. E.g. they will prevent rows from being deleted in the users table that are referenced in the messages table.

To retrieve a list of users in a particular group, use a query like this:

SELECT u.user_id, u.username
FROM users u
  INNER JOIN group_users gu USING (user_id)
WHERE gu.group_id = 3;


The INNER JOIN means the query will only retrieve the users who have at least one row in the group_users table, and the WHERE ensures that we only retrieve results for group_id 3.