Symfony2 (WebsocketBundle) - Simple private (and group) chat database schema and logic

Question

I would like to implement a very simple chat in a website, with private messages between 2 registered and logged-in users (and possibly even group messages between logged-in users).
Take Whatsup as an example, but of course I won't have that many users.

Resources: Symfony2 + WebsocketBundle
https://github.com/GeniusesOfSymfony/WebSocketBundle

Two questions:
1- What database schema would you suggest?
2- How would I manage the "topics"? (After reading WebsocketBundle tutorial, I understand how to subscribe/unsubscribe/broadcast to a topic. But I don't know how to manage the relationship between users and topics, how to protect the conversations etc etc...
I just need some information (no code) about the logic of the application... what information to save and where.)

For example, how do I manage the name of the channel (will it change everytime, or do I store it in the database and somehow re-use it the next time the user logs in)?

session.subscribe("acme/channel/id/12345", function(uri, payload){
    console.log("Received message", payload.msg);
});

P.s. I've already tried searching on SO and Google but couldn't find any useful information.

Answer 1

WebsocketBundle provides you just a mechanism to write less for communication through the socket. For chat/group chat setup you have number of options. The first option can be to use some messaging bundle and modifying that according to the needs. I found this bundle (https://github.com/FriendsOfSymfony/FOSMessageBundle) very flexible.

But if you want to develop your own here is the schema inspired from the schema of FOSMessageBundle with some customization. Attaching a schema with tables and it's column names. One can easily identify primary and foreign keys from it.

The rest of things are pretty straight forward other than one field in Thread table which is thread_group_hash. Actually the purpose of this field is unique hash/key for each thread. Where thread means one to one chat or many to many chat. Whichever is the case whenever someone adds people to the chat the users have associated IDs with them. I sort the added user's IDs in ascending or descending order and generate md5/sha1 key and store it here.

This can also be used as topic in WebSocketBundle because that is using the topic for the same purpose which we are trying to achieve here. Whenever same group of people are added to the conversation from either of the party, the communication will go to the same thread. But remember before inserting the thread/message details you need to identify using this hash that where it belongs to or it's a new thread :)