PHP-MySQL data storage for messenger program

[TheServant]

Hi guys,
Hope this finds you all well. I have been away (getting married and taking a break from everything) but I am back now and I have need of some more experienced php guru's:

I am trying to make an in-game chat system which will create a new chatroom when a user creates a group. I have a couple options and I want to know which is best in your opinion:

1. Create a single table for all chatrooms and include 100 columns for messages including user/time sent/message. Problems is this limits messages to 100, but also means that it is controlled and data will not get too big.
2. Create a single table for all chatrooms and simply have a text variable which will include user/time sent/message separated by a character which can be parsed to separate them and display correctly. This means relatively unlimited messages, but deleting specific messages could be a little more difficult.
3. Create a table for each chat room with user/time sent/message columns. This is the best option, but I don't really want lots of tables.
4. Create a single table for all chatrooms with group/user/time sent/message columns and then when each chatroom is called, search through the whole table to find all rows belonging to that chatroom.

If the game was successful I could be looking at over 1000 groups with maybe 10 messages per hour per group. So it could get very big. Any suggestions would be great.

[pbmods]

Heya, TheServant.

So you have a number of groups, and each group has its own chat[room], which contains a number of messages.

If you don't plan on ever allowing any group to have more than one chat[room], then you can simply tag each chat message with the group for whom it was intended.

You'd end up with something like this:

CREATE TABLE `groups`(
  `groupid` bigint(20) unsigned not null auto_increment,
  /* extra group details */
  PRIMARY KEY(`groupid`)
);
 
CREATE TABLE `chatmessages`(
  `messageid` bigint(20) unsigned not null auto_increment,
  `groupid` bigint(20) unsigned not null,
  /* extra chat message details */
  PRIMARY KEY(`messageid`)
);
 

[TheServant]

That's what I was thinking. Seeing as you picked the one I was leaning towards I will start there and see if I have any problems. Thanks for your help.

Regards,
The Servant