Database Design: Register and Verification

Question

Is it a good choice to have unverified users into the users_table or should I make a temp_users_table to add the unverified users?

The first option would be to create the row on the users_table with a column, for instance, account_activated to hold a integer that defines if the account is verified or not.

The Second option would be to have two identical tables, users_table and temp_users_table. Adding the unverified users on the last. When verified the row will be copied to the users_table and removerd from temp_users_table.

Which one is better and why?

Edit:

The second table is not meant to stay there for ever, it's temporary and will only exist while the user is not activated. When the user is activated it'll be migrated to the "main" user_table.

So:

users_table: Will have the users that have been verified.
temp_users_table: Will have ONLY the users that are not verified.

Answer 1

Always depends on the circumstances, but I have a hard time finding the circumstances where it would make sense to do the two tables solution.

Answer 2

Agreed to erikkallen

Two identical tables best-practise for redundant design. it just doesn't make much sense to check e.g. (pseudo-code):

sql = Sql("SELECT * FROM db_unactivated_users WHERE user_id = X");
sql2 = Sql("SELECT * FORM db_activated_users WHERE user_id = X");
if (sql)
 {
  return "user not activated";
 }
elseif (sql2)
 {
  return "user activated";
 }
else
 {
  return "user not found";
 }

this alternative is much faster and, best of all, doesn't need redundant tables

sql = Sql("SELECT * FROM db_users WHERE user_id = X");
if (sql[activated] = 0)
 {
  return "user not activated";
 }
elseif (sql[activated = 1)
 {
   return "user activated";
 }
else
 {
  return "user not found";
 }

i hope you see the difference ;-)

and on top of that, if you need to change a user's state from "not activated" to "activated", you don't need to delete him from the unactivated-table, save his information temporarily and write him again into the activated-table

with one table, you just change 0 to 1 and thats it!

so, basically it's simple math:

2 tables = 2 queries 1 table = 1 query

which one is faster? ;-)

Answer 3

From the information given I would definitely argue for the one table/extra column solution, unless there is a clear reason to do otherwise. The main drawback to using two tables is it will become extremely easy to corrupt your database with user ids that conflict or don't match up, especially if your ids are auto incremented. You'd have to be very careful about how you move a user from the unverified table to the verified table. On the other hand, with one table, your only code changes will be to add filters for verified/unverified users in instances where you only want to run a query on verified or unverified users. For example, SELECTs for a login check. If there are any reasons not mentioned above that would steer you to use 2 tables let us know.

Answer 4

I see this from a different point of view.

In your situation, one table is probably good enough. But there are other considerations.

1) Volume. In a small table, filtering on a flag is not going to significantly affect performance. In a large table (millions of rows), you would have to put the flag in an index. Putting a low cardinality flag in an index of a large table can decrease performance.

2) Defects. Having a flag in the table requires that almost every query use the flag. For a large enough, or complex enough system, someone is going to miss that flag. Determining the risk depends on the cost of accidentally selecting an unactivated user.

One way to mitigate the risks is to use views. If you implement a two table solution, use a view (All_Users) using UNION ALL. If you implement an one table solution, create a view for activated users only and use that table instead. Only the maintenance functionality needs to modify the core tables.