Need advice on best way to store chats...

[The14thGOD]

I have a rather large project I'm investigating and starting to put together the database. I'm looking for the best way to store chat logs.

I have chat systems I'm looking to store. Possibly a third but that one will probably need a more unique solution and is in phase two. The first is much like a chat room, 3-8 people at a time. The other will be only two people like Google Chat.

 

I started looking at SQLite but that looks like an alternative thing to MySQL? I have enough to learn so I'd rather not add another thing to this project. I want to use MySQL. I'm not sure how to do the chat thing yet either (once I know how I'm storing the chats I will have more of a direction on this). This project has the potential for a very large userbase so I need something fast. At first I was thinking about doing flat files but that seems like its going to take a toll on the drive.

 

But I'm not sure of a way to do it via database that would be fast. I'm not exactly an expert on database/server performance so any help would be greatly appreciated. If you need any clarification on some things let me know and if I can answer I will.

 

Thanks,

Justin

[mens]

As you said, it may get large. MySQL wouldn't be your answer then, unless you clear logs on a daily basis.

I'd suggest using files for the chat logs, and dividing them by hour, day or week depending on the size of them.

 

The is, by using MySQL for this, you would need to select through thousands of chat message entries, which would make it very slow indeed.

With files, you can specify the size of the files, and instantly create a new file for chat messages as needed. This wouldn't affect performance as much as a MySQL database design would.

 

[The14thGOD]

Would you recommend just a txt file xml or does it not really matter in that case? The txt file would probably be smallest, but harder to present/stylize. txt file I would have to come up with some system for new lines etc. I'm not sure if there is a standard out there.

Is there a better option out there than fopen() etc? Just seems like that may get ugly if lots of people are accessing different files at once on the server.

[Zane]

The best way I can think of..... is to have a chats table.

 

 

One:  The Chat Table (of course)

consisting of:

- id
- message
- fromUser
- toUser
- time

In which, fromUser and toUser are just foreign keys of the table Users.

 

and from that just store each message....

[mens]

You can use CSV files, or XML - your choice.

 

Unfortunately for you, fopen() is your best bet when working with file manipulation techniques.

There's also file() and file_get_contents() though.

[chaseman]

Just out of curiosity, how are other sites doing it? Sites like Facebook & co?

[mens]

Just out of curiosity, how are other sites doing it? Sites like Facebook & co?

 

I found this a while back: http://technophiliac.wordpress.com/2008/05/28/ever-wondered-how-gmailfacebook-chat-works/

It may offer an explanation to you, as I also don't know exactly how it works.

 

Edit: look at this as well.

[The14thGOD]

thanks for those links!