If I have a table for user accounts, there is a tendency to just use that same table to store the authentication information. In most cases this is fine, because a site may only have one form of authentication or a user may only login using one authentication method.
However, if I want a user to be able to log into my site using any number of authentication methods, I have to separate the authentication data from the user account information. In most cases, you would use some form of reference to lookup an account based on the authentication. Since each account may have multiple forms of authentication available, but a particular form of authentication may only access one account, you have a one-to-many relationship between accounts and authentication methods. The easiest way to setup this type of relationship in a database is to add a foreign key to the authentication table(s) which stores the value of the primary key for the user account. Then you authenticate the user based on the information in the authentication table, and, once they've logged in successfully, get the user account information by looking up the user account with the foreign key (you can get all of the related data with a join in your database query, but it may be better to avoid pulling user account information until you've authenticated the user).
For your example, if I log into the site for the first time using my facebook account, the site gets some information from my facebook account to build a user account and stores the information to associate my facebook account with that user account. If I change the password to my local account on the site, it is basically creating another authentication method for my account, but leaves the association with my facebook account alone, so either method is a valid method for accessing the site. Some sites may even permit you to associate other social media accounts with your user account, so you can log in using multiple social media logins.
No matter how they do it, the central idea is that the user account and authentication method are two separate pieces of information. It may even be possible to do this with just one database table (by adding fields for all of the possible authentication methods to one table), but this would be inefficient in most cases.
