MarioRicalde MarioRicalde - 9 days ago 5
MySQL Question

Database Design: Register and Verification

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

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.

Comments