如何更好地组织数据库来解决用户状况的变化 [英] How to better organise database to account for changing status in users

问题描述

我关心的用户可以是未确认或已确认。后者意味着他们获得完全访问权，前者意味着他们正在等待主持人的批准。我不确定如何设计数据库来解决这个结构。

The users I am concerned with can either be "unconfirmed" or "confirmed". The latter means they get full access, where the former means they are pending on approval from a moderator. I am unsure how to design the database to account for this structure.

有人以为我有两个不同的表：confirmUser和unconfirmedUser非常相似，除了unconfirmedUser有额外的字段（如emailConfirmed或confirmationCode）。这是稍微不切实际的，因为我必须复制所有的信息，当用户确实被接受（虽然我想象会不会那么糟糕 - 不期待交通繁忙）。

One thought I had was to have 2 different tables: confirmedUser and unconfirmedUser that are pretty similar except that unconfirmedUser has extra fields (such as "emailConfirmed" or "confirmationCode"). This is slightly impractical as I have to copy over all the info when a user does get accepted (although I imagine it won't be that bad - not expecting heavy traffic).

我想象的第二种方式是将所有的用户实际放在同一张表中，如果需要的话也可以在一个表中添加额外的未确认数据（也可能在用户表中添加已确认的标志） ）

The second way I imagined this would be to actually put all the users in the same table and have a key towards a table with the extra "unconfirmed" data if need be (perhaps also add a "confirmed" flag in the user table).

每个方法的优点和缺点是什么？有没有更好的方法来设计数据库？

What are the advantages adn disadvantages of each approach and is there perhaps a better way to design the database?

推荐答案

第一种方法意味着您需要编写两个表的每个查询 - 对于常见的一切。坏（tm）。第二个选择肯定更好。这样，您可以根据需要添加一个简单的，其中confirm = True （或False）。

The first approach means you'll need to write every query you have for two tables - for everything that's common. Bad (tm). The second option is definitely better. That way you can add a simple where confirmed = True (or False) as required for specific access.

什么实际上可以考虑的是确认的数据（不是用户，只是数据）是否存储在同一个表中。也许这将更干净+标准化以将所有确认数据放在一个单独的表中，以便您在join.userid = users.id中附加确认，其中users.id不为空 （或类似的，或内部连接，或在服务器端脚本中获取所有+过滤器等）仅获得已确认的用户。额外的数据，如确认电子邮件，日期等可以存储在这里。

What you could actually ponder over is whether or not the confirmed data (not the user, just the data) is stored in the same table. Perhaps it would be cleaner + normalized to have all confirmation data in a separate table so you left join confirmation on confirmation.userid = users.id where users.id is not null (or similar, or inner join, or get all + filter in server side script, etc.) to get only confirmed users. The additional data like confirmation email, date, etc. can be stored here.

这篇关于如何更好地组织数据库来解决用户状况的变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！