Using Phoenix as a chat service - how to plan DB tables?

I’m using Phoenix to function like pusher.com. I have a PHP app where I need to add chat rooms to it. I’m getting confused on how to relate the users to the messages.
In Phoenix I have the messages table which consists of message_body and user_id. When user adds a message from inside the PHP app I send the message with the user’s ID through the socket and handle it. My problem is with getting back the user’s username to show with the message_body itself.
My PHP app has the user’s table and not Phoenix. Phoenix can only send back the message_body and the user’s ID. This means that to get the username from the user’s ID I have to make an API call to my PHP app to find out. This is very inefficient because what if I get back 500 messages? I don’t want to run 500 API calls just to figure out the username of my user. Now storing the username in the messages table can cause a problem if the user changes his username in the PHP app. However I might make an API call to the phoenix app if that happens and change it to in the messages table.

What am I missing here? How would you plan this? It feels very tricky to work with multiple DB’s.
By the way, I’m only doing this for practice so feel free to share whatever you think.

2 Likes

Just looking for clarification on something there, why would it be 500 API calls and not just 1. Can the PHP app expose an API call to get a user given the user id? Something like HTTP GET /user/:user_id that would return the user?

1 Like

Well, that depends. I was looking at the most tricky case scenario where I grab the last 500 messages which belongs to different users. So I get the 500 messages and I need to loop over them and get the username by the user’s ID and then echo it to the screen with the message_body.
I guess I have to add the username to the messages table as well and just update it if needed. At least that would be only one call.

you don’t have to add it to the table you can just join the user table to get the username. You could also have an end point that resolves ids to usrenames it would run a single query based on PKs so even fetching 500 would be cheap.

How can I join? The user’s table is in the PHP app which is a different DB.
The Phoenix app has only the messages table.

You cannot access the DB directly from the phoenix app?

1 Like

You mean to access to the PHP DB from the Phoenix app? I don’t know. Never had to do such thing. I will check how it might be done. How would that even help me?

Easiest thing then would just be to use that existing database to store your new tables too, then you can join with ease for maximum flexibility and speed.

1 Like

Sounds like a very good idea, thank you.

Another solution would be to store the user ID / username pairings in the client with the in browser database. Then you could just swap it as part of the display logic. That way if you did need to hit a PHP API to get usernames for id’s you’d only need to do it once per chatter.

1 Like