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 :) )

... 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.

  • Yes, I don't store plaintext passwords, I assumed that did not need to be explicitely said ^^ And thanks for your answer, I'll do it that way! – Dylan Meeus Feb 27 '15 at 13:31

I agree with you, storing the status as integer will be enough. And I would prefer store it in different table, let's say user_status, since it will be updated frequently according to user activities.

  • The new table would be better when the next request is some sort of date/time indicating how long a user has been at this particular status (normalization). – JeffO Feb 27 '15 at 13:47

The users table is definitely the place to store it.

Note that if you use MySql (and possibly some others?), you can also use an enum, so that you can be sure that the value that you write is in range, and you can refer it to it legibly in your application's code (e.g., onLine, instead of 1).

  • Would adding a new status require a coding change or is the enum part of the database? – JeffO Feb 27 '15 at 13:48
  • The enum is part of the database. – Mawg Feb 27 '15 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.