Should site registrations be stored in a separate table to active users?

Question

I'm wondering about best practices for user registration. I'm leaning towards storing site registrations in a separate registrations table and then once the signup has been confirmed by email transferring the data over to the users table.

The benefit to this would be that reads from the users table are not cluttered with never activated registrations. Another benefit is that the email (username) field can remain unique in the users table but if you attempt to register with an email address that you don't own, the owner of that email address will still be able to register with it as the email field will not be unique in the registrations table.

I'm wondering if this is a common practice or if there's any reason this is not a good idea?

Answer 1

I think that would be over engineering.

Store all of them in one table and schedule an SQL query (everyday) to delete not actived accounts older than 30 days.

Answer 2

Activated or Not

There's no real need to store a user in a different table. Just store all the users in one table, with a boolean flag indicating if their activated or not. Every so often run a cron job to check and see if there are accounts that are x days old and not activated and delete them.

Email

So presumably you are activating the user from the e-mail right? You also want to set it up so that if another user entered someone else's e-mail that other person could still register correct? That's actually rather simple to do. In that e-mail that is mistakenly sent to them, just have a link to remove that e-mail from your database because obviously it's not them and they're never going to activate the account.

But what if the other guy just deletes it?

Then when they register tell them you already have their e-mail and offer to send the activation again, this e-mail will also have the option of removing it from the database.

OR

Just remove the older account from the database. Stands to reason if the person is trying to activate a new account and there's an old one that never activated you can just remove it.

Answer 3

I agree with this practice for Separation of Concerns reasons: A "registration process" and "user accounts" are two separate concepts.

The registration table would be used to persist the status of the "registration process". Once that process is complete (once the email is validated), the process's "output" would be a new "user account". If the process fails (for example, it "timeouts" after the user fails to confirm his email for 1 month), there's no impact on the "user account" concept.

As was said in another answer, this can be over engineering in some cases, but it can also be a useful way to enapsulate complexity of these two separate concepts. It can also be seen as contradicting normalization principles, but then I believe you have to choose a tradeoff between encapsulation and normalization depending on your context.

Answer 4

Do yourself a favour and keep it a single table. You're just not going to have enough aborted registrations for performance issues to come into play (especially if you wipe them after 3 weeks).

Your justification about the email addresses is silly. Nobody is likely to get their email blocked by a false registration.

Answer 5

There may be one case in which you'd logically split the two tables: If your registration process asks a lot of mandatory user information only after you've confirmed the e-mail address. For example, if your pending registration table would wind up with only a few columns, such as e-mail and activation key whereas your users table has many additional columns, such as username, first name, last name, postal address, etc.

The split might make sense in that case, as you'd be able to declare those columns NOT NULL.

Other than that, though, I have to agree with most of the other answers; it sounds like premature optimization to me. Multiple tables with the same structure is a strong warning sign that ur doin it wrong. Not definite, but a strong warning.