Let's say I need to implement an application which requires email confirmation for new users. When a new user registers, an email which includes a confirmation key is sent to his email address. When he consumes the key, his registration is complete.

The confirmation keys are generated at registration (and associated with the user/email) and stored in a table in the database. The question is: when a user consumes the confirmation key, should the corresponding entry in the database be deleted (i.e. the absence of such an entry states the fact that the user has been confirmed) or should there be an additional column in the table (e.g. 'confirmed') which states whether or not the user has been confirmed?


There is no right or wrong way to implement this in the database, it all depends on your needs.

Normally, the registration confirmation by email is done to validate the email address of the user. Your marketing department wants to make sure that when they send marketing campaigns, those campaigns actually reach someone's inbox :). Other reasons to validate the email is to make sure that if the user forgets his password you have a channel on which you can send a reset token.

If the registration key is used just to validate the user's email, you don't even need to store it somewhere, just generate it from the user's data:

a = user id in the database;
b = user email;
c = timestamp of registration;
d = number of hours (or days) that the confirmation key is valid;
e = any other unique things of the user

confirmationKey = sha1(concat(a, b, c, d, e))

You then send the user this confirmation key in the email, and when he replies you regenerate the hash and match it against what the user sent. If the hash matches then you check if the time the user confirmed the registration is less than c + d. If yes, then the token is valid and still hasn't expired. Of course you need to make sure the confirmation key can't be guessed by anyone, and fields like c help in that sense. You get the idea...

You now don't need to store anything in a database... unless you need to answer questions about the registration process, like:

  • how many users actually confirmed after registration? Maybe some of those who didn't confirm are bots?
  • how much time do users usually take to confirm the registration?
  • for how many users did the confirmation key expire before they actually confirmed?
  • etc

After you find out what questions you need to answer (if any) you will also know what you need to store in the database and how.

  • with only the confirmationKey from the user, there's no way to match it to a registered user in your database since the hash can't be decoded – T. Rex Jun 3 '18 at 15:29

I think you would want the confirmation key to be temporary and expire after a certain time if the user does not register. You should have a separate table with confirmation keys and a link to the corresponding user in the user table. The user table would have a field indicating confirmation status. If the user registers successfully, set the flag in the user table and delete the corresponding record in the confirmation key table. If the confirmation key expires, then it should be deleted at that time.

  • 1
    I like to use nullable datetime fields for such flags. Knowing when the flag was set is often useful info. – reaanb Feb 17 '17 at 6:30
  • @reaanb - +1, that is something I learned a long time ago and suffer querying data on only a bit is used. – JeffO Feb 27 '17 at 16:44

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.