-1

In a website users can register through different social logins ex: facebook, google, github etc in addition to normal email registration.

There is one users table to store these users, and in addition is needed a mapping to check if a user comes from 3rd party logins or not.

  1. One solution to store the users can be in one table in which the design is as follows: id (int), provider_user_id (string), provider (string)

Id is internal user id in your system provider_user_id is the id of the user in the provider system Provider can be facebook, google etc

  1. The second solution is to keep separate tables, ex: facebook_users and there have id(int) facebook_user_id(string)

You do the same thing for all providers (one table for each).

Which approach do you like better and why ?

  • Storing your users by login provider facilitates sorting them by login providers and complicates everything else. But if that were the predominant use case, then your site wouldn't really be doing anything worthwhile. – Kilian Foth May 14 '18 at 9:58
  • @KilianFoth the purpose of storing the provider id, and the user id in the provider system is so that you do not register them again when they login again through that certain provider. Ex user comes first time you register him through facebook, user logs second time through facebook you already have him registered so you just log him in instead. – Kristi Jorgji May 14 '18 at 10:02
  • @KristiJorgji It's not about storing the provider ID in user records. If you need the data to get the job done, that's fine. But storing users separately for different login providers makes little sense. – Kilian Foth May 14 '18 at 10:09
  • @KilianFoth thank you. I thought that maybe has some benefit storing separately in case when you have huge amount of users, millions. But I think you are right, one joined single table does the work fine regardless of how many users will come through socials – Kristi Jorgji May 14 '18 at 10:25
2

If you need different kinds of data from different identity providers, then use different tables. If all identity providers give you the same kind of data, you can combine them in a single table – but you don't have to.

Note that you will be separating different kinds of identity mechanisms into separate tables anyway: email/password-based authentication in one table, and third-party identity providers in one or more other tables.

You will probably allow one user to have multiple login methods, but at most one identity for each provider. Here, the solutions differ in how you express your constraints.

  • If you use one table per provider, you have a 1:0..1 relation between users and provider identities.
  • If you combine providers into a single table, you have a 1:0..N relation. The provider table should also use a composite key (user_id, provider_id) in order to limit users to one associated account from each provider. (Tip: do use IDs or enums for the providers, not string names!)

This kind of increased complexity would lead me to using one table per provider. Your code likely treats each provider as a completely separate login mechanism, not as a generic login mechanism where the providers only differ by their name. However, using a single table for all providers is also a perfectly fine solution, if you design your data model accordingly.

  • Thanks for the insight. I also think like the above, just was wondering what made more sense (in perspective mostly if you have millions of users and how can it affect queries etc). The data saved in the logins table is the same, just provider_id and user_id_in_this_provider_system. I guess I was overthinking it. I will just stick with one table for facebook_users one for google_users even though they will have exactly same design (interal_user_id, provider_user_id) ! What do you think ? – Kristi Jorgji May 17 '18 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.