Hello;

I would like to know how the mysql database schema is designed for storing private chat messages between 2 users. Like if i insert the every line of a chat as a new record, database will grow very huge and it may lead to server crash or slow working.

 

I tried to store the full message in a xml file and later when chat is completed i dumpted that file into database. But here i have a problem at maintaining the history of the chat. And this process will really slows the app.

 

Or can i store the messages at php sessions and finally dump into database. Because in some article i read that max storage of php session is 128MB

 

I'm still worried about the Database structure.

 

So whats the best way to store? I'm thinking how Google [Gtalk] will store the chat message? Which is very fast and user friendly.

 

So please suggest me the best way.

 

Thank you..

Hello;

I would like to know how the mysql database schema is designed for storing private chat messages between 2 users. Like if i insert the every line of a chat as a new record, database will grow very huge and it may lead to server crash or slow working.

 

I tried to store the full message in a xml file and later when chat is completed i dumpted that file into database. But here i have a problem at maintaining the history of the chat. And this process will really slows the app.

 

Or can i store the messages at php sessions and finally dump into database. Because in some article i read that max storage of php session is 128MB

 

I'm still worried about the Database structure.

 

So whats the best way to store? I'm thinking how Google [Gtalk] will store the chat message? Which is very fast and user friendly.

 

So please suggest me the best way.

 

Thank you..

 

128MB is alot of chars, its probably like 130million characters or something stupid

 

Either way this data is going to have to be sent via something like mysql therefore using up bandwidth or whatever, so storing it or not its going to use up bandwidth,

 

The best way i can think of to make it efficient and not use up so much space or bandwidth is by the following;

 

- Users sends message

- Message is stored in database and also stored in that users php session as an array of some description

- Other users chat checks the database for new entries every second or so, if theres a new entry it will get that entry only and store it in their session array and then displays on their chat to read and reply whatever

- Instead of it loading all the messages every second, make it so you count entries stored in the array and then as your query SELECT * FROM chatmsgs WHERE id>$TheAmountOfEntriesStoredOnYourArray then simply repeat the last step

- So that you keep your session storage down just when it gets over 400 messages or something then it slowly starts emptying the messages from the array 1 by 1, so every new entry means 1 entry is removed from the array for example

so when there is the 456th message then message number 56 would be getting deleted from the array and database the same time the 456th message was being loaded

 

- If its a public chat (and maybe even private chats) for safety/security reasons if someone reads a message they dont like for example swearing etc then they can click report and that message would be put in another table in the db under reported messages for moderators viewing to deal with that person accordingly

 

Not sure if that makes sense but thats probably the best way to do it without using a ton of bandwidth