Design tables for multiple login methods

Question

I am faced with the dilemma of normalization.

Now that OpenID has been declared a failure , I would like to include it in my website. I am also going to quit FB Connect while I am on it.

Obviously, this means that I will have a 1: much relationship between accounts and logins. This is straightforward. However, this also means that not all types of logins are the same.

This means that I need to decide how to store three sets of the same in the function, but different elements of information. This means that I have to make annoying choices, and I'm not sure if there is a winning “best” solution. That's why i come to you

I see three instant options.

Option 1: entry table with rows for each type

- ID - Account ID - Username - Password - Facebook ID - OpenID URL - OpenID ID 

Option 2: Generic login table

 - ID - Account ID - Login Type - Generic Field 1 - Generic Field 2 

Option 3: Login table for each input type

 Password Logins - ID - Account ID - Username - Password FB Connect Logins - ID - Account ID - Facebook ID Open ID Logins - ID - Account ID - OpenID URL - OpenID ID 

The presence of special lines for each type of input in the general login table (i.e., option 1) feels dirty and irregular.

The presence of common lines in the general login table (for example, option 2) seems dirty and very confusing.

Having a separate table for each input type (i.e., option 3) feels clean / normalized, but can be inefficient and possibly confusing.

How could others do this? Are there any other options? What will have the most negative consequences in reality?

Keep in mind that I would like to include additional login types (e.g. Twitter / next big thig / whatever) when they arrive.

Answer 1

Option 3 is your best, IMHO.

1) Security
More than one table must be stolen so that you lose all client security data. Think about limiting your risk and liability.

1a) Also ...
Is there a need to find out what facebook login name is associated with which openID? Perhaps, but in any case, of course, this would be useful for a data thief if you put it all together for them (option 1)

2) Table functions - such as triggers, calculated fields, or that you probably (?) Should be used in a unique way for each provider.

3) If you really need to have everything in one table for some odd / special purpose, you can still do it with a look.

4) Design optimization.
Most likely, the data is very similar, but still ... Maybe the data types would not be a motivator, but partitioning / indexing, etc. would be relevant.

5) Unique requirements.
Who knows what may be required in the future. Think about scalability without having to redefine existing structures (quite a lot).

I am sure that there are other important thoughts about this ... but it’s out of my head for what it is worth.