Most efficient way of checking if a user as activated his/her account?

Question

I want my users to activate their accounts before they are able to login. They are sent an email after registration containing an activation link, something like this:

http://www.blabla.com/[email protected]&token=Aisd23uNMAu53932asdDasd82AS

Of course, whenever someone logs in, I have to check wether or not that user has activated his/her account. I can think of 2 ways to solve this problem, either have an extra column in my 'users' table, which is set to empty whenever a user activates like so:

-----------------------------------------------
| id | username | password | activation_token |
-----------------------------------------------
| 1  | user1    | blabla   |                  |
-----------------------------------------------
| 2  | user1    | blabla   | asd232DA34qADJs2 |
-----------------------------------------------

Then I extract the activation_token along with the user-information whenever a users logs in. Or I could have a seperate table that contains activation tokens only, which is then joined on the 'users' table everytime a user logs in:

--------------------------------------
| id | account_id | activation_token |
--------------------------------------
| 1  | 37         | dsad2428491dka98 |
--------------------------------------
| 2  | 2          | asd232DA34qADJs2 |
--------------------------------------

So which one would be most efficient? Thanks for your time.

EDIT: Thanks for all the great responses

Answer 1

Use the first option - add an isactivated column to the USERS table.

There's no need for a separate table - this is a one-to-one relationship.

Answer 2

Storing the token in the Users table rather than a seperate table will mean that you don't have to join them during each query, which will be slightly quicker.

Also, you're not storing the userIds and creating a new Id for that tokens table, which will save on the data storage.

Answer 3

I would have an integer field, Activated, that is defaulted to 0. When someone attempts authentication, you would only look for Activated accounts. I store auth tokens in a separate table like you have described.

Answer 4

Personally, I'd do a combination of the two...

-------------------------------------
| id | username | password | status |
-------------------------------------
| 1  | user1    | blabla   | 1      |
-------------------------------------
| 2  | user1    | blabla   | 0      |
-------------------------------------

Where the status is a TINYINT(1) field which is 0 for deactivated users, and 1 for activated users. That way, you can tell really quickly the "status" of the user...

Then, store the token in another table (just like you already have)... That way, you don't need to join, or check a string column when not activating the account...

Answer 5

If the relationship is 1-1 (e.g. the activation table would have 1 row per account id), then doing a fully normalized 2-table approach is an overkill.

You would not have major problem with either approach but 1-table one is easier.

If you go with 2-table approach, you should store "activated" yes/no flag in the user table, so you don't need to join to a second table for user login purpose.

Answer 6

I don't think there's a need to store the activation token in DB. Something like md5('users@email' . 'secret') will work just fine. As for user status, i agree with others, use a separate dedicate "status" column in the users table. An additional advantage is that this column can store others stati as well (e.g. "banned" ;)

Answer 7

If the activation token is only ever used to validate the 'click here to activate your account' link and is never used again, then there's no point in wasting space in your user table storing the char(32) (or whatever it is) field for a one-time usage. Put the activation tokens in a seperate table that your account activation script can refer to when the user clicks through to activate. Once the activation's completed, you can delete the token's record from that seperate table.

Put an 'is_activated' boolean/bit field in the user table that your login script can check during the login process (and output a "hey, you haven't activated yet" error if the field's null/false).

Of course, disk space is cheap these days. Even a million users each with a 32char activation token will only 'waste' 32meg of space. With a terabyte drive going for less than $100, that's 0.00305% of the disk, and essentially $0.00 cost (0.305 cents).

Answer 8

In my opinion instead of activation code being stored in users table, keep a flag set it off by default. when a user clicks on any activation link, then update the table and set the flag on.

Before logging in check the flag is on or not. If flag is off then the user has not clicked on the activation link. then you can give a error message to the user.

If flag is on then the user can log in successfully.