0

I am trying to create a postgres database for a webapp that has users that either login with email/password combo or with social accounts. I would like to do it such that once logged in a person is also able to link other social accounts.

Based on a similar question here, I came across this database schema storing 3 tables to solve the above issue

user(user_id);
authenticator (authenticator_id, name, description, authentication_type);
user_authentication(user_id, authenticator_id, authentication_identity, authenticator_token) ;

I tried populating some fields in the above 3 tables

+-------+
| users |
+-------+
|     1 |
|     2 |
|     3 |
+-------+


+------------------+----------+--------------------+---------------------+
| authenticator_id |   name   |    description     | authentication_type |
+------------------+----------+--------------------+---------------------+
|                1 | password | login via password | password            |
|                2 | facebook | login via facebook | facebook            |
|                3 | google   | login via google   | google              |
|                4 | twitter  | login via twitter  | twitter             |
+------------------+----------+--------------------+---------------------+


+---------+-------------------+-------------------------+--------------------------------------------------------------+
| user_id | authentication_id | authentication_identity |                     authentication_token                     |
+---------+-------------------+-------------------------+--------------------------------------------------------------+
|       1 |                 1 | abc@gmail.com           | $2b$11$xwyYcDOlRBRQNQYdzmTYVuqRSxswsSzxINJdKl0bWuHFzCWZYztpy |
|       2 |                 1 | bcd@gmail.com           | $2b$11$xwyYcDOlRBRQNQYdzmTYVuqRSxswsSzxINJdKl0bWuHFzCWZYztpy |
|       2 |                 2 | bcd@gmail.com           |                                                              |
|       3 |                 1 | cde@gmail.com           | $2b$11$xwyYcDOlRBRQNQYdzmTYVuqRSxswsSzxINJdKl0bWuHFzCWZYztpy |
|       3 |                 3 | def@gmail.com           |                                                              |
+---------+-------------------+-------------------------+--------------------------------------------------------------+

I have 2 problems in this schema

  1. bcd@gmail.com is repeating twice. The email has to be unique
  2. If we take the email and pair it with users to make it unique, it creates a second problem. Let's say you make an account with abc@gmail.com and a password. 1 month later you come and login with Facebook (which has the same email abc@gmail.com) It wont accept this Facebook login because it will try to create an entry for abc@gmail.com which already exists.

How can I keep the email unique but at the same time let a user link multiple social accounts?

8
  • 1
    Is a email/pw required or can someone use social accounts without providing that information?
    – bbaird
    Nov 17 at 16:16
  • @bbaird a person who registers on the website using an email password combination is required to enter them everytime but if they use a social login to signup on the website then no
    – PirateApp
    Nov 17 at 16:22
  • 1
    And when they choose a social login to signup, what are the values stored as part of that process?
    – bbaird
    Nov 17 at 16:59
  • 1
    I don't have time to type a full answer at the moment but look at this fiddle and see if it makes sense: dbfiddle.uk/…
    – bbaird
    Nov 17 at 20:25
  • 1
    That sounds like something that should be handled in transactional logic (add user if email doesn't exist/etc). UserId here is essentially meaningless - it's a pointer we use to tie disparate things together. We shouldn't have to assign it more than once.
    – bbaird
    Nov 18 at 4:54

Your Answer

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

Browse other questions tagged or ask your own question.