Database Design – Chat Service Database Design Best Practices

chatdatabase-designrdbmsweb-development

I have an android/desktop application that I'm now releasing on the web and, as there are some social aspects, I thought of adding a chat-service so users can IM as well.
For this chat service, I want people to have "Online, Offline, Busy and Away" as possible statuses.

I currently have a table in my database named "users", which has their username/password/mail etc, all the usual stuff. Now I was wondering whether I need to save the status of the user here as well?

If not: How do I best store a user's status? (Keeping in mind others need to see this of course)

If yes: What's the best way to go about this? I thought I would store it as an integer (e.g, 1==offline, 2==online, 3==away and 4==busy). Saving it as text would just be a waste of space?

(By the way, it's an RDBMS, if that matters 🙂 )

Best Answer

... table in my database named "users", which has their username/password/mail etc,

Of course you mean that you're storing the "hashed" or similarly calculated value derived from the user's chosen password. 

How do I best store a users status?

I would go with an Integer value in the users record, which is a foreign key to a (tiny, little) look-up table of users_statuses: 

status_id  Description
        1  Offline 
        2  Online 
        3  Away 
        4  Busy

That way, when you want to add ... 

       37  Lost in the Bermuda Triangle

... it's just another row in a table; no code changes; no database schema changes; easy.

Related Topic