How to properly store login records of third-party systems?

Question

MySQL

There are many users.
Each user can have one or more logins on other systems.
The list of systems is not predetermined.
How to properly store these logins?

The following paths come to mind:

1. One table where there are 2 fields: a user ID and a field like TEXT, where [email protected], [email protected], [email protected] are specified separated by commas

+------+-------------------------------------------------------------+
|  1   | [email protected], [email protected], [email protected]           |
+------+-------------------------------------------------------------+
|  2   | [email protected], [email protected], [email protected]      |
+------+-------------------------------------------------------------+

2. Two tables:
The first with the user ID and e-mail, for example, the
Second - the user ID from the first table (not a unique field) and login in a third-party system.

+------+-----------------------+
|  1   | [email protected]         |
+------+-----------------------+
|  2   | [email protected]    |
+------+-----------------------+

+------+-------------------------+
|  1   | [email protected]         |
+------+-------------------------+
|  2   | [email protected]       |
+------+-------------------------+
|  1   | [email protected]         |
+------+-------------------------+
|  2   |  [email protected]      |
+------+-------------------------+
|  2   |  [email protected]       |
+------+-------------------------+
|  1   |  [email protected]        |
+------+-------------------------+

Questions:
1. Is one of these two ways correct? If so, which one?
2. If there is a better way, please describe it.

Answer 1

The simplest:
users:
id, email
user_linked_accounts
id, user_id, sn_id
social_networks
id, title
one to many throught user_linked_accounts.
Or have I misunderstood you?

Answer 2

I think the second option is better. Plus it is necessary to give a unique field. It will be useful if the information is linked, for example, to one of the accounts, then you will indicate the id of the account on the network there and work with the second table. Otherwise, you will need to record logins in that table with data, and then each time you will need to take a field to cut it and search by name. Moreover, imagine the deletion, you need to cut out the login (that is, you will work everywhere not with the login ID and its data, but with the text) and imagine that in two systems it has the same logins ... there is already an additional field in the second database to distinguish field. I’ll get drunk and the answer will go away ... otherwise they’ve leaked karma and won’t let me write at all.

Answer 3

Inspired by the topic Misconceptions of programmers about names and thoughts that a user can have several names in different systems (a system can be understood even as Facebook, even as a passport office - do not care).
The main idea is that our user in our system (table user) can have a login in any system (table ext_login_system). Each system that can log in to a user is implemented as a certain class, we store in the database how to call the system (well, so that we can fill in the necessary information for the user - login password or whatever is needed - the system itself must know this and draw the necessary screenshots ), and in the database - only the names of the methods that pull. Further, since each system has its own specific data set, it stores them in its own table (in the example, ext_login_system_facebook and ext_login_system_mail_yandex). Next, we tie it all together through the ext_logins label.

Answer 4

User ID as key, then external system ID + User Name and Password in separate columns. One-to-many relationship. Second table with description of external systems (if necessary)

Answer 5

Question to those who answered: why add a unique fake ID if [email protected] is already a unique identifier?

Answer 6

Why would this be wrong? [email protected] cannot be non-unique - otherwise it is not clear who came. And it must be tied to exactly 1 row in the user table. But already in it, please, generate IDs in the system for the user table - they are just needed there.