数据库设计：注册和验证 [英] Database Design: Register and Verification

问题描述

将未经验证的用户纳入 users_table 或者应该将 temp_users_table 添加到未验证的用户？

第一个选项是在 users_table 上创建一列列，例如， account_activated 保存一个整数，用于定义帐户是否被验证。

第二个选项是两个相同的表格， users_table 和 temp_users_table 。最后添加未验证的用户。验证后，该行将被复制到 users_table 并从 temp_users_table 中删除​​。

哪一个更好，为什么？

编辑：

第二个表不是意味着永远呆在那里，这是暂时的，只有用户没有激活时才会存在。当用户被激活时，它将被迁移到主user_table。

所以：

  users_table：将有已验证的用户。 
 temp_users_table：只有没有验证的用户。 
  

解决方案

我从不同的角度看这个。 / p>

在你的情况下，一张桌子可能够好了。但还有其他的考虑。

1）卷。在一个小表中，对旗帜进行过滤不会对性能产生显着影响。在一个大的表格（数百万行）中，你必须把标志放在索引中。将较低的基数标志放在大表的索引中可能会降低性能。

2）缺陷。在表中有一个标志需要几乎每个查询使用该标志。对于足够大或足够复杂的系统，有人会错过这个标志。确定风险取决于意外选择未激活的用户的成本。

减轻风险的一种方法是使用视图。如果您实现两个表解决方案，请使用UNION ALL的视图（All_Users）。如果您实现一个表解决方案，请仅为激活的用户创建一个视图，并使用该表。只有维护功能需要修改核心表。

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.

解决方案

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.

这篇关于数据库设计：注册和验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！