I have 2 ways for users to create an account on my website.
我有两种方法可以让用户在我的网站上创建一个帐户。
a. Normal Registration Form (email, password) b. Registration via Facebook Connect (fb_userid, email)
一个。正常登记表(电子邮件,密码)b。通过Facebook Connect注册(fb_userid,电子邮件)
Which is the best practice to implement this using MySQL (InnoDB engine) ?
哪个是使用MySQL(InnoDB引擎)实现此功能的最佳实践?
My approach:
[USER]
user_id
user_type (normal/facebook)
[USER_NORMAL]
user_normal_id
user_id
email
password
[USER_FACEBOOK]
user_facebook_id
user_id
email
fb_userid
What do you suggest?
你有什么建议?
5 个解决方案
#1
0
If those are the only fields then it's probably easiest to put all the fields in one table and have NULLs as appropriate.
如果这些是唯一的字段,那么将所有字段放在一个表中并且适当地具有NULL可能是最容易的。
However, if you want a normalised design you would go for something like this:
但是,如果你想要一个标准化设计,你会想要这样的东西:
[USER]
user_id (PK)
email
(Other fields common to both)
[USER_NORMAL]
user_id (PK, FK to USER.user_id)
password
(Other fields specific to 'normal')
[USER_FACEBOOK]
user_id (PK, FK to USER.user_id)
fb_userid
(Other fields specific to FB)
If 'password' is the only field specific to 'normal' users and there are many fields specific to FB users then a compromise might be to have two tables: USER (as above but containing 'password') and USER_FACEBOOK
如果'password'是唯一特定于'普通'用户的字段,并且有许多特定于FB用户的字段,则折衷可能是有两个表:USER(如上所述但包含'password')和USER_FACEBOOK
#2
4
This single table would be more simple (in my opinion):
这个单表会更简单(在我看来):
user (user_id, user_email, user_password, user_fbid)
user(user_id,user_email,user_password,user_fbid)
You don't need a "type" because you can use a CASE to determine if user_fbid is NULL then it's a "normal" account, else if user_password is NULL then it's a Facebook account.
您不需要“类型”,因为您可以使用CASE来确定user_fbid是否为NULL然后它是“普通”帐户,否则如果user_password为NULL则它是Facebook帐户。
#3
3
I would have two tables.
我会有两张桌子。
One table should contain basic user information:
一个表应包含基本用户信息:
user (user_id, user_email, user_password)
user(user_id,user_email,user_password)
The other table should be generic and link 3rd party accounts to these users. Example:
另一个表应该是通用的,并将第三方帐户链接到这些用户。例:
user_ext (type, user_id, uid)
user_ext(type,user_id,uid)
The type field should contain the type of service (in this case Facebook), and the unique identifier for the service (in this case the Facebook User ID). It should then link back to the user_id.
类型字段应包含服务类型(在本例中为Facebook),以及服务的唯一标识符(在本例中为Facebook用户ID)。然后它应该链接回user_id。
This strategy will then allow you to add additional services that users can authenticate against in the future.
然后,此策略将允许您添加用户将来可以进行身份验证的其他服务。
#4
1
I would keep everything on one table and differenciate them by if they have a Facebook Id or not.
如果他们有Facebook ID,我会将所有内容放在一张桌子上并区分它们。
#5
0
I would probably prefer to keep all users in 1 table. You can have fields that are null if that user's type doesn't have that field. For example fb_userid can be null if the user is normal.
我可能更愿意将所有用户保留在1个表中。如果该用户的类型没有该字段,则可以包含null的字段。例如,如果用户正常,则fb_userid可以为null。
[USER]
user_id
user_type (normal/facebook)
email
password
fb_userid (can be null: yess)
#1
0
If those are the only fields then it's probably easiest to put all the fields in one table and have NULLs as appropriate.
如果这些是唯一的字段,那么将所有字段放在一个表中并且适当地具有NULL可能是最容易的。
However, if you want a normalised design you would go for something like this:
但是,如果你想要一个标准化设计,你会想要这样的东西:
[USER]
user_id (PK)
email
(Other fields common to both)
[USER_NORMAL]
user_id (PK, FK to USER.user_id)
password
(Other fields specific to 'normal')
[USER_FACEBOOK]
user_id (PK, FK to USER.user_id)
fb_userid
(Other fields specific to FB)
If 'password' is the only field specific to 'normal' users and there are many fields specific to FB users then a compromise might be to have two tables: USER (as above but containing 'password') and USER_FACEBOOK
如果'password'是唯一特定于'普通'用户的字段,并且有许多特定于FB用户的字段,则折衷可能是有两个表:USER(如上所述但包含'password')和USER_FACEBOOK
#2
4
This single table would be more simple (in my opinion):
这个单表会更简单(在我看来):
user (user_id, user_email, user_password, user_fbid)
user(user_id,user_email,user_password,user_fbid)
You don't need a "type" because you can use a CASE to determine if user_fbid is NULL then it's a "normal" account, else if user_password is NULL then it's a Facebook account.
您不需要“类型”,因为您可以使用CASE来确定user_fbid是否为NULL然后它是“普通”帐户,否则如果user_password为NULL则它是Facebook帐户。
#3
3
I would have two tables.
我会有两张桌子。
One table should contain basic user information:
一个表应包含基本用户信息:
user (user_id, user_email, user_password)
user(user_id,user_email,user_password)
The other table should be generic and link 3rd party accounts to these users. Example:
另一个表应该是通用的,并将第三方帐户链接到这些用户。例:
user_ext (type, user_id, uid)
user_ext(type,user_id,uid)
The type field should contain the type of service (in this case Facebook), and the unique identifier for the service (in this case the Facebook User ID). It should then link back to the user_id.
类型字段应包含服务类型(在本例中为Facebook),以及服务的唯一标识符(在本例中为Facebook用户ID)。然后它应该链接回user_id。
This strategy will then allow you to add additional services that users can authenticate against in the future.
然后,此策略将允许您添加用户将来可以进行身份验证的其他服务。
#4
1
I would keep everything on one table and differenciate them by if they have a Facebook Id or not.
如果他们有Facebook ID,我会将所有内容放在一张桌子上并区分它们。
#5
0
I would probably prefer to keep all users in 1 table. You can have fields that are null if that user's type doesn't have that field. For example fb_userid can be null if the user is normal.
我可能更愿意将所有用户保留在1个表中。如果该用户的类型没有该字段,则可以包含null的字段。例如,如果用户正常,则fb_userid可以为null。
[USER]
user_id
user_type (normal/facebook)
email
password
fb_userid (can be null: yess)