MarioRicalde MarioRicalde - 1 year ago 71
MySQL Question

Database Design: Register and Verification

Is it a good choice to have unverified users into the

or should I make a
to add the unverified users?

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

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

Which one is better and why?


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.


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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download