3

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)

Which is the best practice to implement this using MySQL (InnoDB engine) ?

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?

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
mallix
  • 1,399
  • 1
  • 21
  • 44

5 Answers5

4

This single table would be more simple (in my opinion):

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.

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • I think being more explicit is better. If you were to look at that table in a year, you may not remember what the lack of a fbid means – Ojen Nov 16 '12 at 20:49
  • 1
    @Ojen Field comments would help with this. – Kermit Nov 16 '12 at 20:50
  • Well that is what I think also. Maybe I ll decide in a month to implement a 3rd type of registering. I am thinking of this in a long run. – mallix Nov 16 '12 at 21:05
3

I would have two tables.

One table should contain basic user information:

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)

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.

This strategy will then allow you to add additional services that users can authenticate against in the future.

JohnD
  • 3,884
  • 1
  • 28
  • 40
1

I would keep everything on one table and differenciate them by if they have a Facebook Id or not.

Rafael
  • 2,827
  • 1
  • 16
  • 17
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. 

[USER]
user_id
user_type (normal/facebook)
email
password    
fb_userid (can be null: yess)
ajon
  • 7,868
  • 11
  • 48
  • 86
  • 1
    No need for a `user_type` as you can differentiate user type based on whether `fb_userid` is `NULL` or not. – Kermit Nov 16 '12 at 20:55
  • 1
    I understand that, but I personally don't mind a little bit of redundance for the sake of clarity. I would probably do it without the user_type, but I think it is a slightly neater solution to filter `WHERE user_type='normal'` vs. `WHERE fb_userid=NULL`. It makes it more explicit what is going on. – ajon Nov 16 '12 at 23:13
  • I second that IMO user_type is unnecessary. Why not let users take advantage of both methods of registration? Maybe some of them might want to enter login and password, for example, to be on the safe side if their fb account is blocked. – Megan Caithlyn Apr 29 '15 at 15:54
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.

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

Martin Wilson
  • 3,386
  • 1
  • 24
  • 29
  • Yes, I think that this is better since email comes to both types of users. Those keys mentioned are for reference, I could easily have many more keys like (Profile links, gender, Name, Surname) for user_facebook table. – mallix Nov 16 '12 at 20:51
  • My only reservation with this approach is the possible overhead of having multiple tables. You can simplify this problem and avoid having multi-table queries. – Kermit Nov 16 '12 at 20:54
  • Depending on the number of fields specific to FB (sounds like there might be a few) and 'normal' a compromise might be to have two tables - USER (containing password as well, which would be NULL for FB users) and USER_FACEBOOK – Martin Wilson Nov 16 '12 at 20:59
  • For every registration I have a pair of USER and USER_(normal or facebook). How can we have NULL values to either one of them ? – mallix Nov 16 '12 at 21:07
  • Do you mean you have a pair of objects? Which language? Anyway, if it's easier then stick to 2 tables - although when retrieving you should be aiming to populate a User_normal object and a User_Facebook object (or whatever) in one query rather than two – Martin Wilson Nov 16 '12 at 21:12